Best scheme when you need a calculated value to be indexed?
As you may know, if you try to use a calculated field (which references a related field itself) in the right hand side of a relationship, the relationship won't work correctly because the values on the right hand side need to be indexed.
What is the best workaround for when you need to do exactly this?
Here is my particular example. There are three tables: Organization (Org for short), Event, and EventDay
Orgs can have multiple Events. An Event can have multiple EventDays. A date is stored in EventDay.
An Event has a field called "MaxDay" that calculates the latest EventDay for that Event.
I want to have a portal on the Org that shows Events at that Org as of a certain date.
I created a global field in Org that is "AsOfDate" and a relationship "org_EventDayAsOfDate" that matches the Org::AsOfDate to Event::MaxDay (with a greater than or equal to operator).
As you can guess, the portal wasn't showing the events as expected because MaxDay is a calculated field referencing a related field and can't be stored. So as a workaround I created a "MaxDayIndexed" field which is just a straight up Date field, and copied the MaxDay calculation into MaxDayIndexed. That got the portal working.
Now... obviously MaxDayIndexed will get stale and need to be updated regularly. Ug. What's the best way to do this? An auto enter calculation that gets triggered? A script that runs nightly?
Am I missing something? Or am I just destined to some hacky workaround?