Sunday, March 31, 2019

Saved Search with separate columns for multiple time intervals

This article shows how to run a report on all inventory items and see in results separate columns for ship date within multiple time ranges. This can be achieved by creating a saved search and adding a formula field in the results tab of the search. The steps are outlined below:


1.To create a saved search for items that were shipped in two different time ranges navigate to Lists> Search> Saved Searches>New>Item or Inventory Item or Information Item. In this example let's take first time range of ship date between 01/01 and 6/30 and second time range before 01/01. On the Criteria tab and sub-tab Standard the filter should be defined as following: Transaction : Ship Date is before 6/30/2013


2. On the Results tab add a column filed type of Formula(Text)  labeled e.g. "Ship Date within 01/01/2013 and 06/30/2013".  Use the following formula below to fill into the Formula field:
CASE WHEN (ROUND (TO_DATE('01/01/2013', 'MM/DD/YYYY') - {transaction.shipdate}) <= 0)THEN TO_CHAR({transaction.shipdate}, 'MM/DD/YYYY') ELSE '' END


3. Click the button Save&Run


1. The sample formula above can be also used to create a similar formula for column field labeled as "Ship Date is before 01/01/2013" just by changing the operator in the expression above.

2. In order to show only the items with Ship date in your desired time range are displayed in the results of saved search,  go to the Results tab and set the value of the Sort By field to "Formula (Numeric)".


No comments:

Post a Comment