SQLRPGLE – A Sortable Subfile with Column Headings and Search using RTNCSRLOC
If you have been following the subfile series, you will know we already have a solid working example in the SORTSFLBOB program. Now it is time to add the finishing touches that make it feel properly modern.
Let’s go with the assumption that our users are demanding! They want to click a column heading and have the data sort. They also want to type a few characters in a search box and filter the list instantly. This blog shows you exactly how to deliver both features using RTNCSRLOC for cursor detection, a clean DDS design, and dynamic SQL in RPGLE.
The whole thing stays simple, maintainable, and user friendly. Let’s dive in.
DDS Display File Design – Column Headings, Search Fields and RTNCSRLOC
Here is the complete display file source we are using. It builds directly on the earlier example but adds the visual targets and search fields.
A DSPSIZ(24 80 *DS3)
A INDARA
A CA03(03 'Exit')
A CF09(09 'Search')
A R SFL01 SFL
A SORTDATE 10S 0O 9 3
A SORTTIME 10S 0O 9 14
A SORTUSER 10A O 9 25
A SORTTEXT 40A O 9 36
A SORTSTATUS 1A O 9 77
A R CTL01 SFLCTL(SFL01)
A RTNCSRLOC(&RCD &FLD &POS)
A 31 SFLDSP
A 32 SFLDSPCTL
A 30 SFLCLR
A 33 SFLEND(*SCRBAR *MORE)
A SFLSIZ(9999)
A SFLPAG(0013)
A OVERLAY
A RCD 10A H
A FLD 10A H
A POS 4S 0H
A 1 3'SORTSFL - Column Sortable Subfile -
A Example'
A DSPATR(HI)
A 3 3'You can sort this subfile two ways-
A :'
A COLOR(BLU)
A 4 3'(a) Position MOUSE on a blue colum-
A n heading and press F9 to sequence -
A or'
A COLOR(BLU)
A 5 3'(b) enter a partial search charact-
A er string in the search field below'
A COLOR(BLU)
A HEADDATE 10A O 7 3DSPATR(UL)
A COLOR(BLU)
A HEADTIME 10A O 7 14DSPATR(UL)
A COLOR(BLU)
A HEADUSER 10A O 7 25DSPATR(UL)
A COLOR(BLU)
A HEADTEXT 40A O 7 36DSPATR(UL)
A COLOR(BLU)
A HEADSTAT 1A O 7 77DSPATR(UL)
A COLOR(BLU)
A SRCHDATE 10A B 8 3CHGINPDFT
A COLOR(BLU)
A SRCHTIME 10A B 8 14CHGINPDFT
A COLOR(BLU)
A SRCHUSER 10A B 8 25CHGINPDFT
A COLOR(BLU)
A SRCHTEXT 40A B 8 36CHGINPDFT
A COLOR(BLU)
A SRCHSTAT 1A B 8 77CHGINPDFT
A COLOR(BLU)
A R CMD01
A MOUSECLICK 2Y 0B 23 3PSHBTNFLD
A PSHBTNCHC(1 '>Enter')
A PSHBTNCHC(3 'F3 e>Xit' CA03)
A PSHBTNCHC(9 'F9 >Sort' CF09)
A 24 3'No Data matches the Selection crit-
A eria'
A N69 DSPATR(ND)
A COLOR(RED)A few key design decisions make this work nicely.
The HEAD* fields (HEADDATE, HEADTIME, HEADUSER, HEADTEXT, HEADSTAT) sit on row 7. They are output only, underlined, and coloured blue. They are purely visual targets. The user can mouse over them or tab to them. No input capability is needed.
RTNCSRLOC(&RCD &FLD &POS) lives on the CTL01 control record. When the user presses F9, this keyword populates the hidden fields so the program knows exactly which field the cursor was sitting in. That is the magic that lets us support both mouse clicks and keyboard navigation.
Directly below the headings on row 8 we have the SRCH* fields. These are input capable with CHGINPDFT so the user can type partial values straight away. No extra pop up windows, no fuss.
At the bottom we use a PSHBTNFLD called MOUSECLICK. It gives us friendly mouse buttons including the all important F3 Exit option defined with CA03.
Helpful instructions on rows 3 to 5 tell the user exactly what to do. Never underestimate how much clearer a screen becomes when you spell out the two ways to sort or search.
Detecting Column Clicks with RTNCSRLOC
When the user positions the cursor on a heading (or even on a search field) and presses F9, control passes to the HandleColumnClick procedure. Here is the code:
Dcl-Proc HandleColumnClick;
Dcl-S newSortField char(10) inz('');
Select;
When (fld = 'HEADDATE' or fld = 'SRCHDATE');
newSortField = 'SORTDATE';
When (fld = 'HEADTIME' or fld = 'SRCHTIME');
newSortField = 'SORTTIME';
When (fld = 'HEADUSER' or fld = 'SRCHUSER');
newSortField = 'SORTUSER';
When (fld = 'HEADTEXT' or fld = 'SRCHTEXT');
newSortField = 'SORTTEXT';
When (fld = 'HEADSTAT' or fld = 'SRCHSTAT');
newSortField = 'SORTSTATUS';
Other;
SortField = 'SORTDATE';
SortOrder = SORT_ASCENDING;
Return;
EndSl;
If (newSortField <> '');
SortField = newSortField;
ToggleSortOrder();
EndIf;
end-proc;The FLD hidden field tells us precisely which heading or search field was under the cursor. We map it to the real database column name and then toggle the sort order. If nothing sensible was detected we default back to sorting by date in ascending order.
It works beautifully whether the user clicked with the mouse or tabbed to the heading and pressed F9. No extra indicators, no position calculations, just clean field name detection.
Search Fields and Dynamic SQL Filtering
The SRCH* fields on row 8 are read straight into the BuildWhereClause procedure. Partial matches are supported and everything is case insensitive thanks to UPPER.
Dcl-Proc BuildWhereClause;
Dcl-S conditions varchar(1000) inz('');
Dcl-S hasConditions ind inz(*off);
WhereClause = '';
If (srchdate <> '');
conditions += ' AND SORTDATE LIKE UPPER(''%' +
%trim(%scanrpl('''':'''''':srchdate)) + '%'')';
hasConditions = *on;
EndIf;
If (srchtime <> '');
conditions += ' AND SORTTIME LIKE UPPER(''%' +
%trim(%scanrpl('''':'''''':srchtime)) + '%'')';
hasConditions = *on;
EndIf;
If (%trim(srchuser) <> '');
conditions += ' AND UPPER(SORTUSER) LIKE UPPER(''%' +
%trim(%scanrpl('''':'''''':srchuser)) + '%'')';
hasConditions = *on;
EndIf;
If (%trim(srchtext) <> '');
conditions += ' AND UPPER(SORTTEXT) LIKE UPPER(''%' +
%trim(%scanrpl('''':'''''':srchtext)) + '%'')';
hasConditions = *on;
EndIf;
If (%trim(srchstat) <> '');
conditions += ' AND UPPER(SORTSTATUS) LIKE UPPER(''%' +
%trim(%scanrpl('''':'''''':srchstat)) + '%'')';
hasConditions = *on;
EndIf;
If (hasConditions);
WhereClause = 'WHERE' + %subst(conditions : 5);
EndIf;
end-proc;Notice the %scanrpl technique to double up any single quotes the user might type. It keeps the dynamic SQL safe without overcomplicating things.
The LoadSubfile procedure then assembles the full SELECT statement, adds the WHERE clause if needed, appends the correct ORDER BY, prepares the statement, opens a cursor, and loads the subfile. We close the cursor properly afterwards and use a simple LogMessage procedure for any errors. Solid and easy to follow.
CA versus CF Function Keys – Clean Exit with CA03
Look at the push button definition again:
A MOUSECLICK 2Y 0B 23 3PSHBTNFLD
A PSHBTNCHC(3 'F3 e>Xit' CA03)
A PSHBTNCHC(9 'F9 >Sort' CF09)The Exit choice uses CA03. This is deliberate. When the user clicks F3 (or presses the key), CA03 returns only the function key. It does not send the current screen contents back to the program. That means the user can exit immediately even if the search fields contain incomplete or invalid data. No validation gets in the way and nobody gets frustrated.
In the RPG we simply check the indicator:
When (indicators.exit);
leave;Using CA03 here gives the quick, frustration free exit that users expect. It is one of those small details that makes an application feel polished.
Using AI as Your Coding Companion
This combination of clear DDS with obvious visual cues, RTNCSRLOC for cursor detection, and well structured SQLRPGLE procedures is exactly the kind of code that benefits from having an AI pair programmer looking over your shoulder.
The AI will remind you to keep the HEAD* fields output only with consistent colouring and underlining so the visual target is obvious. It will check that RTNCSRLOC is returning the field name you expect and that your HandleColumnClick logic covers every heading. It will review the BuildWhereClause escaping and suggest the %scanrpl technique for single quotes. It will also catch small things like making sure the subfile clear and reload logic is clean before every EXFMT and that you close the cursor properly after use.
Most of all it keeps the whole solution user friendly and maintainable. When the DDS tells the user on screen exactly how to sort or search, and the RPG reacts cleanly to either method, you end up with an application that feels thoughtful and professional.
That is the real value of treating AI as a coding companion. It helps you produce code that is not just correct, but clear, robust, and a pleasure for the next developer (or future you) to work with.
Walk through the DDS & RPG Code with me and see it in action:
Wrapping Up
There you have it. A complete, practical example of a sortable and searchable subfile using modern SQLRPGLE techniques and RTNCSRLOC. The pattern is reusable across many applications and keeps both the display file and the program easy to understand.
Give it a try in your own codebase. Add it to an existing subfile, tweak the column list, and watch how much nicer the screen feels for the end user.
If you are building subfiles regularly, this approach will save you time and reduce those “how do I sort this?” support calls.
Until next time, keep the code clean and the users happy.
