Database Fields Custom Validation - e.g. regular expression, IBAN Bank number, phone
While I am pleased with the site overall, it's clear that it does lack some features that I consider essential, although not critical.
One such feature is field validation of database records.
Currently there is very limited field validation.
I would request more robust import and user entry field validation capabilities to keep data clean. Without this capability, the database very quickly starts to contain garbage fields.
For example, currently a user may enter their phone number in any format they choose, including completely invalid phone number entries. The same is true for zip code fields and others.
-
Roger Brooks commented
For validation of country, state/province and city/town this might help:
https://www.html-code-generator.com/html/drop-down/state-name -
Deborah Stoll commented
We need a number contact/membership field. Currently there is no way to sort numerically. So my list is 1,11,12,13,14,15,16,17,18,19,2,21,22,23,24,25,26,27,28,29,3,31...
I need to use this for the directory gadget. Thanks,.
-
Robin Sapiro commented
Just a thought, but with Integromat you can trigger a scenario whenever a membership record is added/updated. In this way you could potentially grab the fields that need to be formatted and/or validated and either by using regex within Integromat or by passing the data off to some other application do the validation and formatting and then update the applicable member record with the correct data.
-
Julie commented
I appreciate this is a minefield, especially for organisations with international memberships. It would be enough to support a plugin linking to one/some of the many existing 3rd party validation products out there..
-
Patrick commented
Any update to this yet?
-
Joanna Straub commented
Adding my voice to this. While we can (and will) export and clean up data, having it entered right the first time would be a huge time saver.
-
Skotty commented
Does anybody have a work-around on this, or has their been any progress on WA's end? Even just allowing us to set our own validation...
-
Alex Sirota commented
Just an aside -- database hygiene is always an issue even in the most complex and expensive products. There is always a way people can break validation with ingenious lack of information or purposely provide invalid data. Until a feature is available built in, Excel exports of problematic fields along with User ID can be used to bulk clean up data.
There are great Excel formulas that are easier to implement than regular expressions (many don't know REGEX patterns and directives). These formulas can be used to "clean up" data using built in Excel formatting rules for phone numbers, formulas like MATCH, LEFT, MID and RIGHT and data transformation tools like VLOOKUP, IFS and other means to convert values based on specific input. It takes some Excel smarts, but once you have a good workbench in Excel you can clean up data all day long :)
https://xltools.net/data-cleansing-in-excel/
https://support.office.com/en-us/article/top-ten-ways-to-clean-your-data-2844b620-677c-47a7-ac3e-c2e157d1db19 -
htfairfax commented
Bulk mailings require a zip+4 code. A lost of membership applications include only 5 digits and a street address. Adding a lookup to check/add the additional 4 digits should be straightforward and would be helpful. Tal Day, Alexandria Historical Society.
-
Thank you, Jim. We have this requests in our forum already, I've merged yours here.
So far, we're have no actual plans on doing something about this, but when we do, we will comment here and you will be notified. -
JAMES BRADLEY commented
It was suggested that I ask my question here, to wit:
James Bradley
Jun 8, 7:45 PM EDTHowdy All,
My name is Jim Bradley. I am the Membership Chairman for the B-52 Stratofortress Association and maintain the Membership Database. I have a question: Most database allow setting a field so that only certain appearances happen or that certain characters may be entered in certain fields. Example: Only CAPITAL letters or only a specific date format, etc. Is this possible with our database? Or is it something planned/wished for in future? If it can now be done, where will find the instructions?
Thanks for any Help given.
Jim Bradley, Membership Chairman
B-52 Stratofortress Association -
Cindy Cooper commented
Phone and email fields are quite important. State (in US) could be done as dropdown I suppose but that is not as user friendly as allowing only a two digit state entry typed. The directory and lists are not very "professional" looking when phone and state are all different formats. The map service we use won't accept the wide variety of state entries we get and we have to edit them by hand.
-
Andrew Steele commented
Field validation is important to our organization to keep our data clean and organized. Common fields (e.g. phone, zip code) would be a great start, but I agree with Evgeny - giving the administrator a way to input custom validation via regular expressions or js is the way to go. Then a help page could be created that shows a list of common expressions/code snippets that Admins can enter. And if an Admin needs a validation that's not on the list, they can ask the community for assistance creating the code they need. This way the WA coders aren't baking in every possible validation to all instances of WA.
-
As soon as we have, we publish it here. So far, we have no plans yet.
-
Anne commented
Any updates on this requested feature?
-
Suzanne commented
In particular I would like to see the phone field:
Phone number -- (###) ###-#### Ext ####
with the Extension #### being available either for an office extension OR where an international phone number is longer than 10 digits -
Maurine Killough commented
have input masks for phone numbers so it looks good like (xxx) xxx-xxxx, for example
-
Michael Hall commented
I agree. I would like to be able to add a mask feature to membership fields; example: if I'd want a member to enter a phone number into format (###) ###-####, I'd like to specify that on the field so that error entries are not accepted. Our members don't follow the text that explains the field's format. Would also like to set a field's maximum length of data/characters.
-
Cindy commented
I have to concur with my fellow users. Without controls on how data is stored - you get messy data. I spend hours going through exports to clean up phone numbers especially.
-
Goldmine jody commented
We do a printed roster so having the contact details stored in the database in a uniform fashion is important. Phone numbers and postal codes especially.