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.