Summary field to count across multiple relationship

Discussion created by MattLeach on Jun 19, 2012
Latest reply on Jun 22, 2012 by Malcolm

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.