4 Replies Latest reply on Apr 26, 2010 9:38 AM by mrvodka

    Find records from one table that aren't in another

    Wimmachine

      Title

      Find records from one table that aren't in another

      Post

      I am trying to do something fairly simple, but being Friday my concentration level is quite low.. so I need some help.

       

      I have 2 tables:  "store" and "import"  which are related by the "ID" field.  "Store" has a single record per ID, "import" can have multiple records with the same ID.  I am trying to find all of the records in the "store" table that are not in the "import" table.

       

      Example:

       

      Store contains the following IDs:

      XX01

      XX02

      XX03

      XX04

       

      Import contains:

      XX01

      XX01

      XX01

      XX03

       

      My desired result:

      XX02

      XX04

       

      What's the best way to achieve this?  My original thought was via a relationship...? Thanks!

      Edit:  Forgot to mention using FMP11

        • 1. Re: Find records from one table that aren't in another
          mrvodka

          Yes create a relationship between the keys.

           

          Create a script.

          Go to Related Records [ Show Only Related Records; Match Found Set; Import Layout ]

          Show Omitted Only.

           

           

          You could also use a not equal operator in your join and just use the Go to Related Records.

          • 2. Re: Find records from one table that aren't in another
            Wimmachine

            Thanks mr_vodka, worked perfectly! :smileyhappy:

            • 3. Re: Find records from one table that aren't in another
              Wimmachine

              While I am able to find the records I want, when I try to export them FileMaker crashes.

               

              I added on to the script to suit my needs, ending with a Sort Records step.  This appears to be causing the crash.  When I remove the Sort Records step from my script, I am amble to export, but FileMaker gives me an error stating that the sort order has changed since the last export, and any summary data based on previous sorts will not be exported.

               

              I understand what this error message means, but I'm not really sure why I'm getting it as my script no longer sorts the records at any point.

               

              It's not really a huge issue because I am exporting to Excel and I can just sort in Excel.  However, FileMaker crashing on me was quite unexpected -- the first time it happened when I re-opened my file, the script I was working on was lost.

               

              I am running FMPro11 Advanced on Windows XP and this is the first time (any version) of FileMaker has crashed on me. I can reproduce it 100% :smileysad:

               

              Edit:  Upon further testing it looks like the layout I was using in the Go to Related Record step is what was causing the crash and/or error message.  I created a new layout in table view to facilitate the export and everything, including the Sort Records step works fine now.  Weird.

              • 4. Re: Find records from one table that aren't in another
                mrvodka

                If you are fairly new to the project, I may suggest creating a new file and importing the data into that file just ot be safe that you do not have corruption in your file.