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

    Summary field to count across multiple relationship

    MattLeach

      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