Change formula of formula field with script

[Sarah_Esteves1]

Hi. I’d like to write a script where I change the formula of a formula field when a record is updated. Is this possible?

I need this because I need all my values in a column to be the same date, so I configured this field to be: DATETIME_PARSE(“01/07/2022”, “DD/MM/YYYY”). But I need this date to be changed by my user through the dashboard (by editing a specific record). So I’d like to run an script that changes this formula, replacing the date part (“01/07/2022”) for the new one, picked by my user, so my entire column will get the new date value.

Anyone can help with this?

[TheTimeSavingCo]

As far as I know, we can’t make formula fields or modify formula fields with scripts I’m afraid

As a workaround, perhaps you could change the formula to point at a specific field, and then get the script to update every record’s field value to the new date instead?

[ScottWorld · Accepted solution]

Welcome to the community, @Sarah_Esteves1!

Unfortunately, unlike most database apps and spreadsheet apps on the market, Airtable does not support “global fields”, which is what you are looking for. A “global field” is one single field that can be seen & used by all other records in your base.

Be sure to email support@airtable.com to request that they support global fields. Airtable almost never honors customer requests, but it’s probably still a good idea to get your email into their queue (in case Airtable gets better management in the future, fingers crossed).

But you can hack your way to a global field with a workaround.

You can do this by creating a “utility table”, meaning that you create a table that only has ONE RECORD in it. This ONE record will have your ONE date field in it that you would like to be seen globally/universally.

Then, in your normal table of records, you would need to add a linked record field that points to that utility table.

The tricky/workaround/hack part is that you then need to make sure that 100% of your past & future records in your normal table always link to that ONE DATE RECORD.

You can update all of your past records by using the Batch Update extension. For all of your future records, you would need to create an automation that updates the linked record field upon creation of a new record.

Once all of your records in your normal table are linked to the one date record, you may need to take one extra step of creating a lookup field in your normal table that looks up the date field (or a rollup field that rolls up the date field with the formula values). These fields will show you the same date for every record in your normal table, and you can use these fields in formulas as well.

[Sarah_Esteves1]

Hi, @ScottWorld . Your suggestion really worked! I created the utility table, with just 1 record, that can be edited on the dashboard by the user, and configured an automation to fill a linked field from this table on my main table with this one record value, that runs every time a new record is created on the main table. Now all my main table records, since the creation, comes with the date that already exists on the utility table and it changes every time my user edit this date on the dashboard. Thank you very much!

[ScottWorld]

Woo hoo! That’s great to hear!! :partying_face: :tada: :raised_hands:

[Vivid-Squid]

Hi Sarah,
Does the field have to be a formula? Can you just make it a date field? If so, here is a script you can add to the Scripting Extension that will ask the user for a date and will populate the field in a specific view.

Edit the “Main”, “Grid View”, and “Date” to reflect your Table, View, and Date Field names respectively.

let table = base.getTable("Main")
let myView = table.getView("Grid view")
let date = table.getField("Date")
let newDate = await input.textAsync('Enter the Date as YYYY-DD-MM')
let query = await table.selectRecordsAsync({fields: []});
let records = query.records;
for (let i=0; i<records.length; i++){
    await table.updateRecordsAsync([
        {
            id: records[i].id,
            fields: {
                "Date": newDate,
            }
        }
    ])
}

[Sarah_Esteves1]

Hi, @Vivid-Squid . A script to update every single record one by one was taking too long, so I gave up this approach =/ I need all my dashboard to be updated instantly when the user changes the date. This is happening now with @ScottWorld’s suggestion. But thank you for your reply!

[Vivid-Squid]

No Worries, I am glad you found a solution, however the script is instant and updates all fields at once.