1 Reply Latest reply on Jul 8, 2014 1:54 PM by philmodjunk

    Create list of unique records after comparing two files

    AllenFuller

      Title

      Create list of unique records after comparing two files

      Post

           I'm new here and apologize if this has already been covered. I've read several related threads, but none that seem to address this issue...

           I have two files of contacts that are largely duplicative. Each contact has a unique ID. In theory, I would like to add both as separate tables in an FM database (I'm using FMPro Advanced 13), then create a list of the unique contacts from List A that are not in List B, and also a list of unique contacts from List B that are not in List A.

           Basically a diff file.

           Is this possible using an FM script, or do I need to a separate software? If an FM script, any suggestions on how to build it?

           Thank you for a novice question! Looking forward to getting to know FM better.

        • 1. Re: Create list of unique records after comparing two files
          philmodjunk

               There's no real scripting required, but definitely some some database design work needed to do what you want here.

               

                    then create a list of the unique contacts

               Does that mean that you have records in either file's table that are not unique within that one table? I will assume that you do not have records with duplicate IDs in the same table, but if so, let me know as there's an easy way to filter out the duplicates.

               Take either database file and open Manage | Database | Relationships.

               Use the far left bottom button to create a new table occurrence box on your relationship graph.

               Use the Add Filemaker Data Source option in the drop town list in the dialog that appears to select first the other file and then the other contacts table. You now should have an occurrence of both contacts tables in the same relationship graph.

               Link the newly added table occurrence to an existing table occurrence of the other contacts table by their ID fields.

               Let's refer to the two table occurrences as Contacts A and Contacts B. (You can double click a table occurrence box to open a dialog where you can rename a table occurrence.)

               If you go to or create a layout based on Contacts A, you can place the ID field from Contacts B on this layout. If you then enter find mode, specify an * in this field and select the omit option from the status tool bar area, you can perform a find and get a found set of all records in Contacts A that are not also found in Contacts B.

               You can go to or create a layout based on Contacts B and use the same method to get a list of all Contacts B records that do not match the ID of a record in Contacts A.

               No scripting required to do any of that, but you can script the find if you wish.