5 Replies Latest reply on Dec 27, 2012 7:27 AM by philmodjunk

    Sum a number field of all records IF another field is equal to something.

      Title

      Sum a number field of all records IF another field is equal to something.

      Post

           First, I'm about a week in to File Maker.  Doing my best to get out of Microsoft Access and am starting with a personal database I wrote for myself.  So I'm replicating in File Maker.  I'm really struggling with the logic differences between File Maker and Access.  It's definitely a different animal so I'm out of my comfort zone.

           Simple question:  I want to sum all records for the [cost] field IF the [category] fields equals 'savings'.

           I can create a found set easy enough and create a summary field from the found set BUT, I want a layout where all records are shown, but at the top there is a summary of how much money is in the 'savings' account.

           In Access it's a 2 second problem:  Create a feild where the control calcuation = Sum(IIf([Type]="Savings",[Cost])).  Done.

           What I've read so far talks about relationship joins which I'm struggling to think would be required.  I also am struggling because there are no 'Queries' which was straight forward to me.  The found sets (when saved) are not chooable when doing calculations.  I'm not following that but perhaps someone can elighten me.

           -Mike

        • 1. Re: Sum a number field of all records IF another field is equal to something.
          philmodjunk

               It may help to think of a found set as a layout's very dynamic, easy to change recordset or datasource. In Access, you have to modify the underlying SQL query and requery the database to produce a new set of records for a form or report. In FileMaker, you can simply perform a find. Where this can make for a very different design approach is that this often puts much more control over what records are present and in what sort order in the hands of the general user instead of the developer.

               A selective sum of records from a table IS quite easy to set up in FileMaker. But there are several different approaches possible. One method (FileMaker 12 only) even uses SQL to do that.

               It depends on the results you want on your screen and what level of user interaction controlling what data is selectively summed is necessary.

               The simplest, you have already figured out: Create a summary report on a found set of just the records you want to sum.

               The next simplest is to put that summary field in a sub summary layout part. This method allows you to pull up records from multiple categories, sort them into groups (Like "group by" in SQL) and then you can get a sub total for each group of records from your found set in addition to the grand total.

               Other options that work, use a relationship that matches by category, a filtered portal (FileMaker 11 or newer) to display the sums or the ExecuteSQL function can be used to query the table and return a total or a list of totals (Total for one category or many categories) in a single field.

               You may find this thread helpful on those choices if you have not already read it: Sum_Calculation based on condition

          • 2. Re: Sum a number field of all records IF another field is equal to something.

                 So maybe this helps define what I'm trying to do better in relation to the layout.  I'm looking into the SQL idea you mentioned.  I'm not an expert in SQL but I'm familiar with with.  I'm also getting into the other treads.

                 I took my database and put in some fictitious data.  Here is a screen shot and some specifics as to how I want it to look.

                  

                  

            • 3. Re: Sum a number field of all records IF another field is equal to something.
              philmodjunk

                   See this link on how to set up a "summary Recap" using ExecuteSQL in FileMaker 12: FMP 12 Tip: Summary Recaps (Portal Subtotals)

              • 4. Re: Sum a number field of all records IF another field is equal to something.

                     Thanks for the help.  This may be out of my league.  I can't get a grasp as to how FileMaker is structuring things (yet).  Maybe its all the time in Access.  The one thing I like about Access is that I know where I stand.  The structure it straight forward until you get to complicated items (Table, Query's,Form,Report...very discrete).  FileMaker seems to jumble them all together to me.  I can't understand the table in the data from the example.  It's almost like there isn't one.  I'll keep working at it.  I do see a user benefit.  It seems much easier to search for things and apply to other platforms (which is the other reason I'm trying to learn it as I'm an Apple Developer).

                     Thanks

                • 5. Re: Sum a number field of all records IF another field is equal to something.
                  philmodjunk

                       Then I suggest you consider setting up a summary report that lists each category with a total. This is essentially the same as the top portion of your screen shot without the list of records included as a separate section below. Buttons can be added that toggle you from a list or table view of your data to this summary report view of it.

                       Such a report is very simple to set up in FileMaker.