Quick Tip – Advanced filtering: Two fields with same (or different) values

Friday, December 30, 2016 - By Jon Gardner

..Embedding an Excel =IF statement within an =NL(Rows) expression

There are occasions when the requirement is that two fields in a transaction table should have the same (or differing) values. A recent example has been a situation where a client was tracking backorders; a situation where the field Order No. in the Item Ledger Entry table is the same as the field Original Order No.

This situation is not as simple as it seems as it requires an =IF Statement to resolve the query – to return (in this case) the Item Ledger Entry No. if the two fields are the same. In the example below we wanted to count the number of records (Entry No’s) in the Item Ledger Entry table where Order No. was the same as Original Order No.

=NL(“Count”,”Item Ledger Entry”,,”Entry No.”,”=IF(NF(,””Order No.””)=NF(,””Original Order No.””),NF(,””Entry No.””),0)

In another example a client was looking for data errors – records where the field, Unit of Measure Code was not equal to the field, Variant Code.

=NL(“Rows”,”Item Ledger Entry”,,”Posting Date”,$C$3,”Source Type”,”Customer”,”Entry No.”,”=IF(NF(,””UNIT OF MEASURE CODE””)<>NF(,””VARIANT CODE””),NF(,””Entry No.””),0)”,”Department Code”,$C$4)

In both these cases the =IF Statement requires a false value of 0 (zero) to proceed with either the Count or the Rows replicator.

This filtering is not for every day use – but a great piece of code to have in the reports kitbag for whenever a similar situation arises. If you need any assistance with such advanced filters please do not hesitate to Contact us