Amount field condition in Aggregation selection mode of a DBlookup expression
I have a requirement to count the number of open invoices for an account. I am using the Aggregation selection mode of a DBlookup expression to count the unique number of values in a date field. The Select statements looks like:
Select Count of <date Field> from <Database>
The issue I am facing is that I am unable to include a condition for a Amount/Currency field. I want to select only debit items, and exclude credits. The fields of type 'currency' are not shown in the list of elements while adding an include condition.
Is this by design and why are we not able to use such field types in the conditions.?
Alternatively, I was thinking of using a temporary table to list the items and using a Table operation to remove the credit items and count the items, but unable to do a unique count.