Hi there... like the title says, this must be a common problem I just wondered how people go about overcoming it..

We have a customer order database.... obviously there is a balance outstanding for each order and obviously we need to search on it sometimes.... With 100,000+ orders this search is taking 1/2 hour each time!

How do people cope with this..? I need the search to complete in 30 seconds not 30 minutes!

What do you mean by "balance outstanding?" Do you mean payment or backorders?

Either way, searching an unstored calc is slow. The method used is to script transactionally, so that you are searching for a stored value. You can also run a nightly FMS script to set a stored value. Then, your report will be "as of 3am" for example.

With 100,000+ orders this search is taking 1/2 hour each time

But surely not 100,000 orders are outstanding so you really don't need to search all 100,000 every time.

With versions prior to 11:

Find all invoices that have been posted and frozen after month-end and set a date field in the Invoice (maybe called DateFulfilled) with the date of the last payment received. This is important information to capture for accounting anyway so use it here as your 'paid' flag. Then to find outstanding invoices, search this date field for = then loop and set any additional invoices that are now complete. When searching then for outstanding (from User perspective), search for '=' in DateFulfilled then constrain for balance > 0. Always set flag fields when Users are not in system to protect from record locking.

With version 11:

Use script trigger when a payment is made to see if it completes an invoice obligation and set that date field then. Even script triggers can fail so I suggest both methods. After month-end (or nightly) find any to set AND use trigger when payments are entered to check the balance. And when searching for outstanding, find on the flag then constrain on the balance.

Changed the wording on blue portion for clarity ... and changed from searching for * to searching for empty date.

Yeah I'm using script triggers to just set a Number field to be whatever the balance is after a change has been made.... also set a server script to run couple of times a day just to go through the whole set of orders just to make sure! Searches now take a second as opposed to 30 minutes!