7 Replies Latest reply on Nov 21, 2016 7:38 AM by David Moyer

    Sub summary report bad behaviour

    Stu412

      Hi there

       

      I've created a sub summary report which from a script finds a subset of 32 records from 20,000 and performs various summary calculations on them.  In addition, I have some static pre calculated data which appears on each of the 32 rows coming in from a warehouse table.  This report works fine, takes a couple of seconds to load and due to some of the calculations is a little lumpy when scrolling up and down through the 32 records, but it's acceptable.

       

      I've been asked to include a field which would allow end users to exclude a record prior to printing/PDF'ing.  My first thought was to simply put a text field with Y/N field on each row with a button script to simply flick between Y and N.  After this is set, the user would refresh and an Omit script would remove the N's.

       

      However, the report really goes slow after I change the first Y to N.  I get the 'Windows blue circle of doom' when trying to move down the 32 records which previously, before changing the Y to N, I could do easily.

       

      What on earth is FM doing here?!

       

      Before I change the text field, it works fine.  After I change the text field, it's stalled and useless.

       

      Any help really appreciated on this one.

       

      Thanks

        • 1. Re: Sub summary report bad behaviour
          coherentkris

          You should ask yourself what calculations/scripts have to refire when you change a checkbox value then follow the logic/chain of events to find the bottleneck.

          By your description I would guess the entire report has to be recalculated when the found set is disturbed and the found set is disturbed by the include/exclude pdf checkbox click action.

          Without more details we may not be able to help further.

          • 2. Re: Sub summary report bad behaviour
            Stu412

            Hi Kris

             

            At this point, I've set things up so that there's no need (so I think!) to recalculate anything upon changes.  The idea would be:

             

            1.Run report first time to screen (perform script, let summaries calculate, take a little time)

            2.Click and Y's and change them to N's

            2a. Scroll down (probably to records not onscreen) - click any other Y's to N's as required.

            3.Click a second script trigger to rerun the report one more time only to omit the N's

             

            However, the issue is arising at step 2a in that, before changes were made to the Y/N text field, scrolling down was quick.  After any change is made, it processes slower than the initial script find/render of step 1.

             

            Hope that has clarified things somewhat.

             

            Thanks

            • 3. Re: Sub summary report bad behaviour
              Stu412

              So by way of an update, I've now removed the script I use to change a Y to N and vice versa, instead trialling this with a keyboard entry into the same field.

               

              I now don't have any issues moving through to the next 32 records as I did before... 

               

              So it seems that something in the scripting area is causing a hang, although I've performed a Halt Script.

              • 4. Re: Sub summary report bad behaviour
                Stu412

                Solution found

                 

                Filemaker at its most evil...I had some ExecuteSQL fields positioned off screen, but on the layout.  Removing there in the past gave amazing gains in speed, but I kept them just in case.  Just now deleted them from the off screen part of the layout and everything is working fine.

                 

                Moral of the story, never use ExecuteSQL...ever.

                • 5. Re: Sub summary report bad behaviour
                  coherentkris

                  ExecuteSQL is an indispensable tool for FM development.

                  Just like any other "tool in the box" you need to know how and where to use it.

                  in carpentry using a palm/hand plane when you need a thickness planer just because you don't know how to use the thickness planer machine will cost you lots of extra time and frustration.

                  • 6. Re: Sub summary report bad behaviour
                    Stu412

                    I hear you Kris. 

                     

                    The main time I've needed it is for working and getting calculations based on data outside the found set. However, on several occasions I've been subjected to massive speed overheads due to the nature of calculations I'm doing that it's been a relief to remove it.

                     

                    In this case I was able to replace it with scripts and a data warehouse of static values, saving so much time on report loads.

                     

                    I will however merit it when used in combination with virtual lists.  That massively sped things up for me in another area of the solution I am developing.

                    • 7. Re: Sub summary report bad behaviour
                      David Moyer

                      off-topic, but Kris, that really makes want to get back into the shop.  It's been years.