mrsam3
asked on
Slow Calculations?
I am using Filemaker Pro 6 and have had this problem for a while but was wondering if there was a way to speed this up another type of calculation. I have a simple calculation that creates a sum of invoices and sum of payments and then subtracts the two. We have alot of problems scrolling through customers because this calculation can take a long time (especially if there over 80 invoices for that customer) and if i want to run a report for total dues over $1000 on the total due field it takes almost a hour!
For Example
CUSTOMER DATABASE
Total Invoices Calcuaation = SUM(INVOICES:Total Invoice Amount)
Total Payment Calculation = SUM(Payments:Total Payment Amount)
Total Due Calculation = SUM(INVOICES:Total Invoice Amount) - SUM(Payments:Total Payment Amount)
For Example
CUSTOMER DATABASE
Total Invoices Calcuaation = SUM(INVOICES:Total Invoice Amount)
Total Payment Calculation = SUM(Payments:Total Payment Amount)
Total Due Calculation = SUM(INVOICES:Total Invoice Amount) - SUM(Payments:Total Payment Amount)
ASKER
i might have embellished the time a bit but we have 11000 customers with 168807 invoices..... even if I do a insert calc or set field with the sum calculation it takes about 20 seconds on a customer that has alot of invoices... the problem with storing the results. The total invoice price in the invoice area is a calculation and it will not let me store it....
If this is the current example make sure you minimize the calculations in your example you are totaling 4 summary fields. You may want to change the final calculation to Total Due Calculation = Total Invoices Calculation - Total Payment Calculation.... This will eliminate two calculations. This will yield the result in about 60% of the time.
You may want to remove these summary fields from any layout where you don't need the answer and only pull them up when you need the data. Again if you aren't showing the results filemaker will not summarize them.
The other choice is to find the records of interest and run a script with a loop that computes the total on command rather than have the summary running all the time. Also don't forget to make sure the keys used in finding and relating these records are indexed. I think Filemaker 6 only indexes the first 12 characters. If your records are ambiguous in the first 12 characters for the key fields the index will not work and a bubble sort will be used on those fields which is incredibly slow.
You may want to remove these summary fields from any layout where you don't need the answer and only pull them up when you need the data. Again if you aren't showing the results filemaker will not summarize them.
The other choice is to find the records of interest and run a script with a loop that computes the total on command rather than have the summary running all the time. Also don't forget to make sure the keys used in finding and relating these records are indexed. I think Filemaker 6 only indexes the first 12 characters. If your records are ambiguous in the first 12 characters for the key fields the index will not work and a bubble sort will be used on those fields which is incredibly slow.
and sorting on calc. fields is the worst thing, because values have to be re-calc. then sorted with no index.
so not only change the Due Calculation as jvaldes said, but also change the Total Invoices Calculation for a regular number, and store its value when you close a new invoice.
so not only change the Due Calculation as jvaldes said, but also change the Total Invoices Calculation for a regular number, and store its value when you close a new invoice.
ASKER
Actually when i started removing fields it was not the grand total it was the display fields, i have some fields inside a portal that do calculations.
The invoice total is displayed with 3 different calculations so I can color them red for unpaid, green for paid, and black for overpaid..... They are on top of each other on the layout.
For example this one shows a red colored amount of the invoice if it is not a voided invoice and the payment hasn't been paid in full.
If( Total Payments <> TPRICE and Total Payments <TPRICE and ARSTAT<>"V",TPRICE , If( IsEmpty(Total Payments) and ARSTAT<>"v", TPRICE , "" ))
The paid color looks like this (just checks the payments and that the invoice is not voided)
If( Total Payments=TPRICE and ARSTAT<>"V",TPRICE , "")
Also I have a total payments field that does a calculation....
Sum(AM_PAYMENTS::AMOUNT)
It takes up to 10 seconds for this portal to render on the server. As I remove the calculations it is quicker of course but i really would like to have these on there....
The invoice total is displayed with 3 different calculations so I can color them red for unpaid, green for paid, and black for overpaid..... They are on top of each other on the layout.
For example this one shows a red colored amount of the invoice if it is not a voided invoice and the payment hasn't been paid in full.
If( Total Payments <> TPRICE and Total Payments <TPRICE and ARSTAT<>"V",TPRICE , If( IsEmpty(Total Payments) and ARSTAT<>"v", TPRICE , "" ))
The paid color looks like this (just checks the payments and that the invoice is not voided)
If( Total Payments=TPRICE and ARSTAT<>"V",TPRICE , "")
Also I have a total payments field that does a calculation....
Sum(AM_PAYMENTS::AMOUNT)
It takes up to 10 seconds for this portal to render on the server. As I remove the calculations it is quicker of course but i really would like to have these on there....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the problem is to keep them up to date. so whenever you create a new invoice or register a new payment, you can use a script which updates the required sums for the current customer only.
note that hiding the calc fields from the layout speeds up display. so if you still wanna use calculations, show them only where absolutely necessary.
however your 1 hour process seems suspicious... how many customers and how many invoices?