1 Reply Latest reply on Jan 16, 2017 2:18 PM by philmodjunk

    Unique records


      I have two tables with the same type of information. In this database, I am using FileMaker to convert data to be imported into an online database. I export the online database into a CSV file which I then import into FileMaker (TableA). I then obtain a data-feed from a manufacturer as a CSV file and import that into FileMaker into a separate table (TableB). I then import the converted FileMaker data back into the online database thereby updating the data.


      What I want to be able to do is find the records in the online database that are not found in the manufacturer data-feed (usually items they no longer carry). This is the method I am trying to use:  Relate TableA to TableA_2 (self-join relationship) matching tableaID_pk to tableaID_pk AND TableA::CalculationField=1 to TableA::FieldWhichIndicatesASubsetOfRecordsToMatchAgainst (This is a field which calculates to 1 if the manufacturer is the same as the manufacturer used in TableB). Then I relate TableA_2 to TableB with relationship TableB::productsku not equal to TableA_2::productsku. For everyone except me, you know why this doesn't work, but in my inexperienced, mind, this should work.


      So, I have two questions in order of importance. What mechanism WILL work? And 2, if you are willing to explain, why doesn't what I am doing work?

        • 1. Re: Unique records

          Are you trying to find the records in A that do not have any matching records in A2 when you compare product ID and Manufacturer?


          If so, this relationship:


          A::__pkProductID = A2::__pkProductID AND

          A::_fkMfgID = A2::_fkMfgID


          Then on a layout based on A, Perform a find specifying an asterisk in A2::__pkProductID, but with the omit options specified.


          The found set will be all records in A that do not have a matching record in A2 as specified by this relationship.