Find by matching a List field to a List field.

[Brad Mathews]

I have a field that is a List of values I want to match records to. The field in the target table is also a List values.

So my search parameters might be this list "A|B|F" (pretend | is a new line making a List) and my target search field might be "A|G|N". Since they both share the A, the record should match.

I tried looping through each item in the search parameters list and doing a Perform Find on each, but each find overwrites the last. Apparently multiple search criteria do not add to the result set unless part of the same Perform Find.

I tried some creative Find Requests like OneField = Length( FilterValues($SearchValues, ChildStatuses) ) > 0 - where OneField is the value 1, but that finds all records.

How do I perform this find from a script?

Thanks,

Brad

[comment]

28 minutes ago, Brad Mathews said:

I tried looping through each item in the search parameters list and doing a Perform Find on each, but each find overwrites the last.

You would have to create an individual request for each value in the list, before performing the find. Or perform the first find, then do a series of Extend Found Set[] steps with the remaining values.

Alternatively, you could define a relationship based on matching the two fields and use Go to Related Record[] instead of find.

 

Edited March 27, 2016 by comment

[Brad Mathews]

comment,

Extend Found Set! (forehead slap). I need to go through the script commands and functions again. (and again)

I really did not like the relationship method. I did try something like that first, based on a Chris Ippolite tutorial, but it was non-intuitive and had side effects that I had not solved. The script route is more straightforward to my mind.

I tried both of your other suggestions and I think I like your first idea better. The search criteria aren't hiding behind some dialog so the script is more understandable. And now I know what the Request part means in the New Record/Request command.

I have attached screenshots of both solutions showing all of the moving parts for others to follow.

Notes:

1. Both the Perform Find and Extend Found Set commands use the same Find request.

2. You can simplify this script if you aren't using lists to match lists and you have a much more direct way to filter your layouts than the relationship way which the FileMaker tutorials seems to prefer.