12 Replies Latest reply on Sep 26, 2012 1:45 AM by synergy46

    Calculated field 'sometimes' not calculating ....?

    synergy46

      Title

      Calculated field 'sometimes' not calculating ....?

      Post


           I am using FM 12 Advanced on OSX

           I have a 1 to many application where Members (the one) is related to Dues (the many).  On a Members layout there is a dues portal.  One field on the dues portal is titled Over/Under (field name Dues::TotalDifference).  TotalDifference is a calculated field and it works. 

           Above Over/Under is a field called MEMBERS::SumTotalDifference which is a calculated field: sum(DUES::TotalDifference) and does NOT store the calculation results.

           The problem:  SumTotalDifference shows the correct summation of TotalDifference EXCEPT on the first record?  Huh?  And this 'weirdness' ONLY occurs on the first record? 

           Hope you can see what I apprently can not....

           Thanks

            

           Ron

      total.jpg

        • 1. Re: Calculated field 'sometimes' not calculating ....?
          RickWhitelaw

               Perhaps I don't understand, but on the first record the "difference" means different compared to what? I think there might be a structure problem here. More details would help.

          • 2. Re: Calculated field 'sometimes' not calculating ....?
            synergy46

                 Sorry.  I thought the 'difference/ would be evident.

            Members::Sum TotalDifference is a calculated field whose formula is:  sum(Dues::TotalDifference) 
                 TotalDIfference is a calculated field under 'Over/Under'

                 As you can see in the bottom part of the .jpg, Dues::TotalDifference (which is $-35) is the difference between Amount Paid ($100) and Amount Due ($135).  $100-135 =-35.  It is a pretty straight forward calculated field. 

                 The problem is the evident iin the top part of the .jpg.  As you can see, the Members::Sum TotalDifference field above "Over/Under" is huge.  On just this single record, no matter what I do (delete the portal row, add new values... the Sum TotalDifference field miscalculates.

                 If I go to another record and make the same additions, deletions etc.. everything works as expected....

                 I hope this clarifies things.

            • 3. Re: Calculated field 'sometimes' not calculating ....?
              synergy46

                   By the way.   It just happened again. 

                   I was on a member record that looked good.  Then, after running and correcting an errant script, I noticed that the record I was on got a screwed up Members::Sum TotalDifference amount.

                   I find this perplexing because:  Sum TotalDifference and it's reference field TotalDifference are both calculated fields and neither should be able to be written to.  Or, do I have that wrong?  If so, how would you write to a calculated field from a script?

                   Yes, I line by line checked my script for any reference to either field and I don't see any set field[] or any other reference to these two fields.... weird...

              • 4. Re: Calculated field 'sometimes' not calculating ....?
                philmodjunk

                     Presumably, your portal is to a table occurrence box named "Dues". Is "Dues" the exact text shown in "Show Related Records From" in portal setup?

                     And what table occurrence is selected in "Show Records From" in layout setup for this layout? Is it Members?

                • 5. Re: Calculated field 'sometimes' not calculating ....?
                  synergy46

                       Here you go....

                       The weird part of this sitution is that only 'one' record experienced the problem.  All other records performed as expected.  So, I deleted the 'malfunctioning' record.  Used the  app for a while.  Ran a script in debug mode and ... ba boom... another record became infected. 

                       Quesiton:" is there a script command that would penetrate the barrier of a calculated fied?"

                        

                       Thanks

                        

                  • 6. Re: Calculated field 'sometimes' not calculating ....?
                    philmodjunk

                         I spotted a possible issue with your records.

                         Your portal set up indicates that you have a portal filter in place.

                         Sum ( Dues::totalDifference)

                         evaluates at the data level, not the presentation level. This means that it will ignore any filtering of the portal and sum the data from all related records.

                    • 7. Re: Calculated field 'sometimes' not calculating ....?
                      synergy46

                           Phil,

                           Good eye.  It surely seems like Filter would be the culprit.  In fact, I can turn it off because I just turned it 'on' to try and understand how I 'could' use it if it was necessary. 

                           But, some questions do arise:

                           1) When I experienced the problem, I clicked 'filter off' without affect.
                           2)  When I went to another record, (after filter was 'off') everything seemed ok.
                           3)  Returning to the 'weird' record, and everything was still messed up.
                           3)  When I deleted all the portal reccords for the problem member, the sum TotalDifference for the member *still* had a value.  (This was again with filter 'off')

                           I don't see anything in my 'filter code' that would cause this anomalie.  Do you?


                            

                      • 8. Re: Calculated field 'sometimes' not calculating ....?
                        philmodjunk

                             There are two ways to set up a calculation for a field. One method is to use a data field such as a number field and give it an auto-entered calculation. The other is to use a field of type calculation. Which did you use? What you describe sounds typical for a number field with an auto-entered calculation.

                        • 9. Re: Calculated field 'sometimes' not calculating ....?
                          synergy46

                               I don't get what you are saying. 

                               There are only 2 fields referenced:  gStartDate and gEndDate.  These are global fields without calculations. 

                               What am I missing?

                                

                                

                          • 10. Re: Calculated field 'sometimes' not calculating ....?
                            philmodjunk

                                 I'm referring to the field that uses the sum function.

                            • 11. Re: Calculated field 'sometimes' not calculating ....?
                              synergy46

                                   The sum TotalDifference field is a unstored, numeric, calculated field.

                              • 12. Re: Calculated field 'sometimes' not calculating ....?
                                synergy46

                                     I got the problem tracked down. 

                                     There are several fields that get inital values after the user types in the Dues::Date.  Then when the user types in the Dues::AmountPaid another script is run. 

                                     The problem shows if I type in a Dues::AmountPaid WITHOUT first providing a Dues::Date.  So, I trapped for the absence of Dues::Date and if .true., the Dues::AmountPaid script Exits.  This works.

                                     What I don't understand is how, if I don't trap,  a calculated field like Sum TotalDifference can continue to hold a value even after I delete the portal row that holds the summarized field.  Question: "when I delete the protal row, shouldn't the underlying table record be deleted?  This works on ALL other records *except* records that have 'gone crazy' by totalling non existent records.

                                     The .jpg below is how it SHOULD work.  The previous Sep 22 .jpg shows how it DID work.

                                     I suspect I will have to live with the fact that my trap keeps the error from happening and that I will probably never understand WHY it happens... sign.