FILEMAKER: Potential Issue: Imported Data And Validation

** This was posted to my FileMaker Found Set blog but I thought it might be worthwhile to also post it here, for the readers that do not subscribe to the Found Set blog. **

From Dwayne Wright
www.dwaynewright.com
info@dwaynewright.com

When I originally wrote this content and FileMaker 6 was the current version of FileMaker, the article was titled Potential Issue: Imported Data Is Not Validated. That has changed and you now have the feature of validating imported data. This new feature is welcomed but it does have another backhanded issue that you might have to deal with. In other words, just because you can validate imported data during an import, it does not mean you should.

If you are not familiar with field validation, here are some links to read up on it.
Introducing Field Data Validation
Validation Failure Messages To Users
Attributes With Special Needs ( Auto Enter - Validation )
Locking A Record Via Validation
Calculated Auto Enter, Calculated Validation And Calculated Replace

Back in the day (pre FileMaker 7), when FileMaker imported data, it did not perform any error checking or validation. So you could have a field setup to have a critical piece of validation logic attached to it and that setup would be ignored during the import. Even date fields with imported data can have non-date information in them, which can have a variety of problems. The field will display a ? in this case. So, as a responsible developer, you would have to build in routines in scripted imports to perform the replace command to make sure that everything imported would fit nicely with the already stored record information in the file.

FileMaker now has a new option in field validation. It can be set to validate the field on data entry or always. For most circumstances, always is pertaining to import routines. So if you have the field validation setting set to always, your imported data into this field will be validated. If you have the field validation setting set to “only on data entry”, your imported data into this field will NOT be validated.

So you set the validation to always, you have imported some data into your validated field and some of the data to be imported does not pass the validation you set.

Now what happens?

Does FileMaker stop at each import validation error and pause? This could be pretty annoying if you are importing a lot of data. Does FileMaker allow you to program multiple responses to the validation error checking? That would seem that it could become some pretty intense programming. Does FileMaker import the good data that passed validation and ignore the rest? That would seem to be illogical as well because you wouldn’t know what was not imported. Does FileMaker import the good data and automatically branch the bad data to a separate export file for review?

As of FileMaker 9 (and I hope some enhancement is down the road), FileMaker imports the good data, rejects the bad data and displays a dialog box of how many records did not successfully import. It does not indicate in any way, shape or form what specific records did not make it into to file. So you may know that 10% of the overall potential imported records failed validation, you know these potentially dangerous records are not within your FileMaker table but you are not empowered, via any FileMaker settings, in targeting the records that failed validation.

This might be OK if you are importing sales leads because they may not be critical to a business function. If you are importing critical data, then ignoring the imported data that failed field validation is not an option for your business needs.

So you can see how this new feature can have a bitter aftertaste. So you need to polish up your scripting skills again to find a resolution. Most developers agree upon an “all or nothing” approach to importing data that has strict validation settings. If any error is detected, all the newly imported records need to be rejected and the imported data source cleaned up. So what most of us do is have a shadow table that imports the data, checks for validation errors, allows the user to fix the records failing validation and then that cleaned data is then imported into the live production database to be worked upon by the database users.

Here are some links to other posts that might be of interest in regards to this topic...
Import & Export Found Sets
Importing Data Into Filemaker Explored ( part 1 )
Importing Data Into Filemaker Explored ( part 2 )
EXAMPLE: The Basic Import
EXAMPLE: Import (Match / Replace)
Importing Concerns With Auto-Enter And Repeating Fields
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.
====================== ADVERTISEMENT ==============================
For more information on the InBizness SOHO 2.0, please visit /inbiz/soho2/inbizsoho2.html