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.

Find more posts tagged with

Accepted answers

Paul Newcome

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)

All comments

Paul Newcome

Are you able to provide some screenshots for context?

Jerome.m

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.

Paul Newcome

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)

Jerome.m

Hello @Paul Newcome ,

Many Thanks, This did the trick !

I just had to update the corresponding Column in my submission form.

Cheers !

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.