Airtable for Softr end-users [clean-base system part 3]

This guide will provide you with a step-by-step tutorial on how to setup airtable for softr in a  standardized way that remains flexible and easy to read

clean-base system
We will create the users table according to the naming convention and the structure explained in part 1 and 2.

Basic airtable structure

In order to have your table properly set up, this is the basic setup, similar to every table we will create using the clean-base system.
Create a table named users
Create the view sections:
users in the users table
relations in the users table
technical in the users table
Create the views:
users - information in the users view
users - activity in the users view
technical - workflow in the technical view
technical - seo in the technical view

Creating Airtable user fields

Delete every field created by default
Change the primary field to type email and name it email

Setting up the users - information view

In users - information, create the following fields:
name will be a single line text containing the user’s fullname
magic-link will be a URL field
name_firstname will be a formula field:
LEFT({Username}, SEARCH(" ",{Username})-1)
name_lastname will be a formula field:
RIGHT(Username, LEN(Username)-SEARCH("", Username))
avatar will be an attachment field.
role will be a single select field with at least the admin role for you It will be useful when building permissions and workflows. For example when building a job board, you’ll have candidates and employers, on a marketplace you’ll have freelancers and clients, etc.

Setting up the users-activity view

In users - activity, create the following fields:
status will be a single select field with the following choices: This field will be used to define whether a page is shown on the website (and will also impact whether it’s indexed by Google).
  • active
  • review
  • inactive
created will be a created time field. We will create formula fields later as necessary to show that time in different formats but I recommend that original field to be set as follows:
logged will be a date field and set it up with the same formatting as the other date/time fields we just created. We will populate it later using an Appspan Webhook to track the time a user logs in.
updated will be a last modified time field with the above formatting. Set it to trigger for specific fields, select all fields but the Logged field we just created.
Your users - activity view will look like this:
notion image

Setting up the technical - workflow view

In technical - workflow, create the following fields:
avatar_url will be a formula field that will be used for multiple things like schema markup, fastcomments integration, … Use the following formula
IF(Avatar = "","",(RIGHT(LEFT(Avatar, LEN(Avatar) - 1), LEN(LEFT(Avatar, LEN(Avatar) - 1)) - SEARCH("https://", Avatar) + 1)))
live-page will be a formula field with: '' & RECORD_ID() This field will be used whenever we need to provide a link to the user profile.

Setting up the technical - seo view

In the Technical - seo view, create the following fields:
seo:slug will be a formula field with the following formula: This formula will take the name of the user and remove any special character and space to create a clean slug for Softr.
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(LOWER(Name)),"à", "a"),"á", "a"),"â", "a"),"ä", "a"),"æ", "a"),"ã", "a"),"å", "a"),"ā", "a"),"æ", "ae"),"ç", "c"),"ć", "c"),"č", "c"),"è", "e"),"é", "e"),"ê", "e"),"ë", "e"),"ē", "e"),"ė", "e"),"ę", "e"),"î", "i"),"ï", "i"),"í", "i"),"ī", "i"),"į", "i"),"ì", "i"),"ł", "l"),"ñ", "n"),"ń", "n"),"ô", "o"),"ö", "o"),"ò", "o"),"ó", "o"),"œ", "o"),"ø", "o"),"ō", "o"),"õ", "o"),"ß", "s"),"ś", "s"),"š", "s"),"û", "u"),"ü", "u"),"ù", "u"),"ú", "u"),"ū", "u"),"ÿ", "y"),"ž", "z"),"ź", "z"),"ż", "z")," ", "-"),"!", ""),".", ""),"/", ""),"”", ""),"'", ""),"#", ""),"$", ""),"%", ""),"&", ""),"’", ""),"(", ""),")", ""),"*", ""),"+", ""),",", ""),":", ""),";", ""),"<", ""),"=", ""),">", ""),"?", ""),"@", ""),"[", ""),"\\", ""),"]", ""),"^", ""),"_", ""),"`", ""),"{", ""),"|", ""),"}", ""),"~", ""),"«", ""),"»", ""),"€", ""),"„", ""),"“", ""),"---------", "-"),"--------", "-"),"-------", "-"),"------", "-"),"-----", "-"),"----", "-"),"---", "-"),"--", "-")
seo:title will also be a formula field with a formula similar to: This formula shall be adapted to your liking, take the opportunity to add keywords for SEO.
Name & "'s profile on Acme"
Use a metatitle measurement tool to see if your metatitle is not too long.
seo:description will be a formula field with a formula similar to the one shown below. Of course, here you will need to write the summary you want, with your keywords and eventually additional user data to have diverse metadata.
Name & " is on Acme. Visit " & Name_firstname &"'s profile if you are interested in keywords."
Again, don’t forget to use a metadescription measurement tool to see if your metadescription is not too long for Google.
social:title is a simple formula field with the formula:
That way you don’t bother recreating the wheel for Open Graph fields, they will be the same as SEO fields.
social:description is a simple formula field with the formula:
seo:noindex will be a checkbox field showing a red cross that we will use to manually deindex a page.
seo:index will be a formula field with the formula:
Now, if the status is anything other than active or if the checkbox is checked, the page won’t be indexed by google. It will protect you from spamming and bad image in general. We could have just used part of this formula as SOFTR recognizes both 0/1 and true/false, but the point is to have a database that is easily understandable.

Finishing setting up users - information

Go back to users - information and add a button field named live-page_button configured as follows:

Setting up a default avatar for your Softr users

Create an Airtable automation named users - set default avatar
This automation needs to trigger when the avatar field (located users - information view in the users table) is empty:
notion image
Get the URL of a default avatar you like and set an update record step as follows:
notion image
Add a description to your automation so that other users (and your future self) can understand your base:
notion image
Finally, activate your automation.
notion image
Go to the next part of the clean-base system: setting up airtable for softr listings
Joachim Brindeau

I’m a lawyer who likes to tinker with no-code on his free time.


    Related posts

    Softr naming convention [clean-base system part 5]
    Airtable naming convention [clean-base system part 1]
    Softr app structure [clean-base system part 6]
    Airtable base structure [clean-base system part 2]
    Airtable for Softr listings [clean-base system part 4]