3 Replies Latest reply on Jun 22, 2012 8:46 PM by Malcolm

    Summary field to count across multiple relationship


      I have a bit of a tricky situation that i am not sure how to resolve so i thought i would ask the experts.


      Below is a view of the relationship graph in the database:



      Here is the situation.


      I have a layout that is setup as a dashboard that reports information based on property types using a field - Property::Property_Type


      In the Transaction table there is a field for Transaction_Amount. I was able to setup a summary field that totaled the transaction amounts based on the property type and that has been working great.


      The client wanted to do something similar using a field from the Loan table. There is a field for Original_Balance in the Loan table which they would like to be able to total based on the Property type of properties related to the same transaction. I attempted to setup a summary field and a filtered portal as i did above although it does not appear to work.I would assume this is due to the relationship.


      So i was thinking of setting this up using ExecuteSQL although i am not sure if this will work. I was looking through the FileMaker help file and noted that their example did a sum based on a join table. Would i be able to do so with the above relationship setup?


      Bascally would need the sum of Loan::Original_Balance where Property::Property_Type = "value"


      Perhaps i am completely off base and there is a better solution but this one has me stumped.