5 Replies Latest reply on Jan 6, 2015 5:35 AM by simonkramer

    Finding Non Matching Records

    simonkramer

      Hi,

       

      I'm importing a worked hours dataset from Excel every 30 minutes and need to find new Employee and Job records within the dataset.  The Employees and Jobs also have unique ID's which I suppose should make it easier.

       

      I thought I could somehow compare the newly uploaded data to the Employee table using the unique ID and find those records in the new data that don't have a corresponding record in the Employee table?  I also thought this would be easy ... I tried a 'not equal' join between the ID field in the Employee table and new data Employee ID field and expected a list of data for Employees not in Employee table?

       

      Any thoughts?  I'd be very grateful?

       

      Thanks in advance,

       

      Simon

        • 1. Re: Finding Non Matching Records
          Mike_Mitchell

          Hello, Simon.

           

          Set up your join as an equijoin (equals) between the imported data and the existing data based on the unique ID. Place the unique ID field from the existing data's table on the layout for the imported data. Do a Find for the unique ID using a "*" (asterisk) character on the unique ID in the existing data field on the imported data's layout, but use the Omit switch (instead of Include).

           

          This will cause FileMaker to locate all records in the imported dataset that do not have a matching record in the existing data.

           

          Let me know if this doesn't make sense.

           

          Mike

          • 2. Re: Finding Non Matching Records
            simonkramer

            Hi Mike,

             

            I think they say 'easy when you know how'?  Thank you so much I'd have probably spent days thinking about that!

             

            Can I be a real pain and ask a further question?

             

            Now I have my list of new data not in the existing set is there any way of de-duping this so I have a single row for each employee that I can loop through and add to my employee table via script?

             

            I've created a layout using a sub summary that sorts on the Employee ID and shows the new ones but even though I've deleted the 'body' part I still have hundreds of records for each new Employee ID, so what I'm wanting to do is cycle through summary lines not the body records lines?  Does this make sense?

             

            Thanks again, very helpful!

             

            Simon

            • 3. Re: Finding Non Matching Records
              erolst

              Hi Simon,

               

              you can use the Fast Summary technique to loop group-wise. In a found set sorted by yourSubSummarySortField,

               

              Set Variable [ $sizeOfGroup ; GetSummary ( sCountOfANonEmptyField ; yourSubSummarySortField ) ]

               

              returns the record count of the current group, so that

               

              Go to Record [ Get ( RecordNumber ) + $sizeOfGroup ]

               

              would bring you to the first record of the next group. Effectively, you're jumping from one record that represents a sub-summary line to the next.

               

              Alternatively, if you know that the found set consist of “new” employees only, then this …

               

              Go to Record/Request [ first ]

              Loop

                Set Variable [ $sizeOfGroup ; … ]

                Omit Multiple Records [ theCount - 1 ] # omit all but one of current group

                Exit Loop If [ Get ( RecordNumber ) = Get ( FoundCount ) // last record ]

                Go to Record/Request [ next ] # start of next group

              End Loop

               

              … will leave you with a found set of one record per group (i.e. employee) that you could simply import into the Employees table.

               

              HTH,

              Oliver

              • 4. Re: Finding Non Matching Records
                simonkramer

                Hi Oliver,

                 

                I haven't had chance to implement this yet but I just wanted to say a big thank you.  I'll let you know how I get on with this.

                 

                Best regards,

                 

                Simon

                • 5. Re: Finding Non Matching Records
                  simonkramer

                  Oliver,

                   

                  Excellent thank you works a treat!!

                   

                  Best regards,

                   

                  Simon