Solved

Slow Calculations?

Posted on 2008-06-09
6
338 Views
Last Modified: 2012-06-22
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
Comment
Question by:mrsam3
  • 3
  • 2
6 Comments
 
LVL 28

Expert Comment

by:lesouef
ID: 21747632
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
 
LVL 2

Author Comment

by:mrsam3
ID: 21747651
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
 
LVL 9

Expert Comment

by:jvaldes
ID: 21748986
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 28

Expert Comment

by:lesouef
ID: 21749059
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
 
LVL 2

Author Comment

by:mrsam3
ID: 21751766
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
 
LVL 28

Accepted Solution

by:
lesouef earned 500 total points
ID: 21752068
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now