1 2 Previous Next 16 Replies Latest reply on May 7, 2014 9:11 AM by philmodjunk

    Do leading grand summaries actually get processed this way???

    JonathanWexler

      Title

      Do leading grand summaries actually get processed this way???

      Post

      This is actually a follow up to a different post of mine "Trying to be less stupid about why report runs slowly ...", where I think I have isolated the cause (but not the cure). 

      My report has a leading grand summary, with a bunch of totals that represents a fair amount of processing.  The report - when it finishes after 45 minutes - is correct and shows the grand totals for the found set which is generally about 800 out of 40,000 records.  I believe that the layout - before executing any of the OnLoad script steps - is generating layers of totals for the 40,000 records (I base this on part by seeing - for a few seconds while the found set is being isolated via a script step - grand totals for the what would be 40,000 records). 

           Is this actually what Filemaker does (by way of contrast, I think MS Access or Crystal Reports "cheat" and only compute/format leading grand total after the full report has generated)?

           Can I fix this by moving the grand total layout piece to be a trailing one (or will it still stubbornly compute 40,000 sets of totals?  Any other workarounds that are not occurring to me?

           Thanks

           Jonathan

            

            

        • 1. Re: Do leading grand summaries actually get processed this way???
          JonathanWexler

               Illogical (but I suppose possible) afterthought - would freezing the window until the Find records reduces; the working set eliminate this behavior??

          • 2. Re: Do leading grand summaries actually get processed this way???
            philmodjunk

                 I have the same issue with a table of over a million records.

                 Leading or Trailing will produce the same results. But you can set up a "default" found set of 0 records and this will eliminate the delay.

                 Pull the file down off the server and open it directly in FileMaker Pro

                 Go to that layout and do a Show All Records/Show Omitted Only to reduce the found set to 0 records.

                 Put the file back up on the server.

                 Now, when you access this layout from a client machine, the found set will always start as zero records and the summary/aggregate function calculations placed in your layout will evaluate very quickly.

            • 3. Re: Do leading grand summaries actually get processed this way???
              philmodjunk

                   Note: In FileMaker 13, you should be able use Perform Script on Server to show all/show omitted only to produce the same result without taking the file down off the server.

              • 4. Re: Do leading grand summaries actually get processed this way???
                JonathanWexler

                     Yeah, it finally occurred to me that OnLoad/OnEnter in FMP is not "before Load, with the option of overriding the default action of the event", but
                     "after Load".   An "unusual" design, an not IMHO a good one.  But at least I get it now, thanks to your help. 

                     I understand your technique for reducing the found set prior to running the report, but I do not get the sense that it would be persistent in a client-server environment (that is, any user who opened a layout that found all/a-whole-lot of those records would undo the reduction).   True?

                     One I am thinking of doing is having the layout for this report be a one-field dummy form on tthe line items table, reducing the found set to nothing, and then loading the not-listed-in-menu real report layout into the window. I think this would work, and be pretty fast. 

                     Thanks

                     Jonathan

                      

                      

                • 5. Re: Do leading grand summaries actually get processed this way???
                  philmodjunk
                       

                            I understand your technique for reducing the found set prior to running the report, but I do not get the sense that it would be persistent in a client-server environment (that is, any user who opened a layout that found all/a-whole-lot of those records would undo the reduction).   True?

                       This is not the case. What I am suggesting reduces the found set on the host as a single time "fix" of the issue. Each time a client opens the file, this empty found set will be the original state for that layout's table occurrence. Found sets produced by clients, on the other hand, do not persist. When they close the file, it will revert to the original found set last produced from a host context.

                  • 6. Re: Do leading grand summaries actually get processed this way???
                    JonathanWexler

                         Good to know.  I will give that a shot this weekend (I am remote from the server, and the .fp7 is 700MB, which takes a while to ftp)

                    • 7. Re: Do leading grand summaries actually get processed this way???
                      JonathanWexler

                           Well, that did not work.  Let me review the setup and what I did.

                           The server version is v9.  The  clients are all v10

                           What I did (and I checked the file timestamps, and I really believe I did just this):

                           1) RDP'd into the servers admin console, and closed the database

                           2) ftp'd the database to my laptop, and renamed the server .fp7

                           3) opened the database locally on my laptop

                           4) selected the layout

                           5) clicked Records/Select All

                           6) clicked Records/Select Omitted  (at which point,  the found set had a count of zero)

                           7) closed the database

                           8) ftp'd it from my laptop to the server

                           9) in the admin console, opened the database

                           10) used the client on my laptop to access the server

                           11) selected the layout, and it is still starting with the full table as the found set. 

                           Thoughts?

                           Jonathan

                            

                      • 8. Re: Do leading grand summaries actually get processed this way???
                        JonathanWexler

                             So, I "discovered" the table-occurences topic, and did a little reading, and yes, your suggestion should have worked.

                             And so I wondered if my script was confusing FMP.  I have a theory

                             the script opens a new window, does some stuff with another layout, closes the window, and gets around to

                             GoToLayout <name of the layout>

                             as opposed to

                             GoToLayout [original layout]

                             followed by Finds and Sorts. 

                             Could this be causing FMP to generate a different table ocucrence??

                              

                             Jonathan

                              

                        • 9. Re: Do leading grand summaries actually get processed this way???
                          JonathanWexler

                               OK - so I just got convinced that the FM Server v9 retains found set information on a table not a table occurrence basis.  Here is my evidence:

                               1) I did the whole procedure again.  Downloaded to run locally.  Got down to empty found set for the layout. Exited.  Uploaded to FM Server.  Same issue.  Local copy works as expected -shows 0/41887.   Changed the script to GoTo Layout [original-layout] (as opposed to the layout-name) - no impact.

                               2) I did notice that running v10 remotely against a v9 server, the startup screen - which is based on a different table shows - 20697 records not 1/20697 as when using FM Pro v10 locally. 

                               3) If I run remotely, and first use a different layout based on the same table, reduce its found set, and then bring up the real layout, the real layout quickly goes through the records in the other layout's found set, and then actually runs the script steps to produce the correct output in a minute or two. 

                               Jonathan

                                

                          • 10. Re: Do leading grand summaries actually get processed this way???
                            philmodjunk

                                 The found set is based on the table occurrence, not the table. It's also window based. If you have several windows open, each window will show a found set that is independent of the found sets accessible in the other windows.

                                 

                                      If I run remotely, and first use a different layout based on the same table, reduce its found set,

                                 I would guess that your "different layout" specifies the same table occurrence as the first, but does not have the summary fields on it and thus the summary fields do not evaluate and this avoids the delay, but still allowing you to reduce the found set to a small number of records or no records and thus there is no delay when you change layouts to the report layout as they share the same found set.

                                 The reason that I know that this is table occurrence based is that I can create two layouts that use different table occurrences to refer to the same data source table and yet can pull up different found sets on each layout.

                                 But I also went to my file, where I have a 5 milllion record table and refreshed my memory of how it's set up. I find that the script that I use to bring up this layout contains this code:

                                 Freeze Window
                                 Go to Layout ["LayoutforMegaTableHere" (TableName) ]
                                 Show All Records
                                 Show Omitted Only

                                 And this is very rapidly popping up the layout for this report with zero records.

                                 But I then open a simulated modal window (this system runs in FileMaker 10) in the foreground, where global fields are used to specify the search criteria to be used to produce the desired report. And it has occurred to me that another method for making this work is to use such a window (or just its layout) where the layout is not based on the table of many records, you specify your search criteria in the global fields and click a button that performs a script that a) enters find mode, b) changes layout to the report layout, c) finds the records and d) sorts them.

                                 This should also eliminate any delays due to the summary fields trying to compute summary values for large numbers of records.

                                 PS. I couldn't test the process of taking the file down off the network, adjusting the found set and returning it today as it was too late in the day to do that (Business is open and files are in active use at the moment), but I did try using a server schedule to modify the found set and I was unpleasantly surprised to find that this didn't work so I'm now wondering if the other procedure will also fail to work.

                            • 11. Re: Do leading grand summaries actually get processed this way???
                              JonathanWexler

                                   Been a little swamped with other matters. But my users are living with a slightly awkward procedure.  

                                   I am guessing that the snippet you show above was followed by a "Restore Window"

                                   I can accept the notion that the found sets are retained as a function of both (i) window ID and (ii) table occurrence. 

                                   But I confess to being mystified by (i) when found sets are saved, (ii) whether found sets are saved in respect of the database and/or windows and/or sessions and/or table occurrences, (iii) when they get retrieved and from what sources, and (iv) when they get invalidated for changes to underlying table data.   Any written documentation on any of this?

                                   Thanks so much!!!!

                                   Jonathan

                              • 12. Re: Do leading grand summaries actually get processed this way???
                                philmodjunk
                                     

                                          I am guessing that the snippet you show above was followed by a "Restore Window"

                                     No. Freeze Window does not permanently freeze the window. Things will update just as soon as the script finishes execution or some other script step--Refresh Window is one of several options--causes the window to refresh.

                                     Found sets aren't saved. They are emphemeral. That's why we are having a bit of a challenge here. Many things that are "left just as you had them" when you open the file in FileMaker Pro are not left that way when you access the file after it is hosted over a network and you connect as a client of the network. Many of those other settings consistently revert to what was left in place when you last had the file open as the Host--such as when you take the file down off the server and open it directly in FileMaker Pro or when you run a "server side" script to modify things. The values found in global variables are an example of this.

                                     But apparently, the "default" found set that you find when you first open a layout is not one of the items that you can control in this fashion--which is why I have describe two alternative approaches:

                                       
                                1.           The script that freezes the window and sets the found set to zero records.
                                2.      
                                3.           Using a script that collects user inputted search criteria on a separate layout popped up as a modal dialog that then accesses the layout and performs the find for the user so that they do not see this layout until that smaller found set needed for their report is in place.
                                • 13. Re: Do leading grand summaries actually get processed this way???
                                  JonathanWexler

                                       So, it seems all the solutions require that the "real" layout get launched by a GoToLayout script step.  My users have (for somewhere between 18 and 25 years ... no one seems to know) been launching layouts from the master layout list, and I do not want to try to change behavior. 

                                       So, I added a "dummy': layout referencing the same table, with a "Loading ..." message and no fields. It does a Show All, followed by a Show Omitted and GoToLayout for the real layout.

                                       Thanks

                                       Jonathan

                                        

                                  • 14. Re: Do leading grand summaries actually get processed this way???
                                    philmodjunk

                                         I don't see why that would be necessary.

                                         Enter Layout Mode

                                         Open Layout Set up and select the Script Triggers Tab

                                         Select the OnLayoutEnter trigger and have it perform something like this:

                                         Freeze Window
                                         Show All Records
                                         Show Omitted Only

                                         Instead of the last two steps, this script could also perform some kind of find--such as performing a find for all records with today's date--to produce a layout that is not blank but which has a relatively small found set.

                                    1 2 Previous Next