8 Replies Latest reply on Mar 7, 2015 7:52 PM by philmodjunk

# Help with Sub-totalling data

### Title

Help with Sub-totalling data

### Post

I have data in a table structured like this:

 Deal ID Amount Type 1 \$10 F 1 \$12 F 2 \$16 F 2 \$30 N

How do I write a formula for use in a portal that totals Amount by Type?

• ###### 1. Re: Help with Sub-totalling data

There are several ways to do that, but how do you want the portal as a whole to look?

Do you want the rows shown but with an added column for the sub total? (which will then appear on every row.)

Or do you want one row for every Type--reducing your example data to two portal rows?

• ###### 2. Re: Help with Sub-totalling data

I want to use each sub-total in a formula, one formula for the data type F and one for the data type N, each of the two formula's will be in the same portal row, and the portal is already set up to look-up each row by the first column (DealID)

• ###### 3. Re: Help with Sub-totalling data

That makes no sense to me. There would be only one "formula" used and putting both subtotals in the same portal row doesn't make any sense to me given the data you show.

Do you want this:

 Deal ID Amount Type Subtotal 1 \$10 F \$36 1 \$12 F \$36 2 \$16 F \$36 2 \$30 N \$30

or this?

 Deal ID Subtotal Type 1 \$36 F 2 \$30 N
• ###### 4. Re: Help with Sub-totalling data

First off, thanks for engaging on this!

Maybe if I give you more detail on the use we can zero in on a solution.

The actual table has 69 rows.  There are 14 "DealID" types, each row has the value 1-14 in that field.  Within each of the 14 DealID cases, there are anywhere from 2 to 16 occurrences of a dollar amount, and each dollar amount is flagged as either of F or N in the "Type" field.

In the portal, each row starts with DealID.  Within each row I am trying to create two fields:  one takes the sub-total of amounts type N for each DealID and the other takes the sub-total of amounts type F for each DealID and does some math on them.

Does this help narrow the possible solution?

• ###### 5. Re: Help with Sub-totalling data

That definitely provides info not found in your original post. I am interpreting your last answer here to mean that you want the first of my two examples but with two columns of sub totaled data.

If you are using FileMaker 12 or newer, there are two basic approaches that come to mind: a) self join relationships to two other occurrences of this table that match to just the records to be summed or b) a pair of calculation fields with executeSQL function call that uses SQL to define the same relationships and calculate the same subtotals.

If you are using an older version, a) is your only practical option.

Currently, you have this relationship in order to have a portal:

LayoutTableOccurrence-------<PortalTableOccurrence (----< means "one to many" )

By adding two more occurrences of the portal table, we can set up relationships that match to records with the same ID but with a specified type of either "F" or "N". I will demonstrate how to do this with "F" types and leave it to you to generalize the approach to match to "N" types to get the same type of sub total.

Tutorial: What are Table Occurrences? of the PortalTable, I'll name it "PortalFTypes" and define this relationship:

PortalTableOccurrence::ID = PortalFtypes::ID AND
PortalTableOccurrence::constF = PortalFTypes::Type

constF would be defined as a calculation field that always returns the text constant: "F".

Now you can define a calculation field that evaluates from the context of PortalTableOccurrence with this expression: Sum ( PortalFTypes::amount ) to compute the sub total of all records with the same ID and which are labeled as Type "F".

• ###### 6. Re: Help with Sub-totalling data

Forgot something that may help you understand my answer. For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

• ###### 7. Re: Help with Sub-totalling data

thanks

got it working using a summary field and then calling totals in the summary field in the portal