Airtable Scripting - Need to Hire ASAP

[LUDLOW]

Hello all! I am struggling to find a good way to run an Airtable automation script that suits our needs. After many hours of unsuccessfully scouring forums and testing things myself (with no scripting experience), I’ve decided to try and hire somebody for this portion of our build. Two projects within the same Base:

1. When an identical record is created (matching two fields in the same table), replace the existing record with the newer version.

2. When a record is updated (watching one single-select field), update the identical records (matching two fields in the same table) with an alternate selection.

DEADLINE: Monday 7/31

Paid via PayPal. Anyone interested and available? P.S. Please let me know if this solicitation for assistance is against community guidelines so I can remove it.

[carg]

First, I apologize that this is not an answer to airtable.

However, I’d like to share my experience as a newbie implementing the latest version in softr.

Combining hidden field in tally so that submitted responses are recorded in google sheets
In google sheets, I created a separate sheet and added
sort function to sort in reverse chronological order.
xlookup function, it will only get the latest data.
The latest data fetched in that way is reflected in SOFTR.

I hope my experience will be helpful to you.

[LUDLOW]

Hi @carg! Thanks very much for chiming in. I appreciate your thoughts and am glad you found something that works for your systems. That’s a good workaround and a path worth exploring. Unfortunately, this is a very specific use case for us in Airtable, and we’re on a tight deadline. So instead of figuring it out myself, I thought it would just be easier and faster to hire this time around.

[matthieu_chateau]

Could you try this one for your first use case?

The code is inside an Airtable automation (when a record is created => run script)
Change Test table by your current table and Name and Name2 by the right field names. You also need a created time field named createdTime. My field createdTime has the format ISO and the option “include time” is on (12h format).
Though I’m not super sure of what you need (specifically … the value inside Name and Name2, in my case, should be the same so that the last record is deleted?)

const tableName = 'Test table';
const field1Name = 'Name';
const field2Name = 'Name2';

async function main() {
  let table = base.getTable(tableName);
  let queryResult = await table.selectRecordsAsync({
    fields: [field1Name, field2Name, 'createdTime'],
    sorts: [{ field: 'createdTime', direction: 'asc' }],
  });

  let recordsToDelete = [];
  let seenRecords = new Map();

  for (let record of queryResult.records) {
    let recordKey = `${record.getCellValue(field1Name)}_${record.getCellValue(field2Name)}`;
    let createdTime = new Date(record.getCellValue('createdTime'));

    if (seenRecords.has(recordKey)) {
      let existingTime = new Date(seenRecords.get(recordKey).getCellValue('createdTime'));

      if (createdTime > existingTime) {
        recordsToDelete.push(seenRecords.get(recordKey));
        seenRecords.set(recordKey, record);
      } else {
        recordsToDelete.push(record);
      }
    } else {
      seenRecords.set(recordKey, record);
    }
  }

  if (recordsToDelete.length > 0) {
    await table.deleteRecordsAsync(recordsToDelete);
  }
}

main();

[matthieu_chateau]

For your second use case:
Same, you change the table name, the fields name and the alternate selection value (the first 5 const)
Also, the script is inside an Airtable automation (When a record is updated => run script)

rec1 is coming from input.config() => click add input variable in the left console. Name it rec1 and the value will be the recordId of the updated record.

alternateSelectionValue is another input variable added in the left console. Name it alternateSelectionValue and give the value ‘Alternate Value’ (or whatever you want, It should be the exact same value as there is in your single select field that you chose to be an alternate value)

Though, again, not sure if this is exactly what you want as I don’t know what should be this alternate value and where this alternate value should go. In my example the alternate value is called “Alternate Value” and goes to the ‘Status’ single select field.

Also, the record to update, among the records that match the conditions, is chosen randomly in the script

const tableName = 'Test table';
const field1Name = 'Name';
const field2Name = 'Name2';
const singleSelectFieldName = 'Status';
const alternateSelectionValue = 'Alternate Value';

async function main() {
  let table = base.getTable(tableName);
  let queryResult = await table.selectRecordsAsync({
    fields: [field1Name, field2Name, singleSelectFieldName],
  });

  let updatedRecords = [];

  const inputConfig = input.config();
  const updatedRecordId = inputConfig['rec1'];

  const updatedRecord = queryResult.records.find(record => record.id === updatedRecordId);

  if (!updatedRecord) {
    console.error('Updated record not found in the query result.');
    return;
  }

  const statusField = table.getField(singleSelectFieldName);

  const alternateOptionId = statusField.options.choices.find(choice => choice.name === alternateSelectionValue)?.id;

  if (!alternateOptionId) {
    console.error(`Option ID not found for '${alternateSelectionValue}' in the 'Status' field options.`);
    return;
  }

  const matchingRecords = queryResult.records.filter(record =>
    record.id !== updatedRecordId &&
    record.getCellValue(field1Name) === updatedRecord.getCellValue(field1Name) &&
    record.getCellValue(field2Name) === updatedRecord.getCellValue(field2Name)
  );

  if (matchingRecords.length > 0) {
    const randomIndex = Math.floor(Math.random() * matchingRecords.length);
    const randomMatchingRecord = matchingRecords[randomIndex];

    await table.updateRecordAsync(randomMatchingRecord, {
      [singleSelectFieldName]: { id: alternateOptionId },
    });

    updatedRecords.push(randomMatchingRecord);
  }

  output.set('updatedRecordIds', updatedRecords.map((record) => record.id));
}

main();

[LUDLOW]

