5 Replies Latest reply on Dec 10, 2014 4:12 PM by philmodjunk

    Summary Field

    MaxwellRobin

      Title

      Summary Field

      Post

      Hi Guys!

      I have a record with a field that a user populates with the quantity of items I have. I also have a field that shows the status of those items (received, packed, shipped, etc.). I would like to make a summary field to show how many items I have total, but sorted by its status. I made summary fields in a related table for the total received, total packed, total shipped, etc. but I didn't know how to make them only count the items with their status.

      Thank you for your help

      Max

        • 1. Re: Summary Field
          philmodjunk

          Summary fields cannot selectively summarize in that context. In a List view layout, you can sort to group your records by status and then you can add a sub summary layout part with your summary field located in the sub summary layout part to show the sub totals for records with each status.

          You can also use relationships that match only to records of one status and then a summary field from the same table will only summarize across the related set of records.

          You can set up calculaiton fields that are empty for all records except one status value and then summarize that calculation field.

          And ExecuteSQL can compute such a selectivie summary for one or all your possible status values.

          • 2. Re: Summary Field
            MaxwellRobin

            I think using a relationship to match records to a status would be the best option here. Would you mind walking me through that process?

            Max

            • 3. Re: Summary Field
              philmodjunk

              It's actually one of the more cumbersome processes as you need a different relationship ( and table occurrence) for each possible value of Status.

              Say you want a total of the Amt field for all values where Status = "active" in a related table:

              WIth Two tables named "LayoutTable" and "RelatedTable".

              In layoutTable define a calculation field named constActive. Define it by enteing this quoted text as it's calculation: "Active".

              Define your relationship as:

              LayoutTable::constActive = RelatedTable::Status

              Add a summary field to RelatedTable defined to compute the total of Amt.

              If you put the summary field from RelatedTable onto a layout based on LayoutTable, you'll get a total of all records in RelatedTable where the status field = "Active".

              Say you then wanted a total for all "Inactive" records:

              Add another calculation and another relationship:

              LayoutTable::constInactive = RelatedTable|Inactive::Status

              The same summary field added from RelatedTable|Inactive will show the total of all inactive records when it shows the total of all active records if added from ReleatedTable. (RelatedTable|Inactive and RelatedTable would be two Tutorial: What are Table Occurrences? of the same data source table.)

              • 4. Re: Summary Field
                MaxwellRobin

                In that case, how do I go about using ExecuteSQL to crate this? I really just want to make it simple if that's possible.

                Thank you

                • 5. Re: Summary Field
                  philmodjunk

                  Simplest is a filtered portal (A method that I neglected to mention previously) or to summarize different calculation fields that include if or case functions to separate the values by status.

                  If you put the summary field defined in the portal's table into a one row filtered portal, the summary field will display a value based only on the related records that pass the filter test. So you can set up a filter expression such as: PortalTable::Status = "received" and then your summary field shows a total of all related records that have been received. Duplicate the one row portal and change the filter expression and you can get a total for a different value of status. The major draw back to this method is that the values are display only, there no way to access the value of that field to use it in a calculation, for example.

                  If you want to try your hand at ExecuteSQL, see this thread: FMP 12 Tip: Summary Recaps (Portal Subtotals)