Aug 19, 2022 03:45 PM
I am a new user and would like some help figuring out how to write this Airtable Js script. I am trying to change the status of a record base on two conditions in the Maintenance Tracking Table:
If the record has an older Date and the status is Upcoming service check, I want to change the status to past service check. I am using an Airtable automation trigger called “when a record enters a view” .Your help will be appreciated.
Aug 20, 2022 04:43 AM
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.
Aug 20, 2022 10:13 AM
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:
I have four automation that controls this process.
Aug 20, 2022 04:50 PM
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.
Aug 20, 2022 07:34 PM
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
Aug 21, 2022 06:00 AM
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.
Aug 21, 2022 07:56 AM
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?
Aug 21, 2022 09:04 AM
Here is a screenshot of the table.
Aug 21, 2022 09:19 AM
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.
Aug 21, 2022 06:35 PM
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.
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.