Is there a way to limit the number of characters in a particular column?

Xochitl C.
Xochitl C. ✭✭✭✭
in Smartsheet Basics

We are gathering information related to tickets though a smartsheet form. We have a particular column (primary column) to record the ticket number which is alpha numeric characters. The ticket # is always 11 characters, since the smartsheet is limited to managers and the form is only used by agents we want to prevent agents to enter wrong data (more than 11 characters or less than 11 characters)

I see in other posts that this can be set only in the comment sections by adding a formula =IF(Text@row >= 100, "Limit Reached. Please use less characters")

My question is if there a way to set up the form to warn people when filling out this particular field, the ticket number?

Thanks in advance for your help!

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Answer ✓

    Note - even if you have a column with the "error check" formula…you can use the results AFTER the submission but not DURING the submission. Meaning, if you put that column on the form it will not work, it will not show an error. Formulas don't run on forms, they only run once the row is created in the sheet, which happens when the form is submitted.

    So, I suggest have an Update Request automation on the sheet that watches for that error text / checkbox checked as a trigger and sends a request back to the user to have them update their ticket number on the sheet. That update will update the existing row and won't create a new one.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

    · Share on FacebookShare on Twitter

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni

    Unfortunately no there's not a way to limit length. You can add some descriptive text to help. In the sheet that captures the results, you can have a formula check the length and flag ticket number errors. You could also set an Update Request automation to request that the ticket number be corrected by the submitter.

    That formula that you posted also will not work. To capture an error in another column, use this formula and set it as a column formula.

    = IF ( LEN( Text@row) <>11 , "Ticket number incorrectly formatted")

    or for a checkbox column

    = IF ( LEN( Text@row) <>11 , true)

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

    · Share on FacebookShare on Twitter
  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Answer ✓

    Note - even if you have a column with the "error check" formula…you can use the results AFTER the submission but not DURING the submission. Meaning, if you put that column on the form it will not work, it will not show an error. Formulas don't run on forms, they only run once the row is created in the sheet, which happens when the form is submitted.

    So, I suggest have an Update Request automation on the sheet that watches for that error text / checkbox checked as a trigger and sends a request back to the user to have them update their ticket number on the sheet. That update will update the existing row and won't create a new one.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

    · Share on FacebookShare on Twitter
  • Xochitl C.
    Xochitl C. ✭✭✭✭
    https://community.smartsheet.com/discussion/comment/437735#Comment_437735

    Brian, this is a great idea!!! thanks SO much for your insights!

    · Share on FacebookShare on Twitter

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video