Validating particular sets of fields

[jn08]

I’d like to have the option to check a set of fields for missing or incorrect data. Ideally with a clear report or formula that shows which fields are missing (or incorrect). What’s the best way to approach this problem? I can’t seem to wrap my head around it coming to Airtable from a Filemaker background. I would have done this with a script in Filemaker, but there may be a way to do it with a combination of views, formulas and linked records.

[W_Vann_Hall]

I have begun to experiment with integrity checks for particularly significant data during data entry. (Ideally, I’d like to have a handful of canned routines, preferably associated with a stand-alone data-integrity table, but I’m not there, yet – nor am I convinced it’s even possible to get there.) Typically, the process is to force a link from the record being entered to one that performs validation in a second table, with the ‘yea/nay’ result brought back to the original table by means of a lookup or rollup field.

To date, I’ve not posted very many examples of such code. Off the top of my head, you might want to look at the demo base I built in reference to Create sum based on a table’s fields/records (linked in reply #3). [Ignore most of that base, which cleverly manages to do undoable things so cleverly I – the guy who wrote it – just spent a half-hour trying to figure it out. What you’re interested in is the ‘TypeSize’ table, one of the base’s two main data-entry points. This table is used to indicate, for a given class of clothing item, what size an individual wears. In practice, from that table the user first selects (as a punch-through to a second table) the individual and (again as a punch-through) type of clothing and then enters a one- or two-character string denoting size. As Size is a free-form text field, the base checks the user’s input against a list of valid sizes for the clothing type. Records specifying an invalid size for the clothing type are flagged in the SizeOK field.)

You can find a slightly different approach in my data de-duplication routines described in my reply to a feature request post on data de-duplication. That write-up discusses two different types of duplicate control; it’s the code for ongoing detection of potential duplicates that’s of interest. Briefly, whenever a newly entered record appears sufficiently similar to an existing one, it triggers a possible duplication warning; however, this warning is flagged at the base, not record, level. (In other words, in the clothing order demo mentioned earlier, if the user enters an invalid size for a type of clothing, the record containing that mismatch is flagged. In contrast, when a potential duplicate record is identified, the warning flag appears on all records, not just the potentially offending one, as it is safe to assume the most recently entered record is the culprit.)

[jn08]

Amazing work. Thanks for this. My problem may be slightly easier to solve in that I’m not trying to detect validation issues on the fly and continuously. I have a project management system for books that as they go through each stage of the project development cycle they need to be checked to make sure all the required data is there and correct. As each title reaches a new project milestone it gets checked to make sure the fields that need to be filled in at that stage have been filled. It’s an ongoing process, so I wouldn’t want to make fields mandatory or validate continuously. Any idea on how to set up a sort of report that shows whether a set of fields of a given set of records are correct?

[W_Vann_Hall]

Completely off the top of my head, so I may be way off track, I’d first look into encapsulating each stage’s validation processing in a separate table. At the appropriate point, you create a link between your book record and the validation record – possibly along the lines of the Check field in my duplicate detection demo. (Actually, not quite like that, as that is a many-to-one link, where you’ll most likely want a one-to-one. The mechanism for establishing the link could be similar, though.) That would trigger processing only when appropriate and presumably return a yea/nay response as your project milestone.

If you decide that’s a stupid idea before I do, or if you’d like to send me a link to your base, feel free to do so here or in PM.

[jn08]

OK what I’ve done (and it may end up being an ass backwards way of doing it) is created one long formula field that checks for blanks or other requirements and returns a list of missing items with a check or cross separated by new line breaks. I can then use a view that filters that formula field and only shows values that contain a cross – thus zeroing in on the records with missing fields. This should work for my purposes, even though it’s not ideal.

It would be great to be able to refer to fields in a linked table via a formula without having to have that field as a lookup column on the current table (which is possible in Filemaker) but I can work around that limitation for now.