DevBlocks Agency
DevBlocks Agency
🚅

Airtable Formula Showcase

Examples of advanced formulas in AirTable:

  • Parsing Text
  • Parsing Dates
  • Email Notifications

Parsing Text

Getting width and height from this string: 1200x1600.jpg

Width:

IF(
    NOT(ISERROR(LEFT(LEFT({Type},FIND('.',{Type})-1),FIND('x',LEFT({Type},FIND('.',{Type})-1))-1))),
    LEFT(LEFT({Type},FIND('.',{Type})-1),FIND('x',LEFT({Type},FIND('.',{Type})-1))-1)
)

Height

IF(
    NOT(ISERROR(RIGHT(LEFT({Type},FIND('.',{Type})-1),FIND('x',LEFT({Type},FIND('.',{Type})-1))-1))),
    RIGHT(LEFT({Type},FIND('.',{Type})-1),FIND('x',LEFT({Type},FIND('.',{Type})-1))-1)
)

ArrayDiff

In a field I have the next array ({Family Children}): Olivia Ballard,Rhys Ballard,Georgia Ballard,Hank Ballard

In other field I have ({Full Name}): Rhys Ballard

I want the difference: Olivia Ballard,Georgia Ballard,Hank Ballard

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE({Family Children},{Full Name}&",","")
        ,","&{Full Name}
        ,""
    )
    ,{Full Name}
    ,""
)

Parsing Dates

Displaying children name with age in years or in months if the age of the children is less than 3

{First Name} & " " &{Last Name}&
IF(DATETIME_DIFF(TODAY(), {Child's DOB}, 'years')< 3,
 " ("& DATETIME_DIFF(TODAY(), {Child's DOB}, 'months')&" months)",
 " ("& DATETIME_DIFF(TODAY(), {Child's DOB}, 'years')&" years)"
)

Displaying a friendly date for events with a schedule on specific hours or full days

IF(
    {Event Date (all day)},
    IF(
        {Event Date (start)} = {Event Date (end)},
        DATETIME_FORMAT({Event Date (start)}, 'dddd, MMM D'),
        DATETIME_FORMAT({Event Date (start)}, 'dddd, MMM D')&" - "&DATETIME_FORMAT({Event Date (end)}, 'dddd, MMM D')
    ),
    DATETIME_FORMAT({Event Date (start)}, 'dddd, MMM D')&": "&DATETIME_FORMAT({Event Date (start)}, 'LT')&" - "&DATETIME_FORMAT({Event Date (end)}, 'LT')
)

Email Notifications

Email Content:

"<div>"
&Author & " submitted "&Customer&" "&Status&" for "&Keywords&"."
&"</div>"
&"<div>"
&"<ul>"
&"<li>Link to Google Doc: " & {Article Link} &" </li>"
&"<li>Link to Airtable Record: https://airtable.com/tbl5SqyAcNCVEggej/viwf2Mtw6Vp9t37hl/"&{Record ID}&" </li>"
&"<li>Date Submitted: "& DATETIME_FORMAT(NOW(),'MM-DD-YYYY') &"</li>"
&IF({Internal Notification Due Date},"<li>Date Due: " & DATETIME_FORMAT({Internal Notification Due Date},'MM-DD-YYYY') & "</li>")
&"</ul>"
&"</div>"

Replacing html to email txt version:

SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(
                    SUBSTITUTE(
                        SUBSTITUTE(
                            SUBSTITUTE(
                                SUBSTITUTE(
                                    SUBSTITUTE(
                                        SUBSTITUTE(
                                            SUBSTITUTE(
                                                {Internal Notifications Body},"<div>",""
                                            ),"</div>","\n"
                                        ),"<ul>",""
                                    ),"</ul>","\n"
                                ),"<p>",""
                            ),"</p>","\n"
                        ),"<li>"," * "
                    ),"</li>","\n"
                ),"<b>","*"
            ),"</b>","*"
        ),"<i>",""
    ),"</i>",""
)

Multiple Notifications based on a date field (this can be done using Airtable/Zapier/Openside)

//Field to detect time for the next notification to trigger
IF(
    DATETIME_PARSE( DATETIME_FORMAT(DATEADD(NOW(),-10,'minutes'),'MM-DD-YYYY HH:mm') & " UTC" )<{ZAP | 1st Draft Due +48 Hours},
    "+48_hr",
    IF(
        DATETIME_PARSE( DATETIME_FORMAT(DATEADD(NOW(),-10,'minutes'),'MM-DD-YYYY HH:mm') & " UTC" )<{ZAP | 1st Draft Due 0 Hours},
        "0_hr",
        IF(
            DATETIME_PARSE( DATETIME_FORMAT(DATEADD(NOW(),-10,'minutes'),'MM-DD-YYYY HH:mm') & " UTC" )<{ZAP | 1st Draft Due -24 Hours},
            "-24_hr",
            IF(
                DATETIME_PARSE( DATETIME_FORMAT(DATEADD(NOW(),-10,'minutes'),'MM-DD-YYYY HH:mm') & " UTC" )<{ZAP | 1st Draft Due -48 Hours},
                "-48_hr",
                IF(
                    DATETIME_PARSE( DATETIME_FORMAT(DATEADD(NOW(),-10,'minutes'),'MM-DD-YYYY HH:mm') & " UTC" )<{ZAP | 1st Draft Due -72 Hours},
                    "-72_hr"
                )
            )
        )
    )
)

//Field to get the next notification 

SWITCH(
    {ZAP | 1st Draft Next Reminder},
        "+48_hr",
            {ZAP | 1st Draft Due +48 Hours},
        "0_hr",
            {ZAP | 1st Draft Due 0 Hours},
        "-24_hr",
            {ZAP | 1st Draft Due -24 Hours},
        "-48_hr",
            {ZAP | 1st Draft Due -48 Hours},
        "-72_hr",
            {ZAP | 1st Draft Due -72 Hours}
)