Using Where Statement on Aggregate function in Lookup Field
I'm sure this question or ones simular to it have been asked many times, but I can't find an anwer that makes sense to me. I have a table called EbayInvoices. This table has the following fields. "EbayItemID", FeeDate", "FeeType", "FeeAmount:".
I have a second table called FeeSummary that will show me a summary of all the fees related to a specific EbayItemID. This table will have the Fields "EbayItemID", "TotalInsertFees", "TotalFinvalValueFees", TotalBuyItNowFees", etc. The relationship between the two tables will be EbayItemID.
I want all the different fee fields in the FeeSummary table to be calculated look up fields. So for instance the TotalInsertFees field needs to show me a sum of all the related records in the EbayInvoices Table where the FeeType field = "Insert Fee"
In SQL I could do this with a SUM aggregate function and filter it with a where clause. How can I do this in FM? I am so used to working with SQL that I am have a hard time wrapping my head around how to do similar things in FM. I know I could probably do this with a report and grouping, but I would rather have this be a table with permenant stored look up fields as I want to use this data in other ways.