Primary column values - check and prevent bad data entry

Options

Hi Smartsheet community, I hope that you can assist me with this. We have a sheet that allows editors to add rows that provide additional information about items in an inventory. The primary column identifies the item in inventory by a unique ID. The value that they enter should follow a prescribed format, and value. For example, it should begin I-, M-, or P- and be followed by at least 1 digit. It is not nullable -- blank or null is not a valid entry. Also, it should not duplicate any other value already entered in the primary column*.

Is there a way to do this?

After exploring the column properties, and Automation rules that can run when a row/cell is changed, I can't seem to find a way to do this. I may have missed it.

Or, perhaps there's some other means, such as a requiring that data be entered another way (via a form?) and the checks could occur before the data are stored in the primary column.

Thanks for any advice, Peter Spung | pspung@northcarolina.edu

*P.S. Ideally, it would check that the value entered exists is in the inventory. That's in a database table, and a heavier lift. I'll attempt to address that later; "2.0" of the data entry checker.

Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Options

    @Peter Spung In my experience, the fewer folks that can actually access a sheet directly the better. So I would almost certainly use a form to collect the input.

    On the form, you could ask for input for each segment of the unique ID, and then stitch these together in the sheet itself. So:

    • a dropdown list for I-, M-, P-
    • another dropdown (maybe) showing only the ten single digits (if zero is allowed)
    • ... and so on ... (you will need extra columns for each segment of course)
    • the column with the result is something like =[column a]@row + [column b]@row + [column c]@row

    (( Preventing a duplicate entry at point of entry is trickier, you are right. Checking for dups and sending an update request to the submitter is not that hard. ))

    Make sense?

    dm

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!