What is faster, better or more correct?

[Enigma20xx]

Given this example...

 

Table1

NumberFieldA

NumberFieldB

 

Table2

cFieldC [ sum ( Table1::NumberFieldA ) ]

cFieldD [ sum ( Table1::NumberFieldB )]

 

 

What is faster, better or more correct?

 

cTotal [ cFieldD - cFieldC ]

or

cTotal [ sum ( Table1::NumberFieldB ) - sum ( Table1::NumberFieldA ) ]

 

Thank you.

[LaRetta]

I believe that the second is more efficient.  The first example is a cascading dependency calculation - notice it is a calculation within a calculation.  This means that the inside calculation/s must be resolved before the outside calculation can even evaluate.  In this case it is only one deep but that is unnecessary.  How many of us have tried to delete a field only to be told it was being used by another calculation?  So we go try to delete THAT field only to be told ... and on it goes.

 

I swear I've walked six levels deep, before I could finally delete a field (in someone else's solution of course, LOL).  These cascading dependencies really slow solutions to dawg speed and is clear sign of troublesome design.   But still ... I think there is third choice here:

 

In Table 1, you can place a calculation of:  cTotalAmount = NumberFieldB - NumberFieldA

 

Then in Table 2, cTable1Total = Sum ( Table1::cTotalAmount )

 

ADDED:  The new calculation inTable1 should NOT be unstored.  This different approach puts the action (initial math) in the child table which is very fast (stored) and removes the burden from the parent table (Table2) of having to aggregate AND add values unnecessarily.

[LaRetta]

Another option I use quite frequently is to create same cTotalAmount in child table as indicated but also create a summary field in Table1 also which is summary = total of cTotalAmount.  Then that summary field can simply be placed on ANY parent layout and will evaluate based upon the relationship between parent and child.

 

This has added benefit that the same single child summary field (for example if it is a LineItems table), can be placed on Customers, Invoices, Products ... so it can decrease the total number of required calculations because you don't need a 'sum() calc in Customers and a sum() calc in Invoices and so forth.

Which would I use?  Eh, depends upon the purpose - both are valuable.  I think I prefer summary fields in the child tables because of their flexibility and, in the example I provided, a child table of LineItems will certainly be displayed in many places so the summary field would get a good workout (not only by replacing multiple Sum() calculations but also in the child table for reporting purposes) and it will be extremely worthwhile.  I've heard of tests comparing the two and it seems to change per FM version.  I have not tested it recently.  It would seem to me that an index is an index in FM's book so it should not matter any more than comparing a stored Find to a GTRR.  But I can not say for absolute.

 

Ah, don't we love all the options with FileMaker?  :-)

[comment]

What is faster, better or more correct?

 

We will know that when you move the example out of the abstract and into something that represents a real-world situation.

 

And I strongly suspect that the "more correct" solution is to have only one field in "Table1", and populate this field with either a positive or a negative value as necessary.

 

If more convenient from user's point of view, this could be done "behind the scenes" by using a calculation field such as =

 

Case ( Debit ; -Amount ; Amount )

 

Or, practically the same thing:

 

I think there is third choice here:

 

In Table 1, you can place a calculation of:  cTotalAmount = NumberFieldB - NumberFieldA

[LaRetta]

 We will know that when you move the example out of the abstract and into something that represents a real-world situation.

Case ( Debit ; -Amount ; Amount )

 

So true.  I did not see the two numbers as representative as debit/credit (same-purpose number) but rather similar to:  Invoice Amount - Tax Amount ... 

 

Still, as you say, without knowing the meaning of the numbers, it is all speculation.  Clarification of purpose is an important step when offering assistance - I appreciate the reminder, Michael. :-)