Help

Re: Update Multiple Records with Automation

9171 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jonathan_Hollow
4 - Data Explorer
4 - Data Explorer

I am working on using an Airtable automation to assign topics to writers.

Step 1: Trigger

Step 2: Find records that meet the right criteria.

Step 3: Send an email with values from the found records as a grid.

Step 4: Update the records from step 2. I have a single select field “Emailed” that can be true or false. I’d like to update these values to True.

Step 4 is where I am having a problem. I do not see the possibility for selecting Record ID’s for the records found in step 2. Is there a workaround to this?

Thanks in advance.

23 Replies 23
CT3
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi there—

You’re right—it doesn’t seem like the update record step accepts multiple records as input.

You could use a script in step 4 to achieve what you want to do, though. If you make “run script” your step 4 action, and then set an input variable named step2Records with the value of the record IDs from the records in step 2:

Screen Shot 2021-03-23 at 1.32.57 PM

Insert this as the code, replacing the name of the table from which the step 2 records come from :

let records = input.config().step2Records //.step2Records must match the Name field of the input field.
let table = base.getTable("NAME OF TABLE STEP 2 RECORDS ARE IN"); // Replace with your table name.
for (let record of records) {
    await table.updateRecordAsync(record, {
    "Emailed": true, //Replace Emailed with the field name of the checkbox field you want to update, if different.
    })
}

That should update the checkboxes in the Emailed field of all the records passed from step 2.

FWIW, you could probably do a lookup where, if the records in step 2 are connected to the trigger in step 1 via a linked record field, your step 4 in this automation updates a checkbox field on the trigger record instead. Then you could have a seperate automation looking for change in that lookup field, which would then check the box in the Emailed field. That’s a bit convoluted, but it’s your “no code” option. This might be helpful if you want to look into that more.

Stephen
6 - Interface Innovator
6 - Interface Innovator

Hi Jonathan

Did you ever find an ‘easy’ solution to this as I’m trying to do the same thing?

Did CT3’s solution work for you?

Thank you

Stephen

I ended up setting it up to update each record individually. CT3’s solution was a bit over my ability.

My initial goal was to have Airtable send one email to a writer, which would contain several topics for them to write on.

I ended up having it send one email containing only one topic, but doing that for each topic assigned. In the end this worked better for my situation, as it kept my inbox more organized as writers were able to reply to that specific email with questions, deliverables, etc. No more hunting through email threads for attachments or docs.

So my automation was triggered when certain conditions were met on a single record(for example assigned to a writer and emailed field is “False”), then it would send an email, then it would update the emailed field to True.

Okay, thanks Jonathan.

I was looking for a solution to this today, and after running into many errors with my script taking so long, I realised that it was best to just set up a view with the conditions of the records that you want to update and trigger the automation based on the record meeting the view criterea.

Sam_Becker
5 - Automation Enthusiast
5 - Automation Enthusiast

I ran into this problem today and CT3’s solution worked perfectly. Thank you!

Carter_McKay
5 - Automation Enthusiast
5 - Automation Enthusiast

I’ll add to CT3’s solution

let records = input.config().record;
let table = base.getTable(“Table Name”); // Replace with your table name.
for (let record of records) {
await table.updateRecordAsync(record, {
“Deposit”: {name: “Paid”}, //Replace Emailed with the field name of the checkbox field you want to update, if different.
})
}

This is for single select fields. Just switch out Paid for whatever the select option that you want to use is.

~Carter McKay

Ambroise_Dhenai
8 - Airtable Astronomer
8 - Airtable Astronomer

It’s a shame that Airtable doesn’t support updating multiple records through a no-code configuration.

Unfortunately, as of today, there is no other choice than writing a script. And, depending on your workflow, it might be as simple as Update Multiple Records with Automation - #2 by CT3 answer, or something far more complicated.


I’m posting the below code for those who would to see how we deal with “complicated”.

It’s much more advanced than the above, and will also require tweaking the code to change the “Configuration” and the “Step 2” which defines what fields are updated.

The script is fairly well documented, and uses best-practices for testing and debugging. (DX)
We use a similar script (the same “base”) for all our automations.

