Calculated Fields order 1

[MarkB2]

I inherited an application which is failing to calculate correctly. I have a table defined as follows:

o_labor number (manually entered at data entry)
e_labor calculation (number) =sum(extra_labor)
d_labor calculation (number) =sum(deduct_labor)
r_labor calculation (number) =o_labor + e_labor - d_labor
a_labor number (manually posted via a script in another table)
b_labor calculation (number) =r_labor - a_labor

extra_labor number repeating field with 25 repetitions
deduct_labor number repeating field with 25 repetitions

Just to understand the usage a bit, the first field prefixed with "o_' is original followed by "e_"xtras, "d_"eductions, "r_"evised, "a_"ctual and finally "b_"alance. Also all the calculated fields are stored in the database.

The following is what is seen on the screen. Note that the e_labor, d_labor and a_labor figures are correct.

o_labor $88,500.00
e_labor  $9,766.50
d_labor  $2,000.00
r_labor $96,266.50
a_labor $64,806.80
b_labor $33,459.70

In the above, the last line should be $31,459.70 which is off by $2000. Note that if I change the calculation of the balance figure as follows the figures calculate corectly:

b_labor calculation (number) =o_labor + e_labor - d_labor - a_labor

[red]The question is why was it failing? What is the order in which calculated fields are resolved? Was the b_labor being calculated prior to the r_labor used in the original calculation?[/red]

TIA
Mark

 

[JeanW]

Is this a FM 4 application ?

 

[MarkB2]

This is FM 3 actually

 

[JeanW]

The reason I asked for the version is that once I had a similar problem in FM 4/3.

After days (weeks) I figured it out...(well.. for my problem) it was the sequence of creating the fields.
I deleted all the fields and recreated them in an different order and no problem anymore.

Say f.i. that in the sequence of creating your fields you need an other field (not existing yet) to make the calculation, you go out of the calculation screen in whatever way, create the missing field, go back to the previous field and finish the calculation and it is bingo.
The calc will calculate the first field first, and then take the 'new' field into account or not.

Strange, and I never found the reason. Neather did FM in that time. Don't know if newer versions suffer from that now. But since, I'm always very carefull with the creation sequence/order of my fields.

 

[MarkB2]

As you can see I solved my problem by not using an intermediate calculated field which somewhat supports your reasoning.

The hitch is that if I go to the define screen sorted in define sequence, the field calculation should have worked, if done in a top down sequence.

I can't seem to see the calculation sequence in print anywhere and was hoping for a definitive answer so I can proactively look for calculation problems.

Thanx JeanW

 

[JeanW]

Unlike validations for f.i. numeric only, in FileMaker 7, validations by calculation are not evaluated until you exit the record (this is a change from previous versions of FileMaker).


As to the rationale, I can't (of course) speak for the FMI Engineers on this, but I note that f.i. as an artifact of the validation by calculation option, previous versions of fileMaker evaluated any dependent calculations twice, to first establish (in cache) the new state of the record prior to evaluating the validation calc, so as to then use those values (rather than the prior-to-update values) as the context within which to determine if the validation is passed.

If you keep that in mind, it can prevent some 'strange' results....