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

Options

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!

Best Answer

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    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!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    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!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Kelsee Katsanes
    Options

    Hi @bisaacs,

    This helped! Thank you so much:)

  • Kelsee Katsanes
    Options

    @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
    bisaacs ✭✭✭✭✭
    Options

    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!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Kelsee Katsanes
    Options

    @bisaacs

    You are AWESOME! That worked perfectly!