indexing a boolean based on unstored calc

[cdyer]

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?

[Søren Dyhr]

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

[comment]

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

[cdyer]

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.

[cdyer]

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?

[comment]

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.

[Søren Dyhr]

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