Neat hack - but is it useful? (Management program)

Over the past few years, I have slowly been moving reports in the OP's management program from a simple SQL query with maybe one or two parameters (sometimes with a list of customers or therapists on one side and the required data for the chosen customer/therapist, and sometimes via two dates) to a more dynamic form, in which a form/screen/window has what Delphi calls a pagecontrol with two tabs (as shown in the picture on the left), where one tab has a form with various parameters and the other tab shows the result of the query. I see now that I wrote about this eight (!) years ago so I won't go into too many details here.

I see also that I posted the code that shows how the parameters are added, but not the basic query, which for the above form would be

select therapists.name, interviews.curdate, interviews.score, 
people.forename, people.surname, 
coalesce (people.origzehut, people.zehut) as origzehut,
dockets.id, ranks.name as rname, people.id as pid
from interviews
inner join therapists on therapists.id = interviews.therapist
inner join people on people.id =  interviews.pid
inner join dockets on dockets.id = people.docket
left join ranks on dockets.recommendation = ranks.id
where 1 = 1 

The parameters are added after the 'where 1 = 1' line, such as 'and therapists.id = :p1'. When I remember, I try to add a similar parameter tab to every new report.

Last Friday we were talking about adding more fields to a different report when suddenly I thought to myself that it would be a neat hack to add saved queries to the parameter tab. The idea is very simple: let's say that I've filled in the parameters for a query (e.g. select all the interviews held during 2020 that had a score of 5 - that's three fields) and I want to repeat this query at some point in the future, maybe choosing a different therapist (I don't have a way of choosing two or three therapists; it's either one or all). I want to save this query so that I can restore it again with minimal effort. Obviously (or not), the parameters stay on the screen until one exits the screen, so repeated queries with different therapists don't require new input (apart from choosing the therapist). But should one close the screen or run the query on another day, the values must be chosen again. Saved queries allows one to restore the parameter values.

This is less a technological problem (how to save the values and later restore them) and more a user interface problem: the user must be able to save, restore, update and delete queries, preferably whilst seeing the saved parameter values.

After thinking about this for about an hour or more, I came to the conclusion that the requirements would be a list box (showing the names of the saved queries), a button allowing one to save the values (that's the '+' button on the right hand side underneath the list box), a button allowing one to update the values (that's the middle button) and one button to delete saved queries (the '-' button on the left). Restoring/choosing a query is done simply by double clicking on the query name.

Pressing the '+' button brings up a dialog box in which one gives a name for the query, and then the parameter values (those that are not default, e.g. empty) are saved. My original data design had a 'savedquery' table consisting of an id, program number and query name, along with a son table with a pointer to the father id, the tab order of the field being saved and the value of the field being saved. During testing, I belatedly realised that the management program is multi-user, and so I added a field for the user to the 'savedquery' table. This way, two users can have different saved queries for the same screen with the same name, but with different values, and each user will get the values that she saved, and not those of another user.

I originally developed the necessary code as part of my example screen; once it was finished, I considered how to make the code more generic so that I don't have to write it again. Certain parts have to be modified per screen (saving and restoring the value of a given parameter) but no small amount is generic. I think about 60-65% of the code is generic and 35% specific.

Whilst writing this blog and looking at the code, I have just realised how I can make the entire saving and restoring procedures totally generic. Hurray!

I have my suspicions that no one is going to use this feature, but I enjoyed the technical challenge of writing it!