Conditional unique KEY generation ?
Hello to all !
For my helpdesk activity, I am logging "tickets" numbers in a sheet database which are "uniques" and serves as my primary KEY, with a workflow of INDEX/MATCH to populate data to other sheets...
These "tickets ref" are populated trough a smartsheet Form...
Sometimes, we must work without a dedicated ticket number, so the form user input a "No ticket" tag... but this breaks the relationship, as we have multiples identical key...
How could I automate the fact that when "No ticket" is inputed in the form cell, (or maybe just a boolean box to tick), that a new incremental number is added automatically ?
Like "No ticket_001", "No ticket_002"... and so on ? So the unique key relationship could still work ?
Many thanks for your inputs !
Best regards.
Best Answer
-
You will need to insert an auto-number column (called "Auto" in this example) that has no formatting applied to it.
Then in a text/number column which will become your new "unique id" column, you would use:
=IF(REQ@row = "No REQ", "No REQ_" + RIGHT("00" + COUNTIFS(REQ:REQ, @cell = "No REQ", Auto:Auto, @cell <= Auto@row), 3), REQ@row)
Answers
-
Are you able to provide some screenshots for context?
-
Hello.
Thanks for your feedback.. there with some screenshot, I will try to explain better....
The "REQ" column is my "ticket /REQuest" number... those values are unique and are used as KEY value for INDEX/MATCH of rows data to other sheets.
Those REQ number are populated to the sheet via a Smartsheet Form. Sometimes this number is unavailable and the user input the "No REQ" value..
This creating "non-unique" value, and breaking the referencing formulas, I would like that column to "auto-input" an incremental numeric value
Automatically, when the Form user input "No REQ" value or eventually with the tick of button. Like so :
Thanks for your answers !
Regards.
-
You will need to insert an auto-number column (called "Auto" in this example) that has no formatting applied to it.
Then in a text/number column which will become your new "unique id" column, you would use:
=IF(REQ@row = "No REQ", "No REQ_" + RIGHT("00" + COUNTIFS(REQ:REQ, @cell = "No REQ", Auto:Auto, @cell <= Auto@row), 3), REQ@row)
-
Hello @Paul Newcome ,
Many Thanks, This did the trick !
I just had to update the corresponding Column in my submission form.
Cheers !
