Primary column values - check and prevent bad data entry

Peter Spung

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.

Dale Murphy

@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