Working with “old school” dates in Valence grids

Posted on June 11, 2021

If your company’s ERP system database had its genesis in the 1980s or early 1990s, and it hasn’t “evolved” a whole lot since then, you likely have a number of files where date values are stored inside numeric fields. Whether your dates are parsed into separate year/month/day fields, or held in single YYYYMMDD, CYYMMDD fields, etc., you’re already well aware of the hurdles you must jump over when trying to do otherwise simple things like date arithmetic, filtering, sorting, etc.

While Valence of course can’t make upgrades or changes to your physical file structures, there are ways in which Nitro App Builder and some simple SQL trickery can make the process of working with non-date date fields a little easier on you and your users when rendering the data in NAB grids. This tip focuses on how best to make your grids and grid filters work with these relics from the very early AS/400 days without sacrificing performance.

Suppose you have a file with numeric dates in YYYYMMDD format that you want to show your users in the form of a grid. It’s simple to create a data source to pull your desired columns from one or more files and then list them in a grid widget. You can then set the column renderer in the grid widget to convert your YYYYMMDD numeric column into a conventional date expression structure, such as MM-DD-YYYY or DD-MM-YYYY…

But what happens if you want to let users filter the list based on that date column?  Keep in mind that the grid’s column renderers are applied in the browser, after the SQL results have been pulled from the back-end. Hence, if you designate a numeric date column to serve as a filter field, it will not be treated the same as a regular date column that provides a calendar-like date picker option. Rather, the filter field will be a standard 8-digit numeric field, into which users will be expected to enter dates in the underlying YYYYMMDD format. This can be a bit confusing to users when dates in the grid are showing in a more conventional user format like MM-DD-YYYY, as illustrated here:

One way to work around this is to create a calculated column in the data source that converts the numeric date into an ISO date. This can be done using the TIMESTAMP_FORMAT function or your own custom function, like this (assume “SHIPDT” is a numeric field stored in YYYYMMDD format):

 SELECT ordno, prdno, qty, um,
        TIMESTAMP_FORMAT(char(shipdt),’YYYYMMDD’) as shipdt_iso
   FROM order_dtl

Since the calculated column is a true date field instead of a numeric field, selecting that column as a filter for the grid will automatically show the date picker button, and the field will follow the same date structure specified in the corresponding grid column.

This is an acceptable approach in cases where the underlying file has a relatively small number of records. However there can be a performance penalty associated with this approach that may be painfully noticeable on files containing millions of records. This is because whenever a WHERE clause is appended to a widget’s data source to limit results based on any specific column value, NAB must apply the same logic specified for that column in the SELECT clause. In other words, if you filter on a calculated column, the WHERE clause will likewise include that same calculation. Generally speaking, including a function or calculation over column values in a WHERE clause will require the SQL engine to evaluate every record in the file to determine which ones apply to the filter condition, though it does try its best to optimize.

One solution to this performance quandary that we’ve found works quite well is to create a basic two-column work file that serves a single purpose for all your data sources: to convert any numeric date column into a true ISO date column, with separate logicals or indexes on each column. You just need to write a simple one-time-use program to initialize the file one time to hold all dates from, say, 2000 to 2050, then you can include the resulting work file in all your data sources that will employ date filtering.

To illustrate, let’s say our numeric date-to-ISO date conversion global work file looks like this:

To use this work file, rather than converting shipdt to an ISO date in the select clause of our data source (as illustrated earlier), we instead just join it to our conversion file by the numeric date, resulting in a data source that looks something like this:

 SELECT ordno, prdno, qty, um, isodate as shipdt_iso
   FROM order_dtl
        INNER JOIN date_conv on numdate=shipdt

With this in place, you would pull the ISODATE date column from the work file into your grid, rather than the SHIPDT numeric column from the order detail file. And since ISODATE (aliased to SHIPDT_ISO in this case) is a true date column, the grid filter will include the standard date picker functionality and take on the same date format specified in the column renderer.

The performance improvement of using this INNER JOIN work file approach for converting numeric dates to ISO dates can be quite remarkable. In a crude test we set up here over a customer order file containing nearly 2 million records, we found our system was nearly three times faster getting a result set using this JOIN approach to filter for a specific date range, versus using a calculated date column in the SELECT and WHERE clauses.

There is another option to addressing this date filtering performance scenario that entails using a filter override program with your grid widget. This approach serves as a “hybrid” solution, in that you can use a calculated date column in the filter, but then apply the equivalent YYYYMMDD value over the original column in the WHERE clause to avoid the performance penalty. We will cover this filter override concept in next month’s blog post.