I have two tables: people and schedule. Through a relationship, I flag a calc field in people indicating whether the person has a valid record for next week's date range in schedule--Case(IsValid(people_schedule::date);1;0). I would like to perform searches, etc., on this value, but with tens of thousands of people records, searching on an unstored value is prohibitive. Any ideas how to get a searchable field in people that will update with each passing week?

searching on an unstored value is prohibitive

No it isn't, but is just not as fast as the indexed.

But you need to investigate this instead:

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

--sd

You could do the find in the schedule table, then go to related record (match found set).

Thanks, Søren. Were you directing me to the Booking System Demo? I'm no sure what I can take away from it. I actually do need to perform a find of a set of records. The results is to be a printed report with 3 subsummaries and an indefinite number of records.

Thanks 'comment'--this may be my best option, but with nearly a million schedule records, and multiple find criteria, this, too, could get kind slow. No one knows how to capture the boolean in a storable field, yet update it as the original boolean changes?

No one knows how to capture the boolean in a storable field, yet update it as the original boolean changes?

This cannot happen automatically - that is the entire point of unstored. You could have a script do this for you, but you would need to make sure the script runs every time a record in schedule is modified/added/deleted or periodically e.g. at opening/closing the file.

My point was that this entire flagging business should be solved a tad more relational, if they're occupied won't they show up in the portal of availiable. You're a bit thrifty with the tables you like to get involved in the solution.

But if you insist on having a many2many relation with just two tables, must you at least have a few extra TO's for the selfjoin from the upper and lower range global fields to ...well take a look at the image above, but in the end is the found set established by a GTRR(SO) to the found set People 2 points at, no searching actually!

GetTheOccupied is a calc'field where you make a List( of the ID's seen in People TO over the two hops of relations!

--sd