4 Replies Latest reply on May 31, 2009 10:21 AM by FentonJones

    Comparing databases



      Comparing databases


      Hi all,


      I am trying to compare two databases. I mean, I am migrating from an old platform to a new system. Actually the company decided to go on with the old database and the new one for some weeks. During this period, I need to periodically controll the data, to check if the old system and the new one are having the same records.


      The point is, is there a way to compare this records on filemaker? I mean, can I build a function where if the code is equal, but the quantity is different filemaker writes me the record?


      I did this just connecting the code of the two tables on the relationships graphic, but it seems that filemaker does compare codes only if they are in the same order. I mean, if in the table 1 the code is at the position 2 and if in the table 2 the code is in the position 3, filemaker does not compare the two codes, also if they are equal.





        • 1. Re: Comparing databases
             No one that can give me an answer??
          • 2. Re: Comparing databases

            It's pretty hard to answer because of the "whether they have the same records?" question. Unless people are entering the same records and data twice, once in each system, then of course they won't have the same records. Are different people entering the same data into each system?


            You can fairly easily see the count of total records in each table, to see a difference in number of records.


            You can also create a relationship between two FileMaker files, using fields which should be the same. This will allow you to see which records match on that field (or those, if a compound relationship). You can do a Find, "*" in a field which must have data will Find only those with a match. You can put specific fields on the layout (based on that relationship) that you want to see any differences, and compare them visually.

            You can create calculation fields,

            Case ( not IsEmpty (relationship::ID) and field ≠ relationship::field )

            The result is number, it will produce 1. Use to Find matches where a relationship matches, but that specific field is not the same (pretty slow, as it is an Unstored calculation).


            • 3. Re: Comparing databases

              Thanks Fenton.


              The problem is that the company is changing from an excel database. The boss wants to go on inserting datas from both system for about one month (I hope not more). So, I need often to compare the two methods to see if there are any differences. This is the reason why I am trying to automate this step with filemaker.


              Let's discuss about your first solution, the one with the relationship, I tried that, but It seems that filemaker compare only 1:1. I mean, the comparison is made between records with the same filemaker progressive number.



              • 4. Re: Comparing databases

                I didn't know when you said "systems," you really meant 2 different applications, not just 2 different FileMaker solutions. It is going to be difficult to compare the results of a complex Excel spreadsheet to FileMaker, other than visually, and "spot-checking." The only way to get any further would be to convert the Excel spreadsheet to FileMaker, and attempt to match the data. I don't know how complex the Excel SS is, but it is possible to convert, if you understand the limitations. FileMaker can convert only a simple flat area of a spreadsheet. But it supports different Worksheets, and even defined Ranges. You would have to bring these in one at a time. Whether you can put them back together within a relational database depends upon your understanding, and on the data.


                Which brings up the point about the IDs (progressive number). I know it is possible to "serialize" an Excel file with Fill Down. I don't know if it's possible for that to happen automatically upon data entry (elsewhere in the row). It is also easy to serialize a FileMaker table, either upon creation (via an auto-enter serial ID), or afterwards, using Replace (with the same serial number options). This method would not work unless data was first entered in an Excel row in exactly the same sequence as a record was created in FileMaker; always.


                If you cannot absolutely trust the sequence, you would have to rely on some other field(s) to establish a temporary relationship, like: first name | last name | address number & a few letters. I use " | " here to denote a "compound" relationship, with all 3 fields. This may not be 100% accurate, because of typos. This assumes you've managed to convert the Excel to FileMaker. I say "address number," because the last part of an address, especially the "St.", "St", "Street", "Rd.", "Road", etc., are the most problematic.


                Once you match most of the records, you can start comparing. Even with 2 FileMaker files of similar structure this can require some skill and patience (in converse proportions, but always patience). It is pretty hard to estimate the difficultly, as we do not know your structure or data.