5 Replies Latest reply on Mar 2, 2014 5:42 AM by SteveParrett

    How to total figures in multiple records?



      How to total figures in multiple records?


           Hi there

           I am 15 days into my evaluation of Filemaker and, using a manual and various videos on the net have managed to construct a resonably good solution for my organisation (a Rugby Team in the UK.  I have cracked a number of elements - security, scripting, and all the standard formats.  However, I just cannot understand OR work out the correct Search term to get help with the following, and I would be so grateful if someone could help me out in the next couple of days so I can present the solution and, hopefully, get the go-ahead to purchase! :)

           OK  I have a Joined Table that consists of the following elements (simplified right down for ease of explanation

           Player Name  Block-ID Session-Id  Attribute Value1 Value2  Calculated value (Only sum values if Attribute = Strength)

           Jo Blow                     1         2              Strength   1000   2000     3000

           Steve Pigeon           1          1              Strength   500       600     1100

           Steve Pigeon           1          2             Power         200      300       0

           Steve Pigeon           2          3             Strength     100     900       1000

           Harry Wright            1           1             Power         1000   2500    0

           In essence, on a separate layout I want to create graphical representations of the totals of the records in this table but to aggregate those totals based on things like the Player or the Blocks for each player.  This entails "reading" several records within a Tableand I have not got a clue how to do this.

           Apologies if this is a dumb question.  I'm rather proud of my progress so far in a lonely room with no other help, but this one is beyond me!


           Help :O)






        • 1. Re: How to total figures in multiple records?

               What kind of "graphical representation"? a chart?

               There are ways to compute totals and subtotals using summary fields. There are ways to get the same values using a relationship and the sum function. ExecuteSQL can use aggregate functions to also compute these totals. But the devil is in the details--including how you want to present those totals on the screen/page and whether you can set up a grand total or need to produce a sub total.

          • 2. Re: How to total figures in multiple records?

                 Thank you for the swift reply. 

                 As an example:  Add another Calculated field in the table above that has an aggregated record calculation if the attribute is strength.  This would for Steve Pigeon give him a Total Strength value of 2100 and a Total Power value of 300.  I would like to create a Pie Chart that has one slice at 2100 and one slice of 300.

                 In effect:using pseudo code terms:

                 Do While(Player Name = "Steve Pigeon")

                          Read record

                          Add Calc1 Value  to Total Calc1

                          Add Calc2 Value  to Total Calc2



                 Then use Total Calc1 and Total Calc2 to construct the Pie Chart.

                 As I intimated earlier, there may be a very simple thing I am missing here but I cannot spot it in the manual!

                 Thanks agai



            • 3. Re: How to total figures in multiple records?

                   But what I am describing does not use a script. It's a combination of other design elements in FileMaker that produce the needed values.

                   Am I correct that your Pie Chart charts data for a single player?

                   And each "wedge" of that pie chart represents a total for a different Attribute?

                   And you have two fields in each record? value1, value2?

                   And you need to chart sub totals of the sum of these two fields for each attribute?

                   If that is correct, you need a calculation field defined as

                   Value1 + Value 2

                   There is no need for this calculation to only add values if this is a specific attribute. We'll be using a different means to get totals for each for our pie chart wedges.

                   Define a summary field that computes the total of this calculation field.

                   Perform a find for just one player such as "Steve Pigeon" so that your found set is limited to the stats for that player.

                   Sort them by attribute.

                   In chart setup, specify the attribute field as the label for your wedges and this summary field as the field to use for the "width" of your wedges.

                   In the data source settings for your chart, make sure that it specifies: Current Found Set and Summarized Groups of records.


              • 4. Re: How to total figures in multiple records?

                     Thanks again.

                     It looks to me that I need to shift away from my normal way of looking at things which is by using a language to get my results.  Will take a long look at your answer tomorrow - been on the go for ten hours now.  I'm sure that you have given me the way forward that I needed. If not - I should be shot :)



                • 5. Re: How to total figures in multiple records?

                       Thank you so much for your guidance.  Having followed your advice this has opened up a complete cornucopia of delights!

                       Much appreciated.