Unstored calc for portal filtering problem

[bobearl]

I'm having difficulty with creating a filtered portal on the criterion of age.

I am trying to make a portal provide choices filtered by input from the user in fields of LastName, FirstName, Gender, and (DOB or estimated_age). It is actually working to filter by user input, but the age calculation is incorrect because it currently is stored. This is not a big deal for a 50 year old (the estimated age looks up +/- 20%), but is a substantial issue for a 14d old if the calculation is not refreshed for a few weeks.

The example file is attached. The user can put in the LastName, FirstName, Gender, and estimated age. The user input is placed into a global field and a calculation creates the multikey for the relationship (for text, "text" & ⁋ & "textzzz"; for estimated_age, estimated_age * 0.8 & ⁋ & estimated_age*1.2). However, the multikey looks at the child table for the exact age in days, a stored calculation using Get (CurrentDate) - DOB. As a stored calculation, the date gets stale unless the record is refreshed; as an unstored calculation, the relationship breaks.

Looking on other posts, I see that the child side of this relationship cannot be unstored, and since the parent side of the relationship uses a calculation from a global, I don't think I can reverse the relationship. Can anyone suggest a workaround? I have considered refreshing all the records on opening the file, but it may be many thousands of records, and I worry that this will be a significant performance hit. Many thanks in advance for your wisdom.

UnstoredFilterPortal.fp7.zip

[bcooney]

Yes, the child side of a relationship cannot have unstored calcs.

Are you serving on FMS? You set the age using a server script that runs nightly.

[bobearl]

I think that yours may be the best strategy; I'm not using Server yet, but hope to be soon.

Do you have advice for the most efficient way to refresh? I wasn't aware of a built-in way to run a script at a given time each day, which is why I was thinking of a script on opening the file, but a daily script would probably have less adverse effect on performance. Should I just make the script go to the layout with the calc on it and view it in List View, and do I need to do it with the screen unfrozen? Pardon the novice questions, it's a new area for me. Many thanks for your help.