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.
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.
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...
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?
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?"
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.
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?
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.
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?
I'm referring to the field that uses the sum function.
The sum TotalDifference field is a unstored, numeric, calculated field.
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.