The getRecordFieldValues is a helper (it takes more than half of the script’s size) and isn’t meant to be changed unless you want to add new use cases. It basically converts the fields described in BANK_ORDER_FIELDS_TO_FETCH into a more usable data format, to avoid having to transform each data manually. It’s not so useful here because there is only one field being transformed, but when you have a more fields to transform, it’s quite a lifesaver.

/**
 * This script is used by an Airtable automation.
 *
 * It updates several MoneyOutBankOrder records.
 *
 * It works in a 3-steps workflow, such as:
 *   1) It fetches data from Airtable (using the Automation internal API)
 *      - MoneyOutBankOrder - Fetches the data of the record that has triggered the automation
 *   2) It updates the records contained in the field "bulkTransferSimpleOrders" of the trigger record and changes their "executionStatus" to "planned"
 *
 * @see https://airtable.com/appDEqCgPvoO63Pmw?automation=XXX
 * @see https://www.airscript.dev/
 * @see https://airtable.com/marketplace/category/scripts
 */

// ------------------------------------------------ Configuration -----------------------------------------------

/**
 * Fields to fetch from the MoneyOutBankOrder table (for the trigger bank order).
 */
const BANK_ORDER_FIELDS_TO_FETCH = [
  'ref',
  'recordId',
  {
    name: 'bulkTransferSimpleOrders',
    type: 'relationship-to-ids',
  },
];

const bankOrderTableName = 'MoneyOutBankOrder';

const relationshipsValues = {};

/**
 * For testing purpose, as fallback value when "inputConfig.triggerBankOrderId" is not set.
 *
 * Must be a valid ID from the "bankOrderTableName" table.
 * You can use a formula field with "RECORD_ID()" to know the internal Airtable record ids.
 *
 * XXX This is to improve DX, because Airtable automation developer experience is shitty, and they don't allow to run automation that are based on Inputs from the UI.
 *  (or at least, not always, and the current behavior is confusing as of March 2021)
 *  So, to test an automation from the UI, one needs to remove all configured Inputs to run the script manually.
 *  And then, when the script works properly, re-configure all Inputs.
 *  And do this every time.
 *  The alternative is to run the scripts through automation, but it's harder to debug that way (small editor, not possible to run code directly from editor, etc.)
 */
const testTriggerBankOrderId = 'reclBJNkBppAFx0Xh'; // 'order-165-FROM-compte-de-booster-financier-TO-ambroise-dhenain-27'

// ----------------------------------------------------------------------------------
// -------------------- Script (end of script's configuration) ----------------------
// ----------------------------------------------------------------------------------

/**
 * Resolves the values of the record, for all specified fields.
 * Stores the resolved values in the destination.
 *
 * @param fields (array of fields)
 * @param record (airtable record)
 * @param destination (object)
 */
