MattLeach

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:

Graph.png

 

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.

 

Thanks

Outcomes