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???