# Unique number using year and week numbers

Topic Labels: Formulas
Solved
1221 2
cancel
Showing results for
Did you mean:
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
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]`
2 Replies 2
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]`
5 - Automation Enthusiast

Thanks so much! I got this working.