How to make Sub Summary Calculation?

[unisonicxl12000]

Hello everyone!

 

 

My first post! Sorry if this is way too simple, but I seem to have trouble finding the solution online and in my FileMaker Missing Manual book.

 

 

Essentially I'm trying to achieve an unsorted sub-summary function?

 

 

My problem goes like this:

 

 

I have 3 Sales Agents: Tom, Mike, John

 

 

Field 1 [text] denotes the currency that they were paid in, (I am not worried about converting it): "Currency" [Values: USD, CAD, EURO, etc.]

 

 

Field 2 [number] : "Paid Amount"

 

 

For example,

 

 

Record 1: Mike got paid: USD 1,000.00

Record 2: Mike got paid: CAD 200.00

Record 3: Tom got paid: CAD 300.00

Record 4: John got paid: EURO 600.00

 

 

 

 

I have a layout in a List View that sorts by the Sales Agents name in chronological sales date.

 

 

In a Footer at the bottom of this List View layout I would like to display the total summary of each currency.

 

 

ex.

 

 

USD = 1000.00

CAD = 500.00

EURO = 600.00

 

 

A Sub-Summary Part won't work because I don't want to sort the List View by "Currency"

 

 

Is there a calculation I can make to achieve this?

 

 

Thank you for your advice and expertise, this forum has been so educational and helpful, I really appreciate everyone who takes time to post here and share their knowledge!

[AHunter3]

a) Create these calculation fields, all of them result type = "text":

 

USD.cst = "USD"

CAD.cst = "CAD"

EURO.cst = "EURO"

 

b) Create three new SelfJoin relationships of your table to itself like so:

 

Your Original Table..........................New Table Occurrence

USD.cst = Field1 in UsDollarsTO

CAD.cst = Field1 in CanDollarsTO

EURO.cst = Field1 in EurosTO

 

 

NOTE: you did not say whether or not you first do a FIND on your list view to isolate just those records within a specified date range. If you do, you need to replicate that in each of the above new relationships, like so (using the UsDollarsTO relationship as an example):

 

USD.cst = Field1 AND

g.StartDate ≤ ChronologicalSalesDate AND

g.EndDate ≥ ChronologicalSalesDate

 

 

c) Now create these calculation fields which are the answer to your question:

 

 

USDSubTotal = Sum (UsDollarsTO::Field2)

CANDSubTotal = Sum(CanDollarsTO::Field2)

EUROSubTotal = Sum(EurosTO::Field2)

[unisonicxl12000]

This worked perfectly!!! I can't thank you enough!