7 Replies Latest reply on Jan 8, 2016 8:37 AM by Mike_Mitchell

    Problem with aggregating data across related fields

    filemaker@darnold.net

      I'm trying to Sum the subtransaction (middle table) amounts using the Aggregate function Sum from the budgetMonthSubTr.... TO (bottom right)

       

      Here's the key. Month, Year, copyTransactionMethod are all lookups from the Transaction table. This is the only way Sum (or SQL for that matter) will work on the current setup. If I have those three fields in SubTransactions as calculations pointed back to Transactions (i.e. SubTransactions::month = Transactions::month) - nothing ever shows up as a related field from the context of budgetMonthSubTr.... TO. 

       

      So it works fine upon first entry. However, occassionally an error is made and one of those values needs to be corrected. I've tried using the Relookup script step based on script triggers attached to those fields in Transactions, but it always errors out with "There are no fields that look up values based on the field "transactionUUID".

       

      Additional info:

      • Each sub transaction is created within a portal from a Transaction and transactionUUID is editable (a requirement to use the Relookup step)

       

      Any way I can fix this or alternate methods/approaches?

       

      Screen Shot 2016-01-01 at 5.09.17 PM.png

       

      Thanks in advance for any suggestions.

      David