Salesforce and other SMB Solutions are coming soon. ×

How to make Sub Summary Calculation?

Recommended Posts

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

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!

Share on other sites

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

USD.cst = "USD"

EURO.cst = "EURO"

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

USD.cst = Field1 in UsDollarsTO

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)

Share on other sites

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

• Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• Forum Statistics

• Total Topics
33.7k
• Total Posts
141.5k
×
×
• Create New...