12 Replies Latest reply on May 19, 2011 10:21 AM by ig888

    Dynamic value list based on current date range that self-updates???

    ig888

      Title

      Dynamic value list based on current date range that self-updates???

      Post

      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.

        • 1. Re: Dynamic value list based on current date range that self-updates???
          aammondd

          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.

           

          • 2. Re: Dynamic value list based on current date range that self-updates???
            philmodjunk

            First check out this tutorial on Conditional Value Lists. (Ignore Option 1 as it won't work for you.):

            Custom Value List?

            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.

            • 3. Re: Dynamic value list based on current date range that self-updates???
              ig888

              PMJ,

              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!!!

              • 4. Re: Dynamic value list based on current date range that self-updates???
                philmodjunk

                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.

                • 5. Re: Dynamic value list based on current date range that self-updates???
                  aammondd

                  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.

                  • 6. Re: Dynamic value list based on current date range that self-updates???
                    ig888

                    PMJ,

                    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.

                    thanks!

                    • 7. Re: Dynamic value list based on current date range that self-updates???
                      philmodjunk

                      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.

                      • 8. Re: Dynamic value list based on current date range that self-updates???
                        aammondd

                        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.

                         

                         

                         

                         

                         

                         

                        • 9. Re: Dynamic value list based on current date range that self-updates???
                          ig888

                          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.

                          THANKS!!!!

                          • 10. Re: Dynamic value list based on current date range that self-updates???
                            aammondd

                            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.

                             

                            • 11. Re: Dynamic value list based on current date range that self-updates???
                              philmodjunk

                              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.

                              http://www.4shared.com/file/_acNgu94/Date2DateRangeDemo.html

                              • 12. Re: Dynamic value list based on current date range that self-updates???
                                ig888

                                Hallelujah!  Houston we have contact!

                                Finally got this thing to work!!

                                Thank you all for such valuable help!!!!!  There may be some other, different issues that come up and will post. 

                                THANKS AGAIN!Smile