Hit with an insane filter request? RPG (and SQL) to the rescue!

Posted on July 13, 2021

Lists or grids of business data are often pretty straightforward to filter, particularly when applying single-column comparisons. Do your users need a way to limit a list of customer orders to a specific destination country and shipping date? No problem! Two simple column filters — one over the ship-to country, one over the ship date — and the users are able to retrieve what they’re looking for.

But occasionally users may come to you with some more unusual filtering needs…

“Hey IT guy, for my weekly sales meeting I need a list of all customer orders that went to Scandinavian countries. But I only want orders that shipped on the last Friday of the month and contain at least one rainbow-colored item.”

Filling such a request would require a rather complicated combination of standard filters, and likely some complex logic in the underlying data source. 

Alternatively, you could add a single “Show me ‘Unicorn’ orders only” checkbox to your grid header, then have an RPG program behind the scenes create an intricate WHERE clause for the requested data set. In this tip we’ll demonstrate how to put complex filters behind a simple checkbox, as well as how to edit-check regular user filters.

The key to creating a custom filter over a grid is to link an RPG exit program to your widget.  The exit program can be used to ensure the user’s filter values are sensible, and/or override the resulting filter that is applied to the data source.

So to demonstrate this concept in an example, let’s create a grid widget to provide users a list of shipped customer orders, and include a special “Unicorn” checkbox linked to a back-end filter written in RPG. We’ll begin by creating an SQL-based data source over the demonstration order header file included with Valence, DEMOORD_H. The data source will use the following SQL statement:

  select * from demoord_h
   where status=’SHIPPED’
   order by schdate, orderno

With the data source in place, the next step is to link it to a grid widget, with two special additions:

Add a checkbox filter over a column not already being used as a filter.  
Add an RPG exit program to override the WHERE clause applied to this grid.

For purposes of this demonstration, we’ll add a standard date range constraint over the SCHDATE column, and we’ll commandeer the zip code column (SHPZIP) to function as our magic checkbox. This is on the presumption that users would never be needing an actual zip code filter. Otherwise we could pick another column, or add a dummy column to the data source.

So in the widget’s filter tab, we’ll set up the SCHDATE column as a “between” filter and transform the SHPZIP column into a checkbox column, as depicted here (note that we’ve overriden the filter text labels):

Note that when clicking on the checkbox cell, we’re prompted to indicate which condition should trigger the filter to apply.  We only want our special filter to be applied when the checkbox is checked, so we select “Checked” and override the custom value to *UNICORN, which our back-end RPG exit program will look for.

So, about that back-end filter exit program… Its purpose is two-fold: (1) to edit-check filter values when necessary, and (2) to optionally override the WHERE clause for the grid’s data source. This is where our special checkbox logic will come into play.

The RPG exit program should be modeled on the source code from EXNABFLT, with the “meat” of our edit-check and WHERE clause override placed in the Process procedure.  Ultimately the exit program must send a response to the front-end that includes the following:

success: true/false — “true” indicates no problems with the filter; “false” would stop any attempt from applying filters to the grid
msg: if success is false then whatever exception message you want to show the user should be specified here
filter: if success is true, the overridden WHERE clause override goes here

The source code for our example should look something like this:

** ————————————————————–
p Process         b
d                 pi
d lZipCode        s             10a
d lfrDateA        s             10a
d ltoDateA        s             10a
d lFilterString   s            256a   varying

  lZipCode = GetValue(‘SHPZIP’:’value’);
  lFrDateA = GetValue(‘SCHDATE’:’value’);
  lToDateA = GetValue(‘SCHDATE’:’value2′);

  // validate date range…
  if lToDateA     vvOut_success(*off:’FROM date must be prior to TO date’);
    return;
  endif;

  if lZipCode=*blanks and lToDateA=*blanks;
    // nothing to filter
    vvOut_success();
    return;
  endif;

  // if we made it here, we have at least one filter to apply…
  if lToDateA<>*blanks;
    lFilterString=’SCHDATE between ‘+SQ+lFrDateA+SQ+’ and ‘+SQ+lToDateA+SQ;
  endif;

  if lZipCode=’*UNICORN’;
    if lFilterString<>*blanks;
      lFilterString+=’ and ‘;
    endif;
    lFilterString+=’ SHPSTATE in (”NY”,”NJ”,”CT”) and’+
                    ‘ ORDERNO in (select ORDERNO ‘+
                                   ‘from DEMOORD_D ‘+
                                  ‘where ITEM like ”PCB%”)’;
  endif;

  vvOut_success(*on:*omit:’filter’:'(‘+lFilterString+’)’);

p                 e

As you can see, if the user checks the checkbox then the RPG program applies our special “Unicorn” limit to something a bit obscure, in this case orders that shipped to the US states of New York, New Jersey or Connecticut (state codes of NY, NJ and CT respectively), with at least one line item on the order being a PCB board. You can use your imagine to take this “deep filtering” concept as far as you like.

As a quick aside here, you’ll notice that in the original comments of the EXNABFLT source it shows how to override existing filter field values using SetValue() and then calling WriteAllFilters() to apply the changes to a global filter field, gOutFilter.  That approach can be used so long as you’re not removing any filters specified by the user.  But in our case here, where we’ve commandeered the zip code field for a special purpose, we do not want a constraint of SHPZIP=’*UNICORN’ included in the WHERE clause. Thus we’re just completely constructing the whole WHERE clause ourselves and passing it back to the front end.  Note that any WHERE condition specified in the data source definition (in this case, STATUS=’SHIPPED’) is not changed here; Rather, the filter assembled in this exit program is supplemental to it.

Finally, whatever name you gave your compiled exit program needs to be linked up to the grid widget.  This is done at the top of the Filter tab in the FILTER PROGRAM field, as depicted here:

With this in place, you can test your exit program right away by switching to the Configure tab and clicking on the checkbox:

And there you have it — a way to encapsulate some of the craziest filter logic you can conceive of, elegantly front-ended for your users with a simple checkbox!