11 Replies Latest reply on May 1, 2017 2:08 PM by beverly

    Total across certain records




      Is there anyway to get a total from a field across certain records?




                                          x                                                     x

                                   Record A               Record B           Record C       Record D


      Field 1                          4                            8                     12                  6




      Total          16


      Only want to add certain and get a total.   Not sure I explained that well, but please let me know if you have any ideas or need more infor.



        • 1. Re: Total across certain records

          Create a summary field of total field 1.

          If you isolate only the records you want to total, in your example case records A and C, then the summary field will have the total.

          For sake of discussion lets pretend records A and C have a primary key field with values 34 and 39

          Example script....

          Enter find mode.

          set field(the_table::primary key field; 34)

          new record request

          set field(the_table::primary key field; 39)

          Perform find and the summary field will have the total..


          You could also use ExecuteSQL( "SELECT SUM(field1) FROM the_table WHERE primary_key_field=? OR WHERE primary_key_field=?" ; "" ; ""; 34; 39)


          I'm sure there are other methods but these were the first two that came to mind.

          Alot depends on what you want to do with the sum.

          • 2. Re: Total across certain records

            Ok, I will give that a try, thanks

            • 3. Re: Total across certain records

              coherentkris ideas are good, but if you need to sum "particular records" this might be a database design issue instead. Consider creating a 1:M relationship: create a child table whose records you would logically sum.


              An example would be something like CUSTOMERS->INVOICES->INVOICE_ITEMS


              So rather than trying to have INVOICE_ITEM data (and amounts) in the CUSTOMERS table, you would use DB relational design and have those amounts linked (and then easily "SUM-able" for a given CUSTOMER).




              If you requirement is more "ad-hoc", then a summary field or SQL as already suggested is a good way to go.

              • 4. Re: Total across certain records

                Still working on this....

                Show just in case I didnt explain it well... I need the total in blue for each in white..

                there all in the same database, displaying here with portals.


                • 5. Re: Total across certain records

                  There are more people in the database, but only want the total from these...or certain

                  • 6. Re: Total across certain records

                    And what did you do to organize the data into columns like this? (there's more than one way).


                    It would appear that the common info that selects the data to be summed is that they all have the same date?


                    If so, then you need to either match (via relationship), filter (filtered portal), Find (scripted find) or Select (SQL query) by that common date value in order to correctly reference the group of records from which you want to total the records.

                    • 7. Re: Total across certain records

                      The Data is in the Daily Database, then I duplicated daily, 10 times, daily1, daily2.....  Then I set the relationship, to equal the year = year  and the member id =  a number field 1 (this is were I put the id for the record it want. there are 10 number files...

                      Display with 10 different portal daily 1 - daily 10 


                      shows 31 records, starting with 1 for jan, 28 or 29 records for feb starting with 32.....


                      Frustrating when you can see the data on the screen but  just can't get it to add up, LOL

                      • 8. Re: Total across certain records

                        If you have a relationship that matches to the records that you want to sum, average or compute some other aggregate value, there's an aggregate function that you can use such as:


                        Sum ( RelatedTable:: Field ) to sum all related records. So if you can set up an unfiltered portal that lists all the records that you want to sum, the above expression, if referring to the same table occurrence as the portal, will compute a sum of the specified field across those records.


                        If you define a summary field to compute the same aggregate value, but define it in the related table, it will produce the same value, plus, if you place it in a filtered portal, it will total only the related records that pass the filter.


                        And then you also have the other two options: A scripted find (find the records, set a variable or field in a related parent record to the value of the summary field described earlier.) or A Select Query in ExecuteSQL which can also use the SQL version of the same aggregate functions such as sum.

                        • 9. Re: Total across certain records

                          look for pivoting reports - or check this out:


                          Dropbox - PivotingReport.fmp12

                          1 of 1 people found this helpful
                          • 10. Re: Total across certain records

                            They are also called "Cross Tab" reports.

                            • 11. Re: Total across certain records

                              short for "cross tabulation" (aka "contingency table"). PivotTable is TM by MicroSoft!