3 Replies Latest reply on Sep 4, 2015 10:05 AM by fentonjones

    Calculation from a Relationship



      I am working on a database that is calculation heavy. I am using relationships to define the calculations and have run into a stump. I think it is because I am doing calculations on calculations and am beginning to think I need to do the calculations differently.  I have a couple of screenshots here.



      Here is the table I am working with. The last four calculations are what I use to total the daily and monthly numbers.

      Here is the result of the table.


      Notice that the last two columns do not calculate. The last one would not have any numbers in it anyway because we have zero's in the third to last column.


      Here is what the relationship looks like.


      My questions is, am I doing too many calculations on calculations?


      Any help would be appreciated.

        • 1. Re: Calculation from a Relationship

          Doing calculations on calculations, or being "calculation heavy" is not inherently a bad thing. It's possible to set up calculations that take a long time to perform, cause undue dependencies that affect performance, or just plain don't work because they are circular or your calcs fire in the wrong order, but mistakes can be made with all the other tools, too.


          I think your issue here may be that LOG_MONTH and LOG_YEAR are unstored, and unstored fields can't be used on the "right" side of the relationship?


          Chris Cain


          • 2. Re: Calculation from a Relationship

            Adding to what Chris said, I like to script things instead of calculating them. It takes longer and it's more work but it's faster for the DB.


            If you do not have these calculations on the layout, it's not that bad, though. And yes, at least try to store your calculations if you can.

            • 3. Re: Calculation from a Relationship

              Basically the same answer as the others, with some other info:

              I see no reason why you could not have Indexed calculations, for the those fields, based on the Log_Date. Then, if you want to see ones of the current Log_Month_current, you'd have another calculation, Unstored, using Get ( CurrentDate ) as its base.

              You would then use the Unstored Log_Month_current field on he left to the Indexed Log_Month field, in the relationship. In other words, you need 2 "month" calculation fields to produce a relationship for the current Month.


              I prefer to add a "0" to months before 10 (example, currently: 201509), as it makes a better number result.

              Year ( Get (CurrentDate) ) & Right ( "0" & Month ( Get (CurrentDate) ); 2 )

              [Year is similar by simpler.]


              P.S. If you want to see other than the current month, you could add a global field to hold a specified month (or date). You could either use that in the unstored calculation; or you could add it to the calculation, including the "current", i.e., use the global, if it has a value, and the current if the global is empty. Exactly how you show the choose is kind of another problem, in this case :-|