How do I customize a request ID based on specific form selections?

Kelsee Katsanes

iaI know how to create a request ID on the column grid but I wonder if I am able to customize based off of specific selections on form submissions. For example, Jane Doe selected content management (CM) in her form submission so the request ID would reflect that (Request ID: CM_0001), where John Smith selected quality assurance (QA) in his form submission so the request ID would reflect that (Request ID: QA_0001). How would I do this? I feel like this might require an automation or a formula set up but I am unsure how or if this would be possible. Any help/guidance would be greatly appreciated!

bisaacs

Hey @Kelsee Katsanes,

I don't know if there's necessarily a way you can have each form selection have it's own count (ie if there's one submission for Content Management then another for QA, they would each start counting at 1), as there's no way to use a formula with the Auto-Numbering column, and you can't have more than 1 Auto-Number column per sheet.

What you could do is use the Auto-Numbering column as a helper column (with it having whatever auto-number settings you want), hide it, then have an actual Request ID column that joins the auto-number column and the dropdown selection:

=IF(Department@row = "Content Management", "CM" + [Request ID]@row, IF(Department@row = "Quality Assurance", "QA" + [Request ID]@row))

And use this for your actual used Request ID column.

Hope this helps!

bisaacs

Hey @Kelsee Katsanes,

I don't know if there's necessarily a way you can have each form selection have it's own count (ie if there's one submission for Content Management then another for QA, they would each start counting at 1), as there's no way to use a formula with the Auto-Numbering column, and you can't have more than 1 Auto-Number column per sheet.

What you could do is use the Auto-Numbering column as a helper column (with it having whatever auto-number settings you want), hide it, then have an actual Request ID column that joins the auto-number column and the dropdown selection:

=IF(Department@row = "Content Management", "CM" + [Request ID]@row, IF(Department@row = "Quality Assurance", "QA" + [Request ID]@row))

And use this for your actual used Request ID column.

Hope this helps!

Kelsee Katsanes

Hi @bisaacs,

This helped! Thank you so much:)

Kelsee Katsanes

Kelsee Katsanes

@bisaacs I have another question in relation to the one I asked.

Is there a way to set up the Auto number that way it doesn't skip over different CM or QA requests? For example, how it looks right now:

CM_00001

CM_00002

CM_00003

QA_00004

QA_00005

CM_00006

I would like it to show like this:

CM_00001

CM_00002

CM_00003

QA_00001

QA_00002

CM_00004

Is there a way that way it doesn't skip over numbers? I think it might cause a lot of confusion on the backend if it looks like CM_00004 is missing with it jumping from CM_00003 to CM_00006. Any help would be greatly appreciated.

bisaacs

Hey @Kelsee Katsanes,

The best I could come up with is creating helper columns for each Department to keep track of the count and another column for the Request ID #, then use this formula to essentially increment the formula down the sheet:

=COUNTIF(Department$1:Department@row, "Content Management")

This formula was in a column titled "CM COUNT" (and there's a similar one in the QA COUNT, then in my Request ID # column I had this formula:

=IF(Department@row = "Content Management", "CM_0000" + [CM COUNT]@row, IF(Department@row = "Quality Assurance", "QA_0000" + [QA COUNT]@row))

That's the only solution I could really come up with. Unfortunately this means that the number of leading 0s would never change since the Request ID is just appending the count number to the end of the four 0s. Here's what the sheet looks like:

Hope this helps!

Kelsee Katsanes

@bisaacs

You are AWESOME! That worked perfectly!