Dynamic value list based on current date range that self-updates???
Here's my problem. How to show a list of courses available based on today's date. I created a field that concatenates a class/course name with start and end date fields. I created a value list from that, it is then a drop down list for someone to select a class. When I first set it up, I created a conditional filter field ("ClassOffering") that states: If ( StartDate ≥ (CurrentDate) OR EndDate ≥ (CurrentDate); ClassOffering; "" )
I have two main tables that do the work: CLASSES and ENROLLMENT. If someone wants to enroll, the ENROLLMENT::class field should have a relevant drop down that only shows classes that either have not yet started OR not yet ended (in case some one wants to take a class mid-stream). The value list is created from "CLASSES" table.
I have been unsuccessful in creating a dynamic value list that self-updates based on a "start date" that >/= to today's date OR an end date that is also >/= today's date.
Here's the key problem when I tested it using a one-day class: It worked great the first time, when I first set it up, it showed only the current classes correctly. The next day, it did not update the value list for a new enrollee. The classes that were correct for the previous date but not for today, remained on the list. Ideally, the simplist solution is to force it to update every day when the current date changes, but I see no mechanism for that. I did not see how I could use a Lookup for it (?) and the value list cannot be created if some sort of indexing isn't checked off. I hope there is another approach I didn't think about or something I've missed.
Please help! ...thank you.