Date stamp script in automations is very slow

[Lee_Mandell]

I’m trying to use an automation with a script to create an datestamp that be overridden. This is being used for.a microgreen farm and there are times where we will enter new plantings hours after the actually planting and the records need to be back dated. Normally just the current datestamp will work but there must be the ability of override it. I will also be doing this with a number of other values from a linked table. Here is the code for the script. There are ~7k records and the execution time if 17-18 seconds which is not acceptable. Is there anything I can do to speed this up? Hopefully I’m missing something simple.

// the table containing record links, and the linked-to table

let rootTable = base.getTable(“Planting Log”);
// the record with the links we want to summarize
// we need to create a ‘recordId’ input variable connected to a record trigger
let config = input.config();
let recordId = config.recordId;
// query the table and find our record according to its id:
let rootQuery = await rootTable.selectRecordsAsync();
let record = rootQuery.getRecord(recordId);
await rootTable.updateRecordAsync(record, {
// Change these names to fields in your base
“Date Sowed”: new Date(),
});

One thing I don’t understand is why I have to retrieve all records when I already have the id of the record I want. It seems that this could be done much more efficiently on the backend.

Lee

[Bill_French]

Lee,

One way to make this fifty time faster is to update 50 records at a time. But I also have to wonder if a script automation is the right approach. Airtable has a pretty low ceiling for automations so with a lot of records, you might swamp your quota rather quickly.

[Lee_Mandell]

Hi Bill,

Thank you for your reply.

This automation is for new records that are created via data entry so doing them in a batch would not be an option. The idea is to add default value functionality where Airtable doesn’t provide it (like date fields). I could tolerate 1-2 seconds but even that seems like much too long. I believe the bottleneck is having to retrieve all records in a table and then filtering them instead of have search parameters and letting the heavy lifting to be done on the back end. I really like the functionality of being able to run a script when a record is created but the slowness is a killer.

It’s too bad you can’t pass the entire record to the script instead just the recordId. Or an alternative would be to pass a pointer to a field.

Lee

[Bill_French]

Lee_Mandell:

I believe the bottleneck is having to retrieve all records in a table and then filtering them

I have a hunch that this is not the bottleneck you are experiencing. I’m suspicious of the use of input() in this context and the likelihood that it is a slow method.

Besides, the objective is simply – given a new record, set a default date, right?

If so, why not…

• Create a view that always contains records where the target date is empty.
• Fire the automation when – and only when – a record appears in this view.
• Set the default date which causes the record to be removed from the view.

This would create a sustainable approach that always discovers records that are new and contain no such date values and all by reading only the records that need to be updated.

[augmented]

I would defer to Bill in most things Airtable/script-writing related, but I will suggest another approach.

Add a Created Time field to your table. Create an automation that triggers on record creation, then set the Action to Update Record where you set “Date Sowed” to the Created Time field.

I just tried it and it works.

[Bill_French]

augmented:

Add a Created Time field to your table

Ha ha! Duh. Love it.

[Lee_Mandell]

Thank you both for your help. The view was the trick that sped things up enormously. The problem with using the create time is having to add an extra field. This is one of the things that drives me nuts in airtable is the proliferation of fields to do calculations. With scripting and automations I can probably get rid of a lot of my fields.

Is there a way to access the created time directly in a script without having to create an extra field. I’m trying to do it in a script but am having timezone problems with new Date(). I can hard code the offset to my local time zone but that is a bad practice since it will break if used in another timezone. I wish I could use the Moment library for this.

On to the next step which is to populate a number of fields in the main table with values from a linked table when the link is established. I have it working but it is taking longer than I would like (5-10 seconds). Should I post the code or open a new topic?

[kuovonne]

Lee_Mandell:

Is there a way to access the created time directly in a script without having to create an extra field.

Currently, it is possible with custom apps, but not with scripting.

[Bill_French]

Lee_Mandell:

This is one of the things that drives me nuts in airtable is the proliferation of fields to do calculations. With scripting and automations I can probably get rid of a lot of my fields.

This is not an irrational sentiment. I have written about this issue at length. It creates convoluted and difficult-to-maintain data schemas. It obscures the true dependencies of the solution and makes a mess of the data itself.

Lee_Mandell:

I wish I could use the Moment library for this.

I hear ya’. Only in Custom (React) apps unfortunately.

[Lee_Mandell]

I know. I have a react app that is already pulling things from airtable. I use it for back end calculations to determine planting schedules based on past performance and current orders. I haven’t done too much UI work with the airtable data and none for mobile devices. It is the next frontier.

Thank you for your help.