Jump to content
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.






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!

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

  • Create New...

Important Information

Terms of Use