Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Slow Calculations?

Posted on 2008-06-09
Medium Priority
349 Views
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
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
• 3
• 2

LVL 28

Expert Comment

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

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

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

LVL 28

Expert Comment

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

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

lesouef earned 2000 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

Question has a verified solution.

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

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…
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month8 days, 22 hours left to enroll