const getRecordFieldValues = (fields, record, destination) => {
  fields.map(async (field) => {
    const fieldType = typeof field === 'string' ? 'string' : field.hasOwnProperty('type') ? field.type : 'unknown';
    let fieldName;
    let fieldValue;

    switch (fieldType) {
      case 'single-select': // Treats the field as a "Single select" field, returns its "name" as an object and ignores the rest
      case 'single-select-to-text': // Treats the field as a "Single select" field, returns its "name" as a string and ignores the rest
      case 'multi-select-to-text':  // Treats the field as a "Multi select" field, returns all its "name" as a string (comma-separated) and ignores the rest
      case 'relationship': // Treats the field as a "Relationship" field and attempts to resolve it. Requires the relationship to have been fetched and stored into relationshipsValues beforehand (or it will crash)
      case 'relationship-to-ids': // Treats the field as a "Relationship" field, but doesn't attempt to resolve it, returns the "id" of each element as an array
      case 'relationship-to-names': // Treats the field as a "Relationship" field, but doesn't attempt to resolve it, returns the "name" of each element as an array
      case 'formula': // Treats the field as a "Formula" field, returns the value (handles all edge cases where the structure might be an scalar value or object, etc.)
      case 'lookup': // Treats the field as a "Lookup" field, returns the value (handles all edge cases where the structure might be an scalar value or object, etc.)
      case 'number-as-float': // Treats the field as a "Number" field, returns the value as a float (decimal)
      case 'number-as-int': // Treats the field as a "Number" field, returns the value as an integer
      // case 'date':
      case 'number-to-checkbox': // Treats the field as a "Checkbox" field, returns the value as a boolean
        fieldName = field.name;
        break;
      default:
        fieldName = field.hasOwnProperty('name') ? field.name : field;
        break;
    }

    let fieldNameDest = typeof field === 'string' ? fieldName : field.hasOwnProperty('as') ? field.as : fieldName;

    switch (fieldType) {
      case 'single-select':
        try {
          fieldValue = record.getCellValue(fieldName);

          if (fieldValue && Array.isArray(fieldValue)) {
            fieldValue = fieldValue[0].name;
          }
          if (fieldValue && fieldValue.hasOwnProperty('name')) {
            fieldValue = fieldValue.name;
          }
        } catch (e) {
          // No value defined, ignored
        }
        break;
      case 'single-select-to-text':
        // Airtable stores single select choices as an array, or as an object (depends on their internal rules), which will be null if no choice has been set
        fieldValue = record.getCellValue(fieldName);

        if (fieldValue && Array.isArray(fieldValue)) {
          fieldValue = fieldValue[0].name;
        }
        if (fieldValue && fieldValue.hasOwnProperty('name')) {
          fieldValue = fieldValue.name;
        }
        break;
      case 'multi-select-to-text':
        // Convert potential multi choices to string, value will be null if no choice has been set
        fieldValue = record.getCellValue(fieldName);

        if (fieldValue) {
          fieldValue = fieldValue.map((selectedChoice) => selectedChoice.name).join(', ');
        }
        break;
      case 'relationship': {
        const relationships = record.getCellValue(fieldName);

        if (relationships !== null) {
          fieldValue = relationships.map((relationship) => {
            const relationshipId = relationship.id;

            return relationshipsValues[field.table][relationshipId];
          });
        } else {
          fieldValue = [];
        }
        break;
      }
      case 'relationship-to-ids': {
        const relationships = record.getCellValue(fieldName);

        if (relationships !== null) {
          fieldValue = relationships.map((relationship) => {
            return relationship.id;
          });
        } else {
          fieldValue = [];
        }
        break;
      }
      case 'relationship-to-names': {
        const relationships = record.getCellValue(fieldName);

        if (relationships !== null) {
          fieldValue = relationships.map((relationship) => {
            return relationship.name;
          });
        } else {
          fieldValue = [];
        }
        break;
      }
      case 'lookup':
      case 'formula':
        const recordValue = record.getCellValue(fieldName);

        if (typeof recordValue === 'object' && recordValue !== null) {
          fieldValue = record.getCellValue(fieldName)[0];
        } else {
          fieldValue = record.getCellValue(fieldName);
        }
        break;
      case 'number-as-float':
        fieldValue = parseFloat(record.getCellValue(fieldName));
        break;
      case 'number-as-int':
        fieldValue = parseInt(record.getCellValue(fieldName), 10);
        break;
      // case 'date': XXX Doesn't work, day is wrong and time is lost, I believe it's just easier to treat dates as text and then use formulas to convert them into actual dates
      //   const value = record.getCellValue(fieldName);
      //   console.log('value', value);
      //   const date = new Date(value);
      //   console.log('date', date);
      //   fieldValue = date;
      //   break;
      case 'number-to-checkbox':
        fieldValue = parseInt(record.getCellValue(fieldName), 10) > 0;
        break;
      default:
        fieldValue = record.getCellValue(fieldName);

        if (fieldValue && typeof fieldValue === 'object' && fieldValue.hasOwnProperty('specialValue')) {
          console.error(`[WARNING] The field "${fieldName}" has a "specialValue" property (this SHOULD NOT happen). This usually happens when processing a formula field which resolved to NaN (Not a Number). You probably should adapt your formula to avoid that edge case.`);
        }
        break;
    }
    destination[fieldNameDest] = fieldValue;
  });
};

// -------------------- Step 1: Fetching Airtable data --------------------

