2 of 2 people found this helpful
Add a stored (indexed) calculation field, cDateKey with a date result type written thusly:
If ( IsEmpty (end_Date ) ; Date ( 1 ; 1 ; 3000 ) ; end_date ) )
In the table that's to be the layout context for your value list, add an unstored calculation field, cToday, with result type:
Get ( CurrentDate )
Set up a relationship:
LayoutTableOccurrence::cToday < Employees::cDateKey
Base your conditional value list on this relationship and you should get the results that you've requested.
If you need more detail on how to set up the value list, please reply back and ask for that information.
Fantastic, this worked perfectly. I ended up putting cToday into a separate table. As a software dev those globals everywhere make me uneasy .
Thanks a lot.
Except that cToday was not described as "global" but rather "unstored". These are not the same thing. This field needs to be unstored it does not need to be global. It does matter in this case where you define the field. It has to be located in a table that works in terms of the relationships because it's a match field in the conditional value list's relationship. Put it in the wrong table and the CVL doesn't work as expected.
That said, I agree that putting global fields in a single dedicated table is a good idea--except for when you are also using that global as a match field in a relationship.