AnsweredAssumed Answered

Using Where Statement on Aggregate function in Lookup Field

Question asked by WilliamK on Jan 24, 2013
Latest reply on Jan 25, 2013 by philmodjunk


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.