IFS Tips - Make Date-Based Searches a Cinch!

At one time or another, most of us will want to run a regular date-based search – it could be to look at Invoices raised in the last month, or review Customer Order lines that are overdue for delivery.  One of the great things about the Search function in IFS is that not only does it have in-build system dates, you can also use Oracle syntax to create dynamic queries. 

You can use many out-of-the-box IFS date parameters to create dynamic searches. These parameters are available in the standard date search drop down (#END_OF_LAST_WEEK#, #END_OF_LAST_MONTH#, #NOW#, #THIS_YEAR#, etc.)

The two simple Oracle date functions I demonstrate in the following examples are sysdate and add_months. Using sysdate will return the current date and time (the equivalent of the IFS parameter #NOW#) and add_months will let you add or subtract months from a given date.

Firstly, if I wanted to search for Purchase Order Lines with a Promised Delivery Date that was due in the last seven days, I can simply go to the Overview – Purchase Order Lines screen, open the search (F3 function key shortcut), and type in PROMISED_DELIVERY_DATE >=SYSDATE-7 AND PROMISED_DELIVERY_DATE <=SYSDATE AND UPPER(OBJSTATE) IN ('RELEASED', 'CONFIRMED') in the SQL tab of the Advanced search field.

Capture.PNG

So, let’s break down the statement:

PROMISED_DELIVERY_DATE >=SYSDATE-7: SYSDATE-7 subtracts seven days from today’s date. The >= (greater than, or equal to) sign, is searching for any Promised Delivery Date value greater than, or equal to, 7 days ago.

PROMISED_DELIVERY_DATE <=SYSDATE:  This statement is limiting the query to show anything up to today. The <= (less than, or equal to) sign, is searching for any Promised Delivery Date value greater than, or equal to, today. 

Combined, the result is any Purchase Orders that have become overdue in the last 7 days.

The last statement, UPPER(OBJSTATE) IN ('RELEASED', 'CONFIRMED'), is searching only for Purchase Orders that have been sent to, or confirmed by, the Supplier.

Now, if I want to then search Purchase Order Lines raised in the last month, I would change the first part of the SQL statement to be in PROMISED_DELIVERY_DATE>=add_months(SYSDATE,-1) against Date Entered.  What I am doing here is subtracting one month from today’s date, and searching for any Date Entered value which is greater than, or equal to one month ago.

Once you have your query, remember to save it. Then you can reuse it any day of the week!

Lenard GaffelIFS, Tips