Unique Identifier Generator

Meghan_550057

Looking for some guidance on how to generate a unique identifier based on an existing table of all previous IDs and add 1 (+1) to the highest previous ID in the table. Example table below:

My thoughts on steps to take below but can't fully be used because my string isn't a number - it's a string containing text and numbers (ex/ ABC2):

1. Tables: Maximum Data in a Column: pull highest ID existing in the table into a field.

• ONLY works for numbers, not for text+numbers.

2. Numbers: Perform basic math or_ Fields: Field Formulas_: add +1 to existing largest ID (found in step #1) to generate new ID

• Perform basic math ONLY works for numbers
• Field: Field Formulas adding +1 requires more complexity in javascript to allow for adding to only the number portion of the text + number string

Looking for guidance on how to truly extract the largest current ID in the table. I can sort the table in descending order but the Extract cell action only exists for Excel so I can't get the actual value out of the cell itself. Also looking for guidance about how to add +1 to the existing string to generate the new ID.

Thank you!
Meghan

Thomas_937381

@Meghan_642561 Can you maintain a separate rowIndex column? Doing so would allow you to use Tables: Maximum data in a column.

Are your unique identifiers a fixed number of static characters? Always the same prefix? A few formulas that might be useful, could be used either in Fields: Field formulas step, or Tables: Apply formulas to columns.

Say a field unique-identifier is equal to ABC123. If your number of characters in the ID are fixed but prefixes vary, you may need something like this (would give you ABC)
result = fields['unique-identifier'].substring(0, 3)

This will extract the numeric portion and add one (1) to that (i.e. 124, we'll call it plus-one):
result = (Math.floor(fields['unique-identifier'].match(/\d+/))+1)

You can concatenate the value with one added and your prefix using something like this (i.e. ABC124):
result = fields['remove-sequential-number'].concat(fields['plus-one'])

Thomas

Jeff_146001

With the action, Tables: Apply formulas to columns, you can use rowIndex to refer to the row number.

columns['id'] = rowIndex;

Meghan_550057

@Thomas_151920 @Jeff_146001 - thank you both! I put together a sample of what you both recommended.

@Jeff_146001, with suggesting the Tables: Apply formulas to columns action, is my thinking below correct with how you'd approach this?

1. New request comes in via Catalytic web form
2. Tables: Add a Row action to add a new row for new request to existing imported Unique Identifier Table with new request details mapped to columns (ID column left blank)
3. Tables: Apply formulas to columns action to apply formula columns['id'] = 'ABC' + rowIndex; to entire ID column (every time a new request is made/the workflow runs)
4. Tables: Copy a table to another table action with Overwrite existing table set to True for the existing Unique Identifier Table.

Since Tables: Apply formulas to columns generates a new table and technically is applying a formula to the entire column, not just the new incoming request row in the table, I would think those would be the required steps. Let me know if that's in line with your thinking.

Thanks again @Thomas_151920 @Jeff_146001!

Meghan_550057

For context, I want to make sure that's the only way to really accomplish using Tables: Apply formulas to columns. My concern with that approach is timing of new requests coming in to the same workflow. If two requests are initiated at similar times, it's possible that with the timing, the step #4 above could overwrite a new request row add. We could certainly have a separate process generating the unique IDs but the desire was to use the unique IDs throughout workflow runs to continue to update rows.

Hope that helps clarify. @Jeff_146001

Jeff_146001

@Meghan_642561 I'm not sure of the reason for step number 4. Why not write directly to the table where you need this to live in steps 2-3.

For Step 3, I recommend you add IF logic to only update the value if the cell is empty. Something like this:

if (!columns['id'] || columns['id'].length === 0) {
  columns['id'] = rowIndex;
}

Meghan_550057

Thanks for the IF logic @Jeff_146001!

Reason for step #4 is that Tables: Apply Formulas to columns outputs a new table. So it won't actually write to the table I need (Unique Identifier Table), it just uses the data from that to output a new table (hence needing to overwrite the Unique Identifier Table with the output from the formulas).