Script to conditionally update a single select field in airtable base

Welcome to the Airtable community!

It sounds like you could accomplish your goal without scripting by using an Update Record action to change the status.

I also recommend the “when record meets conditions” trigger so that you can keep the logic of when the automation triggers within the automation itself instead of off in the view configuration where someone might accidentally change it.

On the other hand, if you want to do this with scripting for the sake of learning scripting, I recommend starting in Scripting Extension instead of an automation. It is much easier to learn scripting in the extension.

Hello kuovonne,

Thank you so much for responding to my post. I have noticed that you are one of the most active members of that forum. I don’t think it is possible, but you probably know better. Let me explain what I am trying to accomplish in this table.

I have a Maintenance schedule table that is part of the AV maintenance base:

Once a record is created in the Maintenance Table, it might go through four separate stages before It changes to inactive or past service check. The four stages are:

• Stage 1 > Upcoming Service Check (between 0 and 30 days)
• Stage 2 > Past due period (between 30 and 35 days, the status automatically changes from Upcoming Service Check to Past Due)
• Stage 3 > Critical Past Due (between 35 and 44 days, the status automatically changes from Past Due to Critical Past Due
• Stage 4 > Past service Check (Once a new record is created again for that exact location, the old one goes to inactive or past service check)

I have four automation that controls this process.

I’m not sure what your issues is. It sounds like you have four automations that move your maintenance records through the different stages as time passes. The first three automations seem straightforward. The last automation is a little trickier because the trigger is not time based, and the trigger depends on how the new records are created.

I can see how you might want to trigger the automations that change the {status} based on the record entering the view so that it is easy to see all the records in a particular state.

Note that your automations will not change the status of existing records (except when testing). The automation will only run when a record moves into the view. It will not run for records that already exist in the view.

Below is the script that I started. Any help will be appreciated.

//script to conditionally update single select field in Airtable base

//script to conditionally update single select field to Past seervice Check

//when a new record is created for the same location

//Get the input vatiable from the trigger

//const recordId = input.config().recordId;

let inputConfig = input.config();

let RecordID = inputConfig.RecordID;

console.log(RecordID);

//Get the record information from the Table View

let table = base.getTable(“Maintenance Schedule”);

let view = table.getView(“All Upcoming Service Check”);

let QueryResult = await view.selectRecordsAsync({

fields: [“Maintenance Schedule”, “Location”,“Last Checked Date”],

sorts: [

// sort by “Maintenance Schedule” in ascending order

{field: “Maintenance Schedule”},

// then by “Location” in descending order.

{field: “Location”, direction: “desc”},

// then by “Location” in descending order.

{field: “Last Checked Date”, direction: “desc”},

]

});

//console.log(record.getCellValue(“Maintenance Schedule”));

let NewRecord = QueryResult.getRecord(${inputConfig.RecordID});

//let location = QueryResult.getRecord(table.fields.name);

console.log(NewRecord);

// Identify Records with identical Locations fields

// loop through all of the records

let DupsRecords = QueryResult.records.filter((NewRecord)=> {

return QueryResult.records.find((potentialDuplicate)=> {

//compare record to potential duplicate

return NewRecord.getCellValue(“Maintenance Schedule”) ===

potentialDuplicate.getCellValue(“Maintenance Schedule”)

&& NewRecord.id !== potentialDuplicate.id;

})

});

console.log(DupsRecords);

the script works up to this point. I am not sure how to finish it

How much of this script did you write yourself and how familiar are you with JavaScript?

It is hard to tell what you are trying to do with this script. It looks like the script is doing a lot of extra work like sorting records and looking for duplicates.

Are you trying to update a record that is not the triggering record? Are you trying to update multiple records?

If you know that there is only one record to update, I think you are better off without using scripting. Use a “find records” action to find the record to update. Then use a conditional action to update the existing record if there is one.

Hello kuovonne

I just know a little bit about JS that why reason I am trying to customize the Find “Duplicates Script” to accomplish this task. What I would like to happen is that each time a tech does a service check at a location, a new record is created, and the old record needs to change its status to “Past service check.” Multiple techs might be working at different locations simultaneously, hence the need to query the whole table for new records. It seems like I am not explaining the issue clearly enough.
Would it be helpful to see a screenshot of the table?

Here is a screenshot of the table.

That is correct, I am trying to find the previously created record for that specific location which I was able to do with the script that I posted. But I am not sure how to finish it yet.

I’m sorry that I am not going to give you the answer that you want.

These documentation pages about updating cell values may help.

• To update a single select field, you need to use the cell write format included in the documentation.
• You will also need to use either updateRecordAsync or updateRecordsAsync.
• It also looks like you have edited your post and the current script you have posted does not match the script that I first read. Your previous version had code to map the duplicate records to the write format.

I am not going to give your more direct answers because I think your existing script has too much in it that does not belong, and I’m still not convinced that you actually need a script.

Well, I don’t know JavaScript at all, so I can’t help you there.

As @kuovonne mentioned above, if you’re 100% sure that there will always be just one matching result, then you could just use Airtable’s native automations to do a “find records” for the matching client name (but not matching the current Record ID), and then update the found record. This will only work if you always have ONLY one matching record.

However, if you might sometimes have multiple matching records (or if you just want to have more flexible & powerful automations), it seems like this would be significantly easier to accomplish with Make instead of trying to write a script. You would just need 3 simple steps, as seen in the screenshot below.

You would watch for when a new record is created, then search for any records that match the client name (but don’t match the current record), and then update the fields that you want to update.

This keeps everything as easy & straightforward as possible, and you don’t need to know any code.

Thank you both for your help. I think I should be able to resolve it by myself now.

I could be confused with a different post, but I thought an earlier draft of one of the messages said that he already had four automations in Integromat.