Form Data Validation Against Dataset

beckyb

The ability to control text form entry against a large dataset. For example, I have a form that went out to 1,500 employees. The primary column is their name. Now when I want to check who has vs. has not completed the form, I have to manually check for false negatives due to slight differences in how they typed their name into the form vs. how our directory lists their name (e.g. Johnny Doe vs. John Doe). I cannot make the primary column a dropdown to fix this, nor would I want to, as that would be a list of 1,500+ to scroll through. If I could set up a validation column that I could input our list of names from the directory, that would be a huge help.

Adrian Mandile CHESS

Hi @beckyb,

I suggest making your Primary Column a calculated version of a separate input column and make the input column on your form dropdown type with a list of your employee names.

Be aware that I have also found some performance degradation on sheets when dropdown option lists exceed about 2000 items. Hopefully, the new Table view (larger Grid view) will address some of these speed issues.

As users enter their first name into the dropdown box on the form, the list will reduce based on the entered characters. If you definitely don't want to use a dropdown column due to a large number of names, as you stated, then have the 1500 names in another sheet as a master reference list and have a helper column (checkbox/flag) look them up to identify if it finds a match (valid name entered) or not (invalid name).

beckyb

While this technically works, it's not user fool proof. What I want to avoid is someone clicking the drop down arrow, and scrolling down hundreds of names to theirs. Yes, we can write help text to start typing your name etc. but, again, not fool proof. Maybe if they added a dropdown display option of auto-complete text that would help…