I think Ive done something like this using a relationship and then using the value list from the relationship. I tend to do this by populating a table of global fields with useful information. Its been a while since I worked on FMP value lists Im not near my FM platform at the moment. It becomes fairly dynamic by updating the global field.
You could even look ahead or behind by populating the global field.
First check out this tutorial on Conditional Value Lists. (Ignore Option 1 as it won't work for you.):
What you need is a relationship that matches by start and end dates so that the list returns values only from records where that date range encompasses the current date.
This relationship will do the job:
MainTable::cToday > CoursesTable::StartDate AND
MainTable::cToday < CoursesTable::EndDate
cToday is an Unstored calculation field that returns Date. StartDate and EndDate must also be fields of type date.
Your Value list can be set up with a course ID in column 1 and a course description in column 2. That column 2 field can be the calculation field you describe if the start and end dates are needed to distinguish between different offerings of the same course.
thanks for responding...I will check this out. I'm not sure if in the relationship AND will work the same as OR in my scenario.
I've been checking out the tutorial you mentioned & still need to finish going through it. The portal suggested in tutorial didn't seem to do anything for me.
Made sure the relationship you mentioned is set and also the current date is a global field, unstored. I won't know if it worked until the next day to see if it updated. (My original set up worked also, but only when first set up.)
I will also continue with the tutorial you referenced & see if I can understand everything.
For me, an easy solution would be to make sure that a field self-updates or recalculates everyday but there doesn't seem to be an option for that anywhere.
...I will get back with the results and let you know if it's working...thank you!!!
The portal is just a way to check to see if the relationship will work. It's not required to set up the conditional value list. If you can see the values in the portal that you want to see listed in the value list, you've got a relationship that will work for you.
It does not need to be global, but does need to be unstored. When you use Get (currentDate) in a calculation field, it will not automtically update unless you click the storage options and select the "do not store..." option.
By it being in a global field you can change the date in the global field to test. you can set the global field on enter layout by default.
it didn't work. I checked it over the weekend and it did not update. It is only good on the date it was created. I haven't found a way yet to get a self-updating value list based on a date range.
Any other suggestions? I would appreciate it.
The value in an unstored calculation field that uses Get ( CurrentDate ) will update with the current date every day. I use this calculation all the time.
It will not work with a date field that uses an auto-entered calculation to enter the current date.
It will not work with a calculation field, unless you click the storage options button and select the "Do not store calculation results" option.
Did you try my suggestion
Create a table called global value filters (I do this so that I can place all my global fields in a single table)
Create a date field called AsofDate and set it to global storage
Create the relationship between the global table and your classes table like Phil mentioned
Class Start Date >= AsofDate and
Class End Date <= AsofDate
When you set up your value list you can have it show related values by this relationship.
Wherever you want this list to appear you can set the asofdate as part of an enter layout script by Set Field(AsofDate, Get(CurrentDate)) (dont have my platform handy and my syntax may be rusty)
In order to test this you could temporaryly add a button to bring up a dialog box to set the AsofDate field to another date.
Thanks for the suggestions...I will check them out in my database.
I did make sure that I have an unstored global field for today's date. Unfortunately, each time i make changes and check it out, I have to wait a day for the results to see if it refreshed the information properly. Everything always seems to work the day I make the changes. I'll keep posting until problem is resolved.
You shouldnt have to wait a day or anyting if you expose the Global Field and are able to change the date in the field. You should see your value list change. Then its simply a matter of managing the date that appears in the global field.
If the layout is open crossing the midnight threshold you might want to consider a timed script that compares the AsofDate with the current date and updates it you could even set that script to execute on the field entry (wouldnt suggest it in testing but once you know that it works and you want it kept to todays date you could be sure that its always set on field entry) Phil or someone else may be able to suggest a better spot to enforce the value in the global field.
You can also reset your system clock and then re open your file to see the date change in the field.
Here's a demo file of what I've recommended. The cToday field will update when the sytem clock changes.