Solved

Slow Calculations?

Posted on 2008-06-09
6
345 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

626 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