Conditional Formatting by sub-summary calculations

Discussion created by realgrouchy on Mar 15, 2017

It must be sub-summary day, because I have a problem similar to that in this thread ( Sub-Summary with Calculations  ).


I have a table, Transactions, and each transaction has a BatchNumber.


I have a related table, Transactions 2, which matches the customer ID and Batch Number in Transaction 2.


In the Transactions table, I have an unstored calculation field, "Count:BatchMember", which has the formula "1 / Count ( Transactions 2::Customer ID)". This allows me to have a summary field, "Count:Batch" to count the unique number of customers in each batch (i.e. if a customer has 2 transactions in the same batch, each one counts for 1/2, adding up to 1). (I also have "Transactions::Count:Customers", which is just a straight-up count of Customer IDs)


I have a table, Batches, which is related to the Transactions table via the Batch Number field.


Within the Batches table, I have a calculation field, "BatchCount" that takes the sum of Transactions::Count:BatchMember



The upshot of this is that, in a sub-summary report in the table Transactions, sorted by Transactions::Batch Number, I can have the following data:

<<Count:Customers>>/<<Count:Batch>> correctly counts the number of customers that are in the batch of that sub-summary line. This is the number of customers in the selected transactions.

<<Batches::BatchCount>> correctly counts the number of customers in that batch, including those not in records not part of the current search.


This allows me to verify at a glance if <<Count:Customers>> is less than <<Batches::BatchCount>> and therefore I haven't printed all customers in that batch.


However, when I try to conditional format the text field with <<Batches::BatchCount>> to highlight it if these numbers are not the same for a batch, the calculation seems to be calculating Transactions::Count:Batch among all open transactions, rather than just those with the batch number in that sub-summary section. The <<Count:Members>> text field is working, so why does the same field not report the same number in the calculation?


I've tried various combinations of GetSummary but I can't for the life of me figure out how to get the conditional format calculation to give me anything other than the total for the entire FoundCount, or a blank result.


All of the above fields are in the same Sub-summary when sorted by Transactions::Batch Number section.



I've also tried having separate fields for <<Count:Customers>> and <<Batches::BatchCount>> and conditionally formatting them. "Self < Batches::BatchCount" works for the <<Count:Customers>>, but "Self > Transactions::Count:Customers" doesn't work for <<Batches::BatchCount>>.



As a (ridiculous) workaround, the only thing I've been able to do is apply the text I want ("<<Batches::BatchCount>> total customers in batch"), make that text white, then put a field behind it containing <<Count:Customers>> with white text on a white background conditionally formatted with "Self > Batches::BatchCount" to turn dark grey if true, to make the white Batches::BatchCount text appear on top of it.



Am I missing some magic formula for properly formatting the formula for the conditional formatting?


Thanks in advance.


- RG>