3 Replies Latest reply on Jul 11, 2014 10:49 AM by philmodjunk

    Validating database data



      Validating database data


           Hello all,

           I'm converting databases for a client from FMPV5 to FMP13.

           I've found that I can go from V5->V7->V12 file formats and in the process FileMaker converts the database.   From what I can see it appears to work ok but the second half of my effort will be to confirm the few scripts and layouts work as they did in V5 and correct issues we come across.

           Like any database the data in the database is extremely valuable to the client so I'm looking to basically validate that the data is identical between FMPV5 file and the converted V12(13) file.

           First question:  Are they any tools that do such a thing?  IE I'd love to have a tool that I can point to the V5 file and the V12(13) file and have it iterate over the records and confirm that data in the records is the same.

           Second question:  If there is no external tool that will do such a thing can I do such a thing using two scripts?  One script in the V5 file that computes say a massive CRC on the data, then another in V12(13) that does the same thing?  Theoretically if the numbers are the same the data is the same.

           Third question: If I can do 1 or 2 then I guess I can export data from V5 to a file, run a program I write on that file to evaluate the data.  Convert the V5 to V12(13) export the data again and run it through the same external program.   I should get the same evaluation.

           I'm open to better ideas.  I know FM gives a conversion log but I'm not sure that will be good enough for my client.  I'll have to ask.  They run under some strict ISO and documentation requirements.

           Thanks in advance!

        • 1. Re: Validating database data

               If you took a copy of the original file, data and all and converted it as described, the data in data fields (as opposed to calculation fields) should be identical. It will be very unlikely that any will be different.

               But if you want to do a record by record, field by field comparison, you could export the data from the original file into a text file such as a merge, tab or csv format file. You can then import this data into a copy of your table in the converted copy and then use scripts plus a relationship to do a field by field comparison of the imported data with the converted data to ensure that they are identical.

               Note that I stressed this for data fields (text, date, number, container...), not fields of type calculation. When I converted from 5.5 to 10 several years ago, I found that the very calculation expressions were modified during the conversion process where many fields ended up enclosed in "getasnumber" function calls. If a very few cases, I found that I had to remove the added function calls before the fields evaluated correctly. So while this field by field comparison is very unlikely to catch an issue in a data field, it might spot an issue in a calculation field.

               Note 2: one change between then and now is how FileMaker indexes text fields. In the older versions, punctuation characters were not included in the indexing while they are included now. Thus, in FileMaker 5, a relationship that matches by text fields would match the text "Name" successfully to the text "Name." or even the more subtle "Name " (note the space after the "e"). But these values will not match in current versions of FileMaker.

          • 2. Re: Validating database data

                 Hello Mr. Phil,

                 Thanks so much.

                 While I agree that files converted as described "should" be the same, the client will probably want some kind of verification that they are the same.

                 I think my best bet is to export, compute on exported data, then run the same computation on data from V13 (after converted).  

                 I understand about the calculation fields and the client is already aware that somethings like scripts and layouts may need to be visually / hand validated.  I think most of these older databases they have do not actually have computation fields or at least not many.


            • 3. Re: Validating database data

                   I like the idea of "performing calculations on the data" as a way to verify them, but...

                   I seem to recall that there was a bug back in FileMaker 5 that sometimes resulted in incorrect rounding of the data. Saw that in some of my records.

                   Thus summary and other aggregate statistical values produced in FileMaker 13 might not match the same values produced in FileMaker 5 even though the data is the same.

                   So if you are going to use that kind of verification, be sure to use the same version of FileMaker to verify both sets of data.