7 Replies Latest reply on Mar 25, 2013 12:29 PM by philmodjunk

    Help with calculation

    DavidGibson_2

      Title

      Help with calculation

      Post

           I've got a table relationship as shown. I've got three fields that I'm trying to add together.  There's a summary field in DRF called DRF::CAPTotal.  There's a summary field in WorkOrders called WorkOrders::WOTotal.  There's a summary field in Utilities called Utilities::UtilityTotal.  To simplify calculating the balance I created a field called WorkOrders::ProjectTotal that adds WOTotal + UtilityTotal.  The calculation that subtracts the WOTotal field and the UtilityTotal field from the CAPTotal field in a field called CAPBalance.  The problem is, if there are no records in the WorkOrders::WO field, there is no WorkOrders::WOTotal and the calculation fails.  As soon as I put data in a WO field, it works great.  Let me know what I'm doing wrong.

           Dave Gibson

      Relationship.png

        • 1. Re: Help with calculation
          philmodjunk

               In the Specify calculation dialog, did you clear the check box named "do not evaluate if all referenced fields are empty"?

          • 2. Re: Help with calculation
            DavidGibson_2

                 Yes I did.

            • 3. Re: Help with calculation
              philmodjunk

                   In what table is CAPBalance defined?

                   Can you copy and paste the exact expression for that field here?

              • 4. Re: Help with calculation
                DavidGibson_1

                     Okay, here's the related information. CapBalance is in the WorkOrders table.

                • 5. Re: Help with calculation
                  philmodjunk

                       Simply copy and pasting the text from these dialogs would have been faster and easier to read... wink

                       I'm getting these expressions from what you have posted:

                       CapBalance:

                       If ( DRF::CapTotal2 = 0 ; 0 ; DRF::SRCO_Captotal - ProjectTotal )

                       CapTotal2: Total of Cap via DRF table occurrence

                       SRCO_Captotal: Total of SRCO_Cap via DRF table occurrence

                       SRCO_Cap:

                       If ( IsEmpty ( SRCO ) ; Cap ; 0 )

                       and the "do not evaluate...." check box is still selected on this field. Thus, if SRCO and Cap are empty, this field will be empty.

                       Going bac to your first post:

                       

                            if there are no records in the WorkOrders::WO field, there is no WorkOrders::WOTotal and the calculation fails

                       I'm afraid that statement has me confused. If there is no record in WorkOrders, CapBalance does not exist as it is defined in WorkOrders. So I'm not sure what you mean here.

                  • 6. Re: Help with calculation
                    DavidGibson_2

                         Sorry it took so long to respond.  I figured a simpler way to deal with this problem.  It's not possible, or it's difficult to get the application to figure out what the correct Cap amount should be so I created a field and manually entered the correct Cap.  I used the new field in my calculation and it works fine.  Thanks for your help.

                         Dave Gibson

                    • 7. Re: Help with calculation
                      philmodjunk

                           Yet what you wanted to do should work and quite smoothly once the needed work is done via relationships and calcualtions.

                           Manually entering the value raises the possibility of data entry errors that are fully avoidable.