4 Replies Latest reply on Mar 10, 2017 1:37 AM by ndveitch

    Issue with sorting records


      Hi All,


      I have a strange one. For the last few years a script has been running fine and then today just before the management meeting the script decided to stop working. There have been no updates to either FileMaker (as they are still on FM12) and there was no Windows server updates either. I downloaded a backup copy to my local machine to cancel out the network being an issue. Then I went through it in FM 12 (which it was written in), FM14 and FM15 and still the script hang's.


      I ended up going through the script steps in script debugger and when I got to the final sort order, that is where the system is hanging. I ended up running the script again but stoped it just before that step and then tried different sort orders and found that one of the related fields in the sort order is causing the script to hang. It is and always has been a calculated field and for the last few years this script has been working fine. Now today it decided to stop working.


      I am pulling my hair out as to why this is happening. I ended up turning the calculated field that was causing the issue into a text field and did a replace field to get the original wording back. I then re-ran the script and it worked like it has been.


      What can I look for to find out why this one calculated field is now causing issue???

        • 1. Re: Issue with sorting records

          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.

          • 2. Re: Issue with sorting records

            Hi Mike,


            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. 

            Screen Shot 2017-03-08 at 11.54.10 AM.png


            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.

            • 3. Re: Issue with sorting records

              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.

              1 of 1 people found this helpful
              • 4. Re: Issue with sorting records

                Hi Mike,


                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.