Change formula of formula field with script

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.

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!

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,
            }
        }
    ])
}