G'day from Down Under! I think I can help here.
Of course it would be great if we had the ability in FileMaker to just automatically make a relationship to all the records in a found set, or in some other way easily grab all the records in a found set. I imagine something like the list function but have it list all the values in a field in the found set instead of through a relationship. Or a GetFoundRecords function that does something similar to the GetNthRecord function, but gets all of them instead of just a given one...
But alas, not yet. At least not with any of the built in functions. However they have given us a couple of ways we can create such a thing of our own.
Now you've proposed the idea that if there was a status function that could somehow pickup whether or not a record is in the current found set then you could create a relationship to it from a field on the left with a 1 in it, to a field on the right with a 1 or a 0 in it depending on whether it's in the found set.
However, you may or may not be aware that you can create a relationship to multiple records a slightly different way by creating what sometimes is called a multikey.
If every record in your database has a unique serial number, you can create a global field with a collection of those serial numbers separated by returns and then a relationship from that global field to the serial number field will match all the records whose serial numbers are contained in that global field, and only those records. This solves your multi user problem as well, because a global field holds data individual to each user.
So... if you can somehow get all the serial numbers in the current found set into a global field, and set the relationship up that I've explained above, then you've solved your problem.
There are any number of ways to do this, although my preferred method is with a recursive custom function. It has a couple of caveats, so if those caveats are issues, I've explained an alternative option. So here goes.
Option 1: Custom Function.
Have a custom function called: GetFoundSetIds with two parameters:
the Custom Function will then be:
IsValid ( GetNthRecord ( serialNumberField ; iterationNumber ) ) ;
GetNthRecord ( serialNumberField ; iterationNumber ) &
¶ & GetFoundSetIds ( serialNumberField ; iterationNumber + 1 ) ;
Then simply run a script that sets your global field to:
GetFoundSetIds ( <your serial number field> ; 1 )
This function starts at record 1 and gets the serial number field for that record, then repeatedly calls itself, getting the id number of each subsequent record until it reaches the last record, and then stops.
a. If you don't have FileMaker Pro Advanced, then you can't create custom functions. See option 2.
b. FileMaker has a recursion limit of 10000, meaning it will only allow a custom function to call itself up to 10000 times before it borks. In other words, since it has to call itself once for each record in your found set, then this will only work for found sets with less than 10000 records. Anything more than that and you have to go with option 2.
c. Even for found sets less than 10000 but still large, depending on your hardware and other issues, you may find the above takes a while to calculate itself, so this may also be a reason to go with option 2. For large found sets, option 2 can still take a while, but I believe it's faster than the custom function in most circumstances.
So... Option 2: Scripted Copy/Paste.
I dislike this method because it futzes with your clipboard. While there are ways around that, that's a topic for another discussion. But if you're ok with that, then in short...
1. Create a layout in your database that has nothing on it other than your serial number field. Ideally it should be set to list view, but that's not really necessary.
2. Create a script that goes to that layout, does the "Copy All Records" script step, then goes to another layout that has your global field on it, pastes what it just copied into that field, and then returns you to your original layout.
3. Actually, there is no 3.. that's it. ;)
Hope that helps. Feel free to ask for any clarification if needed.
You're my hero. Option 1 works like a charm. Some clients do have more than 10,000 records in this field, but I have no problem just throwing up an error saying that they need to further restrict their search to use this page.
Thank you so much for taking the time to spell it out.
No worries. Good luck with it :)