1 2 3 Previous Next 43 Replies Latest reply on Mar 15, 2016 6:31 AM by beverly

    Sum a field across a found set??

    wfgclapp

      What's the most efficient way to sum a field across a found set of records in a script?

       

      Assume using a summary field is a no-go.

       

      The only way I know to do this is loop through the records.

       

      Does anyone know of something simpler?

       

      Thanks much.

        • 1. Re: Sum a field across a found set??
          Mike_Mitchell

          You can use a self-joining relationship from a global field to another instance of the same table with all the primary keys listed in the global.

           

          Why is a summary field a no-go?

          • 2. Re: Sum a field across a found set??
            taylorsharpe

            Mike's advice on the summary field is by far the easiest and what I would do.  But if I'm ever looking to do things with some special requirement, I often return to a SQL call, which is quite powerful, but takes more time to put together.  And, of course, you can do a loop through.  But FileMaker is notoriously slow doing Loops, so I would avoid that. 

            • 3. Re: Sum a field across a found set??
              beverly

              The GetSummary() function is what you need to get a value:

               

              https://www.filemaker.com/help/14/fmp/en/html/func_ref1.32.227.html

               

              this relies on the use of a summary field and the field to be sorted (as it would be on a layout with a sub summary part).

               

              beverly

              • 4. Re: Sum a field across a found set??
                Mike_Mitchell

                Of course, the key being you need the summary field - and we don't know what the objection to that is.

                • 5. Re: Sum a field across a found set??
                  schwjm

                  Like the other posts have said, there is not much way around using a Summary field of some sort of the goal is to calculate based off of an arbitrary found set. That is the whole point of Summary fields in my opinion--they are the only FileMaker concept to work with the found set (or subsummaries) instead of a relationship set, without using a script.

                   

                  If you are trying to avoid performance problems from summary fields, please clarify what you are trying to accomplish.

                   

                  One other suggestion: If you are hosted on FMS, you may be able to make use of Perform Script on Server to calculate things that become inefficient when latency is introduced. Depending on how flexible these found sets of yours, how they are obtained, and how often to be recalculated, you may be able to calculate the Summary field on the server and pass it back (but it of course would not automatically update this way.)

                  • 6. Re: Sum a field across a found set??
                    wfgclapp

                    thanks everyone. Wonderful info for me.

                     

                    It looks like everyone's biggest question is my objection to a Summary Field.

                     

                    Reason I want to avoid that is b/c the table I'm dealing with has 14 million records in it. These recs are invoice line items. The project I'm working on is essentially a little BI tool for our sales department. Lots of filtering and sorting of invoice data. The resulting found sets are relatively small, as in 4000-5000 records. So dealing with them is not bad. But I'm scared to death of what happens when I do a Show All Records or anything else that would result in grabbing either the entire table or maybe even a legit instance when the found set is exceptionally large. But, I'm relatively new to FM so there might very well be things I don't fully understand that could help me avoid such a scenario.

                     

                    To respond to another comment: the particular script I'm working on now would be run at night and would be run on server. So I would get that performance advantage.

                     

                    Thanks so much to all of you.

                     

                    Regards.

                    Martin

                    • 7. Re: Sum a field across a found set??
                      Mike_Mitchell

                      Yes, this is a valid concern, which is why you either (a) use Beverly's method and insert the value into a separate field using GetSummary ( ) or you (b) restrict the user's ability to Show All Records by trapping that command using a Custom Menu.

                       

                      If you don't actually put the summary field on the layout, it won't resolve. So if this is for server-side scripting, just put the field in the table and use a layout that doesn't have it (just to be sure; it may not resolve server-side unless called anyway).

                      • 8. Re: Sum a field across a found set??
                        siplus

                        if you do a find all and the summary starts to calc because it's visible, you get a progress bar, just press cmd-. and it will stop.

                         

                        You can also have a OnLayoutEnter script that sets $$HideSums to 1 and a hide when on your sum field, with a button "Totals" near it which when clicked will set $$HideSums to not $$HideSums, conditionally formatted to be red / green depending on $$HideSums.

                         

                        if you are in a script you can always check Get(FoundCount) before going to "dangerous" layouts.

                        • 9. Re: Sum a field across a found set??
                          wfgclapp

                          Mike_Mitchell wrote:

                           

                          You can use a self-joining relationship from a global field to another instance of the same table with all the primary keys listed in the global.

                           

                           

                          Mike, would you mind explaining how you do this? Not sure I follow.

                          • 10. Re: Sum a field across a found set??
                            Mike_Mitchell

                            On the parent side of the relationship, create a global field.

                             

                            Insert into that field a list (return delimited) of all keys you want (i.e., a multi-key).

                             

                            Create a relationship between that global field and the primary key of the current table, using another TO of that table (a self-join).

                             

                            Now, all the related records will be the found set of the current table. You can then create a calculation (or use scripting) to run whatever aggregates you want.

                             

                            But again - a summary field is purpose-designed for the original use case. So this is a workaround in need of a problem.  

                            • 11. Re: Sum a field across a found set??
                              Stephen Huston

                              expanding on Siplus:

                               

                              Set the field to be Hidden when Get(foundCount) exceeds a certain number. Hidden fields don't have to resolve.

                              • 12. Re: Sum a field across a found set??
                                wfgclapp

                                Mike_Mitchell wrote:

                                 

                                On the parent side of the relationship, create a global field.

                                ...

                                But again - a summary field is purpose-designed for the original use case. So this is a workaround in need of a problem.  

                                 

                                Ah, I see. I didn't realize you could do a join on a list. Pretty nifty.

                                 

                                But you're right. In fact, I'm already there. It's the aggregating part I'm trying to lick.

                                 

                                It sound like I need to use the Summary Field. But my worry isn't users so much as...uhhh...the developer.

                                 

                                THanks.

                                • 13. Re: Sum a field across a found set??
                                  BruceRobertson

                                  Don't forget the ever popular replace operation on a global field.

                                  Let's say you add a global number field; zTotal.

                                  And we are summarizing the field Invoice_item::QTY

                                   

                                  Then you do not need a summary field; and this two step script works:

                                   

                                  Set Field [ zTotal; "" ]

                                  Replace [ zTotal; Invoice_Item::QTY + zTotal ]

                                   

                                  For your record count, it should work fine.

                                  • 14. Re: Sum a field across a found set??
                                    beverly

                                    I always forget that little gem. Thanks for the noggin knocking, Bruce!

                                    beverly

                                    1 2 3 Previous Next