Skip to main content

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.

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:

    oRecord (Step 1: Trigger) ... {Formula Field}] - oData (Step 2: Run a script) position]

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:

    oRecord (Step 1: Trigger) ... {Formula Field}] - oData (Step 2: Run a script) position]


Thanks so much! I got this working.

Reply


Terms & Conditions