7 Replies Latest reply on Apr 18, 2015 3:14 PM by macrolide

    Finding if found set of records match existing records in a join table

    macrolide

      relationGraph.png

      In the setup shown above, I am trying to save records from "Data" into "SavedSets" for each User.

      Before adding a UserDefinedRecordSet to the SavedSets table, I wanted to check if the records that are being saved into the SavedSets Table is an exact match for an existing set of Records the user has created.

       

      I tried creating an UnstoredglobalField Data::gRecordID_ofCurrentFoundSet to contain the RecordIDs of the current found set (using a custom function from Briandunning), hoping an EQUAL to Relationship between the globalField and SavedSets::DataID_fk field would work. A calculation checking if Get(FoundCount) IS EQUAL to Count(FoundRecords_Match_DataIDfk::DataID_fk) if returned TRUE meant a match. But it does not work.

       

      Any suggestions how to make this work?

        • 1. Re: Finding if found set of records match existing records in a join table
          erolst

          I'm assuming that SavedSets is a join table (rather than a child table with a field that has a list of IDs), and that you want to save the found set for a given user.

           

          If so, try this:

           

          • add a predicate FoundRecords::gCurrentUserID = FoundRecords_Match_etc::UserID_fk

          • sort the FoundRecords_Match_etc TO by SavedSet_ID

           

          check if

          FoundRecords_Match_etc::SavedSet_ID = Last ( FoundRecords_Match_etc::SavedSet_ID )

          AND

          Count ( FoundRecords_Match_etc::UserID_fk ) = ValueCount ( FoundRecords::gRecordID_ofCurrentFoundSet )


          if True, then all records in the found set have been saved for this user in the same set, i.e. for this user exists a set that is identical to the current found set.


          Your existing check doesn't take into account the user, or the set association – it would only check whether all records have been previously saved (across all users in any set).

          • 2. Re: Finding if found set of records match existing records in a join table
            Mike_Mitchell

            The reason the method you tried likely didn't work is that you likely have more than one record in SavedSets for each foreign key. Your method will still work, but you have to do a little bit more to isolate the duplicates.

             

            First step is to do what you already did: Create the global key on the parent side and join it to the foreign key on the child side. This will give you all the records in SavedSets that have DataID values in the current found set. But then you have to make sure that every value in the found set is represented. You can do this via the FilterValues ( ) function. Assuming your CF eliminates duplicates (and you'll have to verify that it does), you can do something like this:

             

            Let ( [

            childKeys = List ( SavedSets::DataID_fk )

            ] ;

             

            Case (

             

            Data::gRecordID_ofCurrentFoundSet = FilterValues ( childKeys ; Data::gRecordID_ofCurrentFoundSet ) ; 1 ; 0

             

            )

            )

             

            This calculation will be 1 (True) if the global field exactly matches a filter after being passed through the child keys. In other words, when FileMaker checks to see if each value in the global exists in the child keys, it'll pass it on through if it does, and filter it out if it doesn't. (That's what FilterValues does.)

             

            You may have to play around with it a bit, like sorting the lists, but this should do the trick.

             

            HTH

             

            Mike

            • 3. Re: Finding if found set of records match existing records in a join table
              macrolide

              Thank you for the quick replies.

              Will try as suggested.

              • 4. Re: Finding if found set of records match existing records in a join table
                macrolide

                Thank you for the quick reply.

                BRB after trying out the suggestions.

                • 5. Re: Finding if found set of records match existing records in a join table
                  justinc

                  I will suggest another technique:

                       Make a new 'Listof' summary field in Data:   Data::ListOfCurrentFoundRecords_s

                       Make another new text field in SavedSets:   SavedSets::SetRecordIDs

                   

                  Let's assume that this is an empty system (i.e. no 'SavedSets' have been defined yet); as a user, I am on a layout based in Data and I perform a find.  I get, say, 10 records.  I now want to save those records as a FoundSet.  I push the 'Save Current Set' button.  There are no existing 'SavedSets' to compare to, so for now let's just save this set to the 'SavedSets' table:  This script would read from the 'ListOfCurrentFoundRecords_s' field and write it to the 'SavedSets::SetRecordIDs' field.   (This is skipping a step for now, that's why I started with an empty system.)

                   

                  The next day the same user does another found set that they want to save.  They press the same button.  The script would check to see if this set exists.  To achieve this I would suggest adding a global field:  Data::UserID_g.  The script would take the current User's ID and set it into this new field.  Then you have a new TO of 'SavedSets':  SAVEDSETS_compare.  You relate that to 'Data' by two fields: 

                       Data::ListOfCurrentFoundRecords_s = SavedSets::SetRecordIDs

                       Data::UserID_g = SavedSets::UserID_fk

                   

                  Now all your script has to do to see if this set already exists for this user is to do:  isEmpty ( SAVEDSETS_compare::SavedSetID).  If it IS empty, then it doesn't exist, other wise it exists.

                   

                  Essentially, what you are doing is using the list of RecordIDs in the saved set itself to become the predicate in the relationship.

                   

                  --  Justin

                  • 6. Re: Finding if found set of records match existing records in a join table
                    macrolide

                    Thanks Justin.

                    Might take a while for me to study through your technique.

                    • 7. Re: Finding if found set of records match existing records in a join table
                      macrolide

                      Like erolst and Mike suggested I forgot to include userID and setID in my match between the two tables. It works now!

                       

                      But its amazing to see the different techniques suggested for the same problem.

                      Thanks Everyone for all the suggestions.