3 Replies Latest reply on Oct 5, 2012 8:24 AM by philmodjunk

    Sorting slow in script using Filemaker 12

    Kj

      Title

      Sorting slow in script using Filemaker 12

      Post

           Hello,

           Note, this is not a fmp 12 issue. I just re-did this in 11 and it is the same. Converted to Filemaker 12 1 week ago. Made a new script and summary report for db. Here are the details:

           Created new table in db to hold field for summary report.

           Script:

           Go to layout New Summary Table, show all records, Delete all records. (records are imported daily so always need to update for report).

           Go to parent layout, show all records.

           Import field A from parent into Summary field. Import Field B from parent into Summary field.Import field C from parent into Summary field. (Since I need a summary of the values from three separate fields cannot do from parent. Needed this method).

           Go to layout Summary Totals, show all records. Sort records by subsummary field.

           This sort takes 8 minutes! However, if I show all records and sort records from the fmp menu the sort takes a couple of seconds. 

           Why?

           Thank you.

            

        • 1. Re: Sorting slow in script using Filemaker 12
          philmodjunk

               Most of the time is taken up by the import records operation, would be my guess, though any sort that reorders records on a summary field is likely to be slow.

               Instead of importing records into this table, why not define a calculation field:

               FieldA + FieldB + FieldC

               and then define a summary field that computes the total of this calculation field?

               If this works, you no longer need to import records into another table just to put together a summary report.

          • 2. Re: Sorting slow in script using Filemaker 12
            Kj

                 As it moves along, the scrip shows what it is doing. The delete and import appear to take seconds. The sorting goes on and on (27534 records remaining to sort, 26345 records remaing to sort, etc). You may be right, this is what it is saying to me.

                 Field A, B and C are text fields each holding a single text value from a drop down list of 35 choices (What is most important 1, 2 and 3.).  I have created subsummary reports for field A and another for B and another for C. But then they wanted to see what it looked like with all three together. It would be 10,000 record from the parent times 3 = 30,000 values. I do not think A + B + C would get me that result. Tell me if I am wrong.

                 thank you.

                  

                  

            • 3. Re: Sorting slow in script using Filemaker 12
              philmodjunk

                   During the sorting process, your table of newly imported records are likely also indexing some fields and this will take a lot of time.

                   I have created subsummary reports for field A and another for B and another for C. But then they wanted to see what it looked like with all three together

                   I assumed from your last post that fields A, B and C were what were being totaled by a summary field. Now, I see this is not the case. I would guess from your posts that you have three fields and that a "category" type value is being entered into one or a combination of these fields and that the same value might be entered into any one of them. Is this the case?

                   If so, then I'd use a related table of up to 3 records in place of your fields A, B, and C. ( A portal or group of portals can be used in place of the individual fields on your current layout.) A summary report can then be set up for what you want from a layout based on this related table (calculation fields can pull data from the parent table so that summary fields can produce aggregate values such as totals, counts and averages) and no data import would be needed.