What is the calculation in the related field?
There are very heavy performance implications of sorting by:
1) Related data (essentially the system needs to load all of the records from the related table to sort by)
2) Unstored calculations (along with #1, it also need to calculate a value for all of those records)
3) Multiple fields.
Give us more information and we may be able to suggest something.
The script is for the balance sheet run and the sort order has always had 4 sort fields from a related table and one sort field from the layout the script is on. The table GL for Balance Sheet is a table that holds all the GL codes and what the wording should be depending on whether or not the balance sheet item is a debit or a credit.
This is the sort order, that is now after years of running fine, giving trouble. I even stopped the script at the sort order step and then tried each sort separately and only the Posting Type Category field hung the sort. I even had the other 4 as one sort and it sorted fine. But when I had the Posting Type Category field, whether by itself or with any combination of the other fields, the sort hung my system and I had to force quit.
In desperation I have turned the Posting Type Category into an Auto-Enter Calc field and only that field, and now the script runs like it has always run. I even tried removing the calculation in database manager, closing the solution, opening it up again and re-doing the calculation, but the problem still persists.
The calculation for the field Posting Type Category is,
If ( general ledger::SUMMARY total of amount > 0 ; BS SUB CATEGORY dr ; BS SUB CATEGORY cr )
BS Sub Category dr/cr are plain text fields in the GL for Balance Sheet table and the "Summary total of amount" field is in the General Ledger table as a summary of the total amounts for a particular GL Code.
What is most annoying is that this script has been working without issue from around 2014 when we created the script.
1 of 1 people found this helpful
Something obviously has changed that is causing it to fail. Database corruption isn't likely. More likely is something regarding that field, or the summary used by that field's calculation, is now slowing it to a crawl.
If you put it back to the way it was, and sort a much smaller found set (like 1 record), does it work again? If so the amount of data it is chunking together to perform the sort is so massive that it's just freezing your system up.
You're on the right track for using auto-enter values instead of calculations. I would continue not only down that route, but figure out how to cache/update the data in the parent table, so that no related data is required as part of the sort order.
Yep, something had changed. In the table where the total amount comes from one of the other developers had changed the field so the calculation field broke.