5 Replies Latest reply on Dec 17, 2014 11:54 AM by philmodjunk

    Comparing Values Among Many Fields



      Comparing Values Among Many Fields


      Hello everyone

      I have to develop a database which compares two lists of information, among many fields.

      Presumably the lists will be made in Excel.

      Lets call it list A and list B.

      List B will always be the latest.

      The list will contain information like SSN, Name, Address, Salery etc.

      How do I the most user-friendly way, compare the two lists in FileMaker?

      • I want my Database to make a rapport with just the slightest change.
      • I want my rapport to be made in Excel to then be imported in my Database.
      • I want a list over the records in the Excel-sheet, who haven't been touched at all.
      • I want a rapport over the records who already exist in the Database already.

      Is it possible?

        • 1. Re: Comparing Values Among Many Fields

          What problem do you solve by making your list in Excel instead of in FileMaker?

          Since you have an SSN field, wouldn't it be simpler to check for records with the same SSN?

          Or will there be multiple records in the database with the same SSN but with other fields different?


          • 2. Re: Comparing Values Among Many Fields

            Hi Phil

            The list is originally constructed in Excel, so that's one problem - but I would like to find the differences made in sheet B compared to A.

            As your mentioning, it's two huge Excel sheets and with multiple records, but I will have to do a lot of different search criteria.


            John Doe is having SSN X, Address X and Position X, all stored in Excel sheet A, but in different rows and columns.

            Now in Exceel sheet B, John Doe has the same SSN X, but he might have changed Address to Y and Position to Y.

            Is it possible to manage a FM Database, to do a so detailed search and then point 'em out?

            • 3. Re: Comparing Values Among Many Fields

              As I just pointed out in your other thread, most such "cross referencing" is done via relationships when using a relational database such as FileMaker. That's one of the reasons for using a Database in place of a spread sheet such as excel.

              A Record in table A can have one record only for John Doe, but that one record can then link to multiple records in other tables (I'll use SSN for this example though it is often not the ideal value to use for this) by SSN to show past addresses and past jobs as well as the current address and Job.

              The most efficient way to do this will require restructuring your data from the excel file into multiple related tables in FileMaker. This can be done through careful manual or scripted importing from the Excel files.

              Note I said SSN's are not ideal as data entry errors can result in records having different SSN values that should be the same and people--such as someone who has entered the country illegally and gives a fallse SSN--which can then match to someone else's SSN in your database and thus produce false matches. But I also understand that you have to do the best you an with the data you have to work with as well.

              • 4. Re: Comparing Values Among Many Fields

                Let's change criteria to _ID, although I get your point, but let me just try to rephrase your points.  

                Let's say I start from a scratch and build a FM Database.
                I will have to build a database, which consists of different tables and then create a script, which imports the data from Excel sheet A into the database (carefully imported). (Maybe just two table, but with according fields based on my Excel sheet, make a relation and then compare the two tables/fields with each other.)

                I'll then have a starting point and can then (carefully) import my Excel sheet B into my FM Database, to compare the fields based on the Columns (See below) 

                UnionSSN.NameSurnameAddress.Area CodeCityYearsDepartmentMonthly WageLPositionResignedVoluntary contributionEkstra voluntary contribution                

                Company contribution

                Pension StartEmployedScheme

                Is this correct understood or entirely false?

                • 5. Re: Comparing Values Among Many Fields

                  You are close. Much depends on the details of what exactly you mean by "compare".

                  What I described was to match a table of records with only one record for each individual to a table of the above shown data. A portal could be set up on a layout based on that table that lists only those records of your imported data that have the same ID (SSN, whatever) as that of the current record.

                  On the other hand, you can pull up a list or table view of your imported data and sort the records by SSN to see all records with the same value in SSN grouped together for easy comparison.