/**
 * Resolve the updated record from the Airtable "input.config" API.
 *
 * "inputConfig.triggerBankOrderId" will always be set when the script is executed through an Airtable automation, but it won't when we execute it manually (during script testing).
 * This will show a red warning on Airtable until you configure a proper "Input variable" for the automation.
 */
const inputConfig = input.config();
const triggerBankOrderId = inputConfig.triggerBankOrderId || testTriggerBankOrderId; // Fallback to hardcoded test record id for better DX

// Fetching trigger bank order
const bankOrderValues = {};
const bankOrderTable = base.getTable(bankOrderTableName);
const bankOrderTableRecords = await bankOrderTable.selectRecordsAsync();
console.log('bankOrderTableRecords', bankOrderTableRecords);
const bankOrderRecord = bankOrderTableRecords.getRecord(triggerBankOrderId);

getRecordFieldValues(BANK_ORDER_FIELDS_TO_FETCH, bankOrderRecord, bankOrderValues);

console.log('bankOrderValues', bankOrderValues);

// -------------------- Step 2: Updating the MoneyOutBankOrder records --------------------

const bankOrdersToUpdate = bankOrderValues.bulkTransferSimpleOrders.map((orderId) => {
  return {
    id: orderId,
    fields: {
      executionStatus: {
        name: 'planned',
      },
    },
  };
});

try {
  console.info(`Udpating data for "${bankOrderTableName}" table, where ref: "${bankOrderValues.ref}"`, bankOrdersToUpdate);
  await bankOrderTable.updateRecordsAsync(bankOrdersToUpdate);
} catch (e) {
  throw new Error(`Error happened while updating the table "${bankOrderTableName}": ${e.message}`);
}

Thank you for sharing. It is interested in seeing how different people approach coding issues, and there is no single way to solve any particular coding problem.

On the other hand, I think that this script is more complicated than it needs to be. For others who want update multiple records with an automation script, there is a wide middle ground of complexity between this script and the one previously linked to.

Just wanted to say “thanks for the script!” I was able to modify it to update a text entry field for my company’s needs.

On2Air Actions allows you to update multiple records within automations.

It’s an easy setup then a script for automations is automatically generated for you. You’ll just add it to the Run a Script action in Automations.

______________________________________
Hannah - On2Air.com - Automated Backups for Airtable

This was so helpful! I just wanted a way to mark records as having been sent an email. Hopefully updating multiple records is coming soon!

Forums are amazing!

I just figured this out based on the solution provided by @CT3. Thank you for leading me down the right path!

I want to add to @CT3’s solution as I needed to figure out how to code the “Find Records” action into this solution and wanted to share what I did if anyone else is still or going to struggle with this topic of bulk updating records in Airtable Automations using a script.

image
Use the screenshot as reference and follow steps below.

In the scripting app:

  1. Click the “add input variable” option on the left panel.
  2. Give the variable a name.
  3. Add the record id’s as a list from your find records action as a dynamic value by clicking the blue +.
  4. Replace .step2Records with your newly created input variable name.

Just to add, this is @CT3’s solution. I just wanted to provide further insight.

Thank you for this. I was struggling with the same issue. Your Script worked perfectly after defining the input.config as you stated.

Thank you! :grinning:

Thanks for this!

Can I ask how would it be with 2 variables, one of them being a date used as a timestamp hence going at the same place as the “true” in the example above?

Charles_Kirchof
5 - Automation Enthusiast
5 - Automation Enthusiast

The easiest way is to create an automation that will update record when it enters a view. Then create a filter for your table that removes only the ones you want to update. Then remove that filter. The automation will then run. After that, delete the automation.

Hi,
can I kindly ask you to guide me how can I make that instead of checking the checkbox I will be able to add a value that was defined by Airtable automations in Input variables?

Hi there. I was struggling with a similar endeavour to clear checkboxes and your code did the job perfectly. Your help is the gift that keeps giving! Many, many thanks as I was going around in circles and this has put me on the path to learning more. Much appreciated.

Cheers
Shaun

Faith_Nte
4 - Data Explorer
4 - Data Explorer

image

Use the “plus” sign to insert the “airtable record ID”, I’m not sure how this was generated but it includes the record ID of different rows.

The limitation here is that it only works when there is only one record per day. If you have multiple records it seems not to return an error.