Use formulas in an interface number elemtent

[joelgabauer]

Hi All, I’d like to create a unique number for a record based on the year and week. So for example, I have a date field which I use as YEAR(Date)&" - "&WEEKNUM(Date). So if the date was today this would give me an output of 2020 - 43.

Now, I want to add a counting number for each record with a date in that week in order of when the record was created. So the first record created with a date in that week would be “2020 - 43 - 1” and the second record created would be “2020 - 43 - 2” and so on and so forth. But when the week changes to the next week I want the counting number to start again, so it would then go “2020 - 44 - 1”, “2020 - 44 - 2” and so on.

I just don’t know how to do this and would appreciate any help thanks.

[Kamille_Parks · Accepted solution]

You could do this for all records going forward using an automation with a script action step:

1. Add an automation triggered when a new record is created in the appropriate table.
2. Add a Script Action step that has one input variable (“weekNum”) set to be the value from the formula field you have that gives you YEAR - WEEKNUM.
3. In that script action step use this code:

const inputConfig = input.config()
const weekNum = inputConfig.weekNum

const table = base.getTable("Table Name")
const query = await table.selectRecordsAsync()
const records = query.records.filter(x => {
    return x.getCellValue("Name of formula field") === weekNum
})

const position = records.length

output.set("position", position)

4. Add an Update Record action step that updates the trigger record, and set it to update a single line text field like this to combine the value from your formula field with the appropriate number added to the end:
   [Record (Step 1: Trigger) ... {Formula Field}] - [Data (Step 2: Run a script) position]

[joelgabauer]

Thanks so much! I got this working.