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
FoundRecords_Match_etc::SavedSet_ID = Last ( FoundRecords_Match_etc::SavedSet_ID )
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).
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 )
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.
Thank you for the quick replies.
Will try as suggested.
Thank you for the quick reply.
BRB after trying out the suggestions.
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.
Might take a while for me to study through your technique.
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.