Filtering Values for a list by date range
I have a series of values with a start date and end date. I want to make a value list of these for a drop down box. So like this:
Table 1 Fields : Start Date, End Date, TargetValue
I want to populate a field in Table 2 with the targetvalue list where Start Date <= Get(CurrentDate) <=End Date
That way I get all of the values that are current. I think a self join figures in somewhere but I'm new to FM (working in a demo version for now) and can't get it to work.
Thanks in advance.
(I tried a calc field where the filter was :
FilterValues ( tAdSchedule::Source Code; tAdSchedule::Run Date ≤ Get(CurrentDate) ≤ tAdSchedule::End Date)
to no avail)