AnsweredAssumed Answered

Filtering related records using unstored calc fields

Question asked by TylerNelson on Aug 4, 2015
Latest reply on Aug 6, 2015 by philmodjunk


Filtering related records using unstored calc fields


I'm trying to calculate the sum total of Transactions that are in a related table, filtering certain types of transactions using the TOG, which works perfectly when I use stored Calcs. But I now want to filter the transactions using a related field (unstored calc) and it's not working. I'm wondering if there's a good workaround or another method I should use for this.

So I have a "Months" table where each field represents a month, which I'm using to calculate the sum total of related "Transaction" records via a relationship where the MonthDateStart is less than or equal to the TransactionDate and the MonthDateEnd is greater than or equal to the TransactionDate, so as to summarize the transactions in a date range. This works perfectly.

For one of my calculations in the context of Months, I calculate the sum total of Project related Transactions. Certain Transactions that have a foreign key called kf_ProjectID are Transactions associated with a Project. This is related to the primary key ProjectID. So I have a ProjectBoolean Case() calculation (indexed) that sets to 1 if there's a foreign key and 0 if IsEmpty. So in addition to the date range relationship filter, I have a relationship filter where a constant 1 calculation (zcConstant1) equals the ProjectBoolean. This works perfectly because both of the fields are stored calculations.

What's giving me trouble is when I try to add another filter using an unstored calc in the context of Transactions. I have a boolean field in the Projects table (DevelopmentBoolean) for Projects that are "Development" projects (set by the user with a checkbox). I have a boolean calc field in the context of Transactions that is set to mirror the related DevelopmentBoolean field. So from the context of Months I want to be able to calculate the total related Transactions associated with "Development Projects", but since the "DevelopmentProject" boolean is a related field (in the context of Projects), I'm not able to use a calc stored in Transactions, which doesn't allow me to filter properly when I'm in the context of Months.

I hope that makes sense. Any suggestions???