This is an awesome start. Thank you very much! Here’s more context. Let’s say we have one loan application record in the table “Loans”. When an applicant selects which banks to send their loan application to (perhaps 5 different banks), that triggers an automation to break that one loan application into 5 separate loan applications in a new table, “Term Sheet”. The deduping needs to happen for whenever the original “Loans” record gets updated - that would again trigger the automation above to create 5 new records (now total 10) with updated loan information in the “Term Sheet” table.

When running this script, we want to watch for “Term Sheet” duplicates that match the fields “Loan Record ID” (from the original record) and the “Lender” to which they are applying. If the original loan record ID and the lender name match an existing record in the “Term Sheet” table, then the new record needs to replace the existing (because it has the most up-to-date loan information).

I added the “createdTime” field and edited the Names:

const tableName = ‘Term Sheet’;
const field1Name = ‘Loan Record ID’;
const field2Name = ‘All Lenders’;

The Term Sheet table looked like this with 5 lenders applied:

When I edited the original loan record in the “Loans” table to add another bank (KeyBank), the “Term Sheet” table expanded like this:

Now, ideally, the first 5 lenders would have been replaced with the next 5 plus KeyBank. But what happened with this script was this:

Maybe Celtic was the last record to be updated so it overwrote the rest? I feel like we are close here, but I must be implementing it incorrectly. Thank you again.

[matthieu_chateau]

I just tested it out with your setup (almost). Good news, It works!
So, where should be the problem? I’m 95% sure that It works on my end because I don’t use a “link to another record field” and/or the time format is not the right one.
Of course, there is no point to re-think your setup and process, there are ways to make it work.

Can you try exactly the same but:

1. Verify that All Lenders field has the option “Allow linking to multiple records” disabled.
2. The createdTime field should be ISO format.
3. Retry the automation + script to check if it works

If not working =>

3. Add a look up field linked to All Lenders (display the name of the lender => it will appear exactly like in All Lenders but it will be a lookup field, which might be better understood by Airtable)
4. Replace All Lenders in const field2Name = ‘All Lenders’; by the the lookup field name you created in 2.

Below, you will find screenshots of my setup.
The first screenshot is the setup before the script runs
The second screenshot is the setup of the AT automation with the “run script” action
The third screenshot is the result of the AT automation with the “run script” action in it, after I clicked on Test action

[LUDLOW]

@matthieu_chateau, you’re brilliant. This worked flawlessly, and #4 was the final solution. Onto the next, taking cues from what we just created…

From your script above regarding our second dilemma. What’s the best way to remove the “field2Name”? I realized that for this to work, we really only need to match one field. The ideal situation:

When a record is updated (or meets conditions) when one record is “Selected” the other records matching the “Loan Record ID” are automatically populated with “Declined”. I had a non-script version of this, but couldn’t get the automation to constrict the output to only editing records with identical “Loan Record ID”.

[matthieu_chateau]

Way easier to do, you don’t even need a script for this.

Here is the setup of the automation:

• When a record matches condition => Choose the right table => “When Selected is Selected”
• Find records => choose the same right table => Find record based on condition => Where Selected is empty AND Where Loan Record ID is Loan Record ID (enable the dynamic condition here for the “is Loan Record Id”, not the static condition)
• Choose repeating group action => Input list being list of records (the previous action find records) =>repeat for each in “list of records”
• Still in the repeating group action add a update record action => Choose the same right table as before =>RecordId from Current Item from list of records => Fields Selected with value Declined

Press On for the automation and run a live test within the table (not inside the automation test)

Here are some screenshots

[matthieu_chateau]

For the record, it can also be done with a script inside an airtable automation =>

With my example

• Airtable automation being when a record matches condition in Test table and Status is Value 1 => run script

• script being:

const tableName = 'Test table';
const field2Name = 'Name2';
const singleSelectFieldName = 'Status';
const autoPopulatedValue = 'Value 3';

async function main() {
  let table = base.getTable(tableName);
  let queryResult = await table.selectRecordsAsync({
    fields: [field2Name, singleSelectFieldName],
  });

  let updatedRecords = [];

  const inputConfig = input.config();
  const updatedRecordId = inputConfig['rec1'];

  const targetRecord = queryResult.records.find(record => record.id === updatedRecordId);

  if (!targetRecord) {
    console.error('Target record not found in the query result.');
    return;
  }

  const statusField = table.getField(singleSelectFieldName);

  const autoPopulatedOptionId = statusField.options.choices.find(choice => choice.name === autoPopulatedValue)?.id;

  if (!autoPopulatedOptionId) {
    console.error(`Option ID not found for '${autoPopulatedValue}' in the 'Status' field options.`);
    return;
  }

  if (targetRecord.getCellValue(singleSelectFieldName)?.name === 'Value 1') {
    const name2Value = targetRecord.getCellValue(field2Name);
    const matchingRecords = queryResult.records.filter(record =>
      record.id !== targetRecord.id &&
      record.getCellValue(field2Name) === name2Value
    );

    for (const matchingRecord of matchingRecords) {
      await table.updateRecordAsync(matchingRecord, {
        [singleSelectFieldName]: { id: autoPopulatedOptionId },
      });
      updatedRecords.push(matchingRecord);
    }
  }

  output.set('updatedRecordIds', updatedRecords.map((record) => record.id));
}

main();

Here are some screenshots:

1. Table setup (same as before)
2. global AT automation
3. script setup

[LUDLOW]

This worked great! Seriously can’t thank you enough.