Calculation using a subset of related records
Hi folks, I am new to FM and at the moment frustrated with my own inability to manipulate the stored data. Specifically, my greatest problem seems to be "fetching" the right records from related tables, on which to perform slightly more complex calculations. I have two tables: a table Transactions with the fields ISIN (text), Date (date), Transaction Type (list of text entries), and Amount (number); as well as a table Securities with the fields ISIN (text) and a portal showing related records from the Transactions table based on ISIN as break field. Now, I would like to create in the table Securities calculation fields showing (i) the sum of values in the filed Amount for all records matching the ISIN and a certain value in the Transaction Type field, as well as (ii) the same, further restricted on a certain date range. I really would be grateful for any help on how to accomplish this with a calculation and possibly without any scripting.