Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

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)
0
mrsam3
Asked:
mrsam3
  • 3
  • 2
1 Solution
 
lesouefCommented:
the only way is to change calculations for number field which will store the results.
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?
0
 
mrsam3Author Commented:
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....
0
 
jvaldesCommented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
lesouefCommented:
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.
0
 
mrsam3Author Commented:
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....
0
 
lesouefCommented:
First the 3 colours should be a single field.
Then payments could be a number as well. When you enter a new payment, you re-calc the sum with a script.
A good delay is the one the user does not feel! 10s is not really acceptable, except for something you do once a week.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now