To Calculate or Not to Calculate

[geod]

I know from experience not to put calculated fields on a list layout. Especially ones that calculate sums over a relationship. I do not use them on lists. Now in a products DB, on the Inventory layout I have 4 fields to hold 'Items Sold', 'Items Rcvd', 'Backorders' & 'Inventory'. I have a button for the user to click on to run a group of Set Field Commands and recalculate those fields. For reports I use a script to Replace those fields with calculations before printing. I have never liked that arrangement. Now today I am questioning whether my logic is correct in this case. Could users please share their wisdom concerning the use and placement of calcs (especially ones using relationships) on forms, etc. The environment is FM5 with FMServer 5.5 over a 10BaseT network. The computers are Macs. Thanks.

[Kurt Knippel]

It really depends upon the scope of the calculation. Lets say that you have an Inventory file in which all of you inventory transactions are stored; then you have a list view in the Invoice_LineItem file which displays the current status of the Sold, Recieved, In Stock, Back Ordered status of each line item. Now this may or may not be quick. It will depend upon how many records are in your line item file, how many in the Inventory file. There are just so many variables that it is difficult to give you exact advise.

I have had situation where I simply displayed calculated sums, and situation where a special screen was used to show them, and situations where procedures were used to store the information. In the particular situation each of the above were the best choice.

[Anatoli]

I am against all calculations. It is slowing down with more records.

The only calculations I am using are the Status and within the same record.

Ask yourself how the system will perform with 10,000, 100,000 and 1,000,000 records.

[trevorg]

If one does not use calculations, how do you calculate sub-totals, etc... for line-item calcs?

Then, if one wants to list the "sales per day" or something similar, how would you list the total if it needs to pull data through a relationship?

Am I missing something huge? Calculations have always seemed to be more graceful than triggering scripts to store calculated results.

[Anatoli]

I have no problems with calculations.

In reports one expects it will be slower.

But when calculation is running across many records, then the system is painfully slow. In that case I am against calculations and much faster is to work only with stored values. If you store the result of transaction it will be done in split second.

And all users will get the stored values immediately.

If you calculate the transaction the system is very slow for all users. And if you resize the window, it gets recalculated again, it is real pain.