How to implement a Count If on Saved Search

There are instances wherein you need to create a Saved Search and use the Count If function similar to MS Excel. Count If function behaves in a way that when an event (maybe Transaction, Record or Item) meets a certain category/criteria then the system will include this on the count. An example of how this may be used is by getting the percentage of when an Item is used on Drop Shipment / Special Order transactions.

 

Implementation:

1.    Navigate to Transactions>Management>Saved Searches>New>Transaction.

2.    On the Criteria add Type is Purchase Order, Item Fields>Type is Inventory Item or Assembly Item and Main Line is Yes.

3.    On the Results add Number, Date, Name and Created From.

4.    Still on the Results add Formula (Numeric): CASE WHEN {createdfrom.type} = 'Sales Order' THEN 1 ELSE 0 END.

5.    Then select the following Summary Type: Count for Number and Summary Type: Sum for the Formula (Numeric) field created in step 4.

6.    Hit Save and Run or Preview.

 

In this case, when a Purchase Order is created from a Sales Order (which is the behaviour of Drop Shipment / Special Order), then the Item will be counted and if not then it will be disregarded. With this in mind, you can now implement enhanced / improved logical expressions on Saved Searches for other similar cases.