Skip to main content

Null check in Airtable Automation Script

  • December 9, 2022
  • 7 replies
  • 117 views
A
Forum|alt.badge.img+1

Hi 👋
I recently decided to take the leap to level up how I use Airtable by taking baby steps to use Airtable script to accomplish more complex tasks, especially in Airtable Automations. 

I am currently struggling to combine a typical "If conditional statement" with a "for loop."
In this example below, I am trying to do a simple null check to check if NOT BLANK then run through the for loop to update those blank fields with an "Undefined" text, but it always seem to replace all records even when a record already have text in it. 

What am I doing wrong or what am I missing? 


 

let records = input.config().step2Records let table = base.getTable("Playground"); if ("Email") { for (let record of records) { await table.updateRecordAsync(record, { "Email": "Undefined" }) } }

 

For reference,  input variables:

 



I have seen you @kuovonne all around forum posts patiently teaching others about understanding and utilizing Airtable script (which has been tremendously helpful btw so thank you for what you do!), so I thought you may know the answer to this.


    7 replies

    Andrey_Kovalev
    Forum|alt.badge.img+20
    • Andrey_Kovalev
    • Inspiring
    • 95 replies
    • December 9, 2022

    @Andrew_Tsao Hello! It seems that IF statement is executed only once, before the FOR loop. That is why you get identical results for all of your listed records. If you first set the FOR loop and use a conditional IF inside, then you, probably, will get what is expected.  

    Andrey_Kovalev
    Forum|alt.badge.img+20
    • Andrey_Kovalev
    • Inspiring
    • 95 replies
    • December 9, 2022

    @Andrew_Tsao Hello! It seems that IF statement is executed only once, before the FOR loop. That is why you get identical results for all of your listed records. If you first set the FOR loop and use a conditional IF inside, then you, probably, will get what is expected.  


    I mean:

    for (let record of records) { if ("Email") { await table.updateRecordAsync(record, { "Email": "Undefined" }) } }
    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • December 9, 2022


    > I have seen you @kuovonne all around forum posts patiently teaching others about understanding and utilizing Airtable script (which has been tremendously helpful btw so thank you for what you do!), so I thought you may know the answer to this.

    Thank you.  Unfortunately the new community platform has made it impossible for me to participate with the community the way I have. For example, little things like quoting specific pieces of your message is now incredibly difficult. Please forgive the formatting of this post. It is the fault of the platform.

     

    if ("Email") { ... }

     This bit of code is part of your problem. Your "if" condition is testing the hardcoded text string "Email", the literal word "Email", which is always a truthy value. The word "Email" is not the actual email of of a record. You need a variable in there somewhere. Are you trying to get the value of the "email" input variable?

    It also looks like you are trying to cross-reference the emails and record IDs in your input variables. I do not recommend doing this. Instead, you should probably
    1. Get the records IDs as an input variable.
    2. Query for the actual record objects with those record IDs
    3. Loop through the actual record objects to get the email values, check for nulls, and perform the updates.

    Karlstens
    Forum|alt.badge.img+24
    • Karlstens
    • Brainy
    • 602 replies
    • December 11, 2022

    Sometimes it's worth working out the logic in a simpler environment - in that, before trying to get your script working within Automation, try out some logic in the Scripting Extension before hand.

    Using the scripting app - I think a working solution may look something like this;

    const emailTable = base.getTable("Emails") let myRecords = await emailTable.selectRecordsAsync( { fields : [ "Email Address"] }); console.info(myRecords) let emptyRecords = myRecords.records .filter( record => ( record.getCellValue("Email Address") === null)) .map( record => ({ id: record.id, fields : { "Email Address" : "My new value" }})) console.log(emptyRecords) await emailTable.updateRecordsAsync(emptyRecords)

    Importantly, if you are planning on venturing into the land of scripting and all things Javascript, seek an understanding and the .filter() and .map() methods. Unfortunately (and what I found to be very frustrating as I went about learning Airtable Scripting) is that you won't find these techniques used within the API examples - but as soon as you learn and understand them, Airtable scripting becomes far more approachable.

    Once you're solving the problem within the Script Extension, you may then migrate your idea into an Automation script.

    A
    Forum|alt.badge.img+1
    • Andrew_Tsao
    • Author
    • New Participant
    • 2 replies
    • December 12, 2022


    > I have seen you @kuovonne all around forum posts patiently teaching others about understanding and utilizing Airtable script (which has been tremendously helpful btw so thank you for what you do!), so I thought you may know the answer to this.

    Thank you.  Unfortunately the new community platform has made it impossible for me to participate with the community the way I have. For example, little things like quoting specific pieces of your message is now incredibly difficult. Please forgive the formatting of this post. It is the fault of the platform.

     

    if ("Email") { ... }

     This bit of code is part of your problem. Your "if" condition is testing the hardcoded text string "Email", the literal word "Email", which is always a truthy value. The word "Email" is not the actual email of of a record. You need a variable in there somewhere. Are you trying to get the value of the "email" input variable?

    It also looks like you are trying to cross-reference the emails and record IDs in your input variables. I do not recommend doing this. Instead, you should probably
    1. Get the records IDs as an input variable.
    2. Query for the actual record objects with those record IDs
    3. Loop through the actual record objects to get the email values, check for nulls, and perform the updates.


    Ah I think I also see the issue with the UI (specifically the reply box) Thank you for still taking your time to answer my questions thoughtfully. 

    I was trying to get the value of the "email" input variable. Should I have used something like 

    record.getCellValue to achieve that? 

    1. Get the records IDs as an input variable.
    I thought I was already doing that with `step2Records` and then defining it as "records"?
    2. Query for the actual record objects with those record IDs
    Apart from the If condition checking the wrong thing, is there something that I'm not querying here?
    3. Loop through the actual record objects to get the email values, check for nulls, and perform the updates.
    Was the (let record of records) not looping through the record objects as I imagined it would?
     
    A
    Forum|alt.badge.img+1
    • Andrew_Tsao
    • Author
    • New Participant
    • 2 replies
    • December 12, 2022

    Sometimes it's worth working out the logic in a simpler environment - in that, before trying to get your script working within Automation, try out some logic in the Scripting Extension before hand.

    Using the scripting app - I think a working solution may look something like this;

    const emailTable = base.getTable("Emails") let myRecords = await emailTable.selectRecordsAsync( { fields : [ "Email Address"] }); console.info(myRecords) let emptyRecords = myRecords.records .filter( record => ( record.getCellValue("Email Address") === null)) .map( record => ({ id: record.id, fields : { "Email Address" : "My new value" }})) console.log(emptyRecords) await emailTable.updateRecordsAsync(emptyRecords)

    Importantly, if you are planning on venturing into the land of scripting and all things Javascript, seek an understanding and the .filter() and .map() methods. Unfortunately (and what I found to be very frustrating as I went about learning Airtable Scripting) is that you won't find these techniques used within the API examples - but as soon as you learn and understand them, Airtable scripting becomes far more approachable.

    Once you're solving the problem within the Script Extension, you may then migrate your idea into an Automation script.


    It's a great idea to figure things out in the scripting block before trying it in automation. I think I was intimidated by what I read somewhere that there are some small differences in writing script In Automations vs Scripting and thought just to dive straight into Automations first. But thanks for the pro tip! 

    You're absolutely right about understanding .filter() and .map() methods and I had a small suspicious it would include something like that, but what you wrote did help clarify some understanding. 

    The script yo shared worked! If I were to add on to this, where I am doing null checks for multiple fields (Example: If "Name" === null, make "Name= "my new cell value") how I would I add that in this script?

    Karlstens
    Forum|alt.badge.img+24
    • Karlstens
    • Brainy
    • 602 replies
    • December 12, 2022

    So glad to hear of your progress.

    As you've noted, there are differences between an Automation Script vs Extension Script - such as they have an ability to take input from previous Automation Triggers and Automation actions, whilst also able to output to subsequent Automation actions. This gives us multiple ways to get base and record data - through an input, or through a base query - both are just as useful, and there are no doubt scenarios where one method is preferred over the other.

    One trick, within automation, is try apply a javascript techneque called Destructuring Assignment on the input object. For example, your Automation inputs of step2Records, Second Link, and emails - with minor alterations we could do something like;

    const {step2Records, secondLink, myEmails} = input.config(); console.log(step2Records); console.log(secondLink); console.log(myEmails);

    This method assigns the values directly to your named variables in one simple line. I usually can tell when I'm working with my old code, because I always forgot about this in my early days of scripting.

    Destructuring is also great for taking the first element held within an array of records, be it either an id or a record object.

    const amazingAnimals = ["Cat", "Dog", "Fish", "Bill French"]; let [anAnimal] = amazingAnimals; console.log(anAnimal);

     To help with your .filter() question, take this example;

    Once you understand filter, you'll realise it's kind-of-like an "if"statement. It's basically letting any true statement through the gates, which in our case is a subsequent mapped object used to update up to 50 records.

     

     

    My code snippet below (I hope I don't break the forums... man I miss being able to use emoji!)

    const emailTable = base.getTable("Emails"); let myRecords = await emailTable.selectRecordsAsync({ fields: ["Email Address", "Colour"] }); console.info(myRecords); let emptyRecords = myRecords.records .filter( (record) => record.getCellValue("Email Address") === null && record.getCellValueAsString("Colour") === "Blue" ) .map((record) => ({ id: record.id, fields: { "Email Address": "My new value" } })); console.log(emptyRecords); await emailTable.updateRecordsAsync(emptyRecords);

    Importantly, we've added more fields to the selectRecordsAsync query, along with updating the filter() with an AND and a second comparison.


    Terms & ConditionsAccessibility statement