6 Replies Latest reply on Jun 25, 2013 8:35 AM by philmodjunk

    Calculate Next Upcoming Date based on related records



      Calculate Next Upcoming Date based on related records


           I have two tables: one with a list of Class types and another with a list of Class instances that are related to a specific class type. For example, "Adult Pottery" might be a class type with class instances on 6/22, 6/29, 7/6, etc.

           I would like the Class Type record (in this case Adult Pottery) to have a field called Next Class that would look at the related Class Instances and calculate the date of the next class (i.e. 6/29, if i were looking at it today).

           I'm not quite sure how to do this (non of my attempts thus far have worked), or if it's even possible, so any feedback/suggestions would be greatly appreciated.

        • 1. Re: Calculate Next Upcoming Date based on related records

               There are several ways,  I like the "Automatic" way.  I would use a new relationship on the two tables to find the "closest future date" class, if any.

               1) Create a new global date field in the Class Type Table.  lets call it  ... gToday

               2) Using a startup script or other reliable method to assure there is always a value  Set Field  gToday to Get (Current Date)

               3) Create a New Table of Occurance for Table Class Instance on your Relationship Tab in Manage Database window, With a new TO name.

               4) Connect the same relationship as the Original number  pkClassType>-----<fkClassInstance

               Here is the "Automatic Step"  next....

               5) Double click on the "=" box and get the "Edit Relationship" window.  Now we are going add a New relationship logic.

               6) Select   gToday  on the Class type and the Date field of Class Instance side and Hit the ADD button. you will see a Second Criteria appear in list as logical AND...

               7) Between the two Table list is the Logicial operator Drop Down List... click and select "≥"  and click CHANGE button.wink Click "allow creation of Instance records".

               This just made a pre done Find using a Relationship .  This made the New Relationship match Class Type AND Instances the Current date.yes

               8) Now go to the Layout you want to Display "Next available Instance", create a new Portal selecting the new Relationship name. Sort Date field chronologically and make it a one line and one field portal,  being the Date Field.surprise

               9) Lable the Portal "Next Date", if any.

               10) Wahhhh Lahhhh the Next Class Instance will appear when entering that layout when you select a Class Type.  It wil be empty if there are none available.


          • 2. Re: Calculate Next Upcoming Date based on related records

                 Another option that you can use in place of gToday is an unstored calculation field with get ( CurrentDate ) as its calculation and "Date" as it's result type. It will never need to be loaded with today's date, but you also cannot load it with different dates than today's--which may have uses for you. Thus, it's very much a "developer preference" as to which option to use.

            • 3. Re: Calculate Next Upcoming Date based on related records

                   @Phil i was thinking about that gToday global on second cup of coffee this morning.  I was wondering if your idea would work on a Server with Multi-Clients?  I hadn't tested  your idea and kinda dodged the solutions. I would have probably set gToday  in a script to navigate to that Layout or a Script trigger to run OnLayoutEnter.

                   The reason was... What if a Server changed days while still running?  What would "recalculate" the gToday ?

                   I like to think of gToday type globals as really "locals" on a Server environment, thus recalculate for each client is needed.indecision


              • 4. Re: Calculate Next Upcoming Date based on related records

                     Don't see why there'd be any problems using a calculation field so long as it is unstored. Both gToday and cToday would need something to make them update if the user is accessing the file near midnight and you wanted the value to change automatically without closing/reopeneing the file when the system clock's date changes.

                     Both evaluate from the client machine's perspective so hosting a file from a server doesn't change how things work.

                • 5. Re: Calculate Next Upcoming Date based on related records

                       @Phil  Thanks!  If you think about this post question, probably after 9:00pm it doesn't matter about the Current Day's instancewink.  I tend to be overly careful when dealing with Server "globals" since i was 'burned big time" on my first FMP conversion to Server environment.


                       Your thought of cToday should "recalculate" for a client anytime they change Class Types and you could add to that calculation a "near midnite" check  so IF Time > 20:00 then cToday = Current Day + 1.yes


                  • 6. Re: Calculate Next Upcoming Date based on related records

                         Or maybe just a reminder should pop up:

                         "Hey! it's nearly Midnight! Stop working so late and go to bed!" wink

                         Hmmm, wonder if I can gin something like this up for a certain teenaged son of mind who gets way to wrapped up in his electronics...

                         Seriously, I don't think automatically changing the value early is a good idea, it might confuse rather than help the user. But if it was really necessary (and I doubt that it is in most circumstancs), a script trigger could perform a time check and if it's close to midnight, it could use Install OnTimer Script with a calculated interval to force an update of the value exactly when midnight is reached and the script can warn the user of the value change.