5 Replies Latest reply on Jan 31, 2011 12:27 AM by Sorbsbuster

    How to display a date calculation globally on hosted database?

    DanielPinder

      Title

      How to display a date calculation globally on hosted database?

      Post

      Many thanks in advance.

      I am trying to make a system to calculate how many minutes per day need to be written (by a composer) based on a date of completion and today's date. In other words, say the date the project needs to be done by is May 1st and there are 90 minutes left to write. I already have a field that calculates minutes yet to be written ("Total_unwritten") but I need the new component to calculate the current value of Total_unwritten divided by the amount of days existing between today and the completion date and express it in minutes.

      I think I managed to do the calculation correctly:

      days_left = (date_of_completion - Get(CurrentDate))

      minutes_per_day = (Total_Unwritten  /  days_left)

      So my question is, how do I make this a global value in a hosted database? I want to display it in the header and have it not tied to only one record.

      THANKS

        • 1. Re: How to display a date calculation globally on hosted database?
          Sorbsbuster

          For the whole of the file, is there only one concept of the value 'minutes_per_day'?  That calculation will evaluate for every record in the file, so are you saying you want that to be the case?  If it is, I'm a bit curious where the components of the calculation are getting their data from. (Is one record going to keep track of how many minutes are unwritten for it, and then you will have a concept of adding together all the unwritten minutes over all the tracks (records) and this is the value you will display globally?)

          Should you not be adding the number of unwritten minutes over each track (record) and dividing that by the number of days, then?

          I know that is not answering your question, but if it is the case then you may want to do other things.  One way to achieve what you want is, for example, to have a cartesian relationship from the table to itself, and then no matter what record you are on that relationship would always pick up that value - IF - the value is the same on every record.

          • 2. Re: How to display a date calculation globally on hosted database?
            DanielPinder

            Thanks for the thoughtful reply.

            There is only one concept of minutes_per_day because it looks at all records, takes a tally of which have an X in the box "written" and the cumulative time is calculated by a summary total and the field is displayed in the footer as "mmss".

            "Should you not be adding the number of unwritten minutes over each track (record) and dividing that by the number of days, then?"

            You are right about this.

            Many thanks

            • 3. Re: How to display a date calculation globally on hosted database?
              DanielPinder

              Okay, I did it successfully using the above fields and calculations. All fields mentioned are Global, except that I set the date of completion in a related table with one record in it and use "set field" to transfer the data to my main table via a script. This is, of course, to overcome the weird FM behavior of Globals on a server. Jeez, talking it out here over several posts looks like the ramblings of a tired man.

              • 4. Re: How to display a date calculation globally on hosted database?
                DanielPinder

                I do have one question: The days_left calculation is being stored globally, but, because it needs to update itself when the days change (from Sunday to Monday, for example) will I have a problem with it doing so? I want it to be a global value, but to dynamically recalculate, based on the current date.

                • 5. Re: How to display a date calculation globally on hosted database?
                  Sorbsbuster

                  If you have a self-relation to the table itself, using a cartesian [X] relationship, you could pick up that total of minutes outstanding directly.  If you set the calculation involving days left to be unstored then you wouldn't need globals, other tables, or any scripts.  The days-to-go is the same calculation (assuming you have the target date universally available?) and the minutes written (or vice versa, whichever way you think of it) is

                  Sum ( TableBySelfRelationship::MinutesToWrite)

                  The best way I've found to handle the globally-available-value characteristic that you want is to do what you done (or maybe nearly done): create a Preferences Table with one record (lock it down so no-one - not even you - can accidentally delete that record or add another one).  Again, just put the Target Date in that table, once.