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

Kelsee Katsanes · 05/13/24

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 · 05/13/24

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 · 05/13/24

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 · 05/13/24

Hi @bisaacs,

This helped! Thank you so much:)

Kelsee Katsanes · 05/13/24

Kelsee Katsanes · 05/13/24

@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 · 05/14/24

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 · 05/15/24

@bisaacs

You are AWESOME! That worked perfectly!

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

Best Answer

Answers

Categories