Help

Unique number using year and week numbers

Topic Labels: Formulas
Solved
Jump to Solution
1131 2
cancel
Showing results for 
Search instead for 
Did you mean: 
joelgabauer
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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)
  1. 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]

See Solution in Thread

2 Replies 2
Kamille_Parks
16 - Uranus
16 - Uranus

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)
  1. 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]

Thanks so much! I got this working.