Using multiple values in a query

Caveat: heavy programming content ahead.

Nearly two years ago, I wrote¹ about the 'temp' table that I had added to my blog manager program. As I wrote then (edited): I would like several different functions to output a list of blogs, each showing different data. This would mean using the multiple document interface (MDI) and displaying the same form several times. How would each instance of the form know what to display? I added to the temporary table a new field, 'instance' that would be part of the primary key; each query would get a unique instance and store its data (entries) along with that instance. The form that displays the data would be sent the instance number and so would retrieve the correct data. When the form closes, it deletes the data from the temporary table and frees the instance number for further use. In other words, the 'temp' table is being used to store data for output.

I thought of another, more sophisticated use, a few months ago. Due to limitations in Delphi, or rather limitations in how I write database programs in Delphi, a program is limited to passing a single value of an entity (e.g. a customer) to a query. In the (purposely) truncated picture of an input screen on the left, one is limited to passing a single value for 'Tag 1' (I am using this example solely because the input screen is in English). What happens when one wants to pass several tags to the retrieval query at one time? Enter the 'temp' table as a mechanism for passing multiple values to a query. 

I added a checkbox (here it's a radio button, but later on I discovered a problem with this) that when pressed brings up the familiar two listbox screen. Here one chooses tags from the left listbox then by means of the arrow buttons one transfers the tags to the right listbox. When one presses the 'OK' button, a new instance of the 'temp' table is created with the ids of the various tags chosen added to lines in the table.

The code for using the the temp table from this example is too convoluted to display here because apart from anything else, the output of the query is sent to another instance of the 'temp' table. Instead, I'll show the various necessary lines to support multiple values in the OP's management query ('s' is a string that contains the various lines of the SQL query, at this point consisting of the 'SELECT' and 'FROM' clauses but before the 'WHERE' clause; 'tipulim' is the name of the 'treatment types' table so an alias is used for the temp table).

 if rgTipulim.Checked
  then s:= s + ' inner join temp tipul on tipul.id = tipulim.id ';
 ... // part of the WHERE clause
 s:= s + 'tipul.instance = :p4';
 ... // parameters
  sdsShowByTreatment.parambyname ('p4').asinteger:= rgTipulim.tag; 

In other words, if multiple values are required than an extra line is added to the 'FROM'  clause, joining the temporary table to (in this case) the treatments type table. Later on the instance is added to the WHERE clause and finally the parameter is passed. This works very well.

Why did I write earlier here it's a radio button, but later on I discovered a problem with this? In that 'set values for retrieval' form, there is only one parameter capable of choosing multiple values, but in the form in the OP's management suite from which I quoted above, there are two parameters that can have multiple values - customers and treatment type - and if I used radio buttons (as I did at first), selecting one will deselect the other and vice versa. If one wants multiple values for both parameters, then a radio button is useless but a checkbox is fine.

That's how things stood six months ago. On Friday I was talking with the OP about selecting dockets that could be defined with one of several treatment types - in other words, a clear case of a multiple value parameter. But after adding this to the form, I was struck with a new idea: what if I wanted to save this combination of values in a similar fashion to saved queries?

I was thinking about how to implement this when I went swimming on Saturday morning; as a result of all that thinking, I swam better and faster as I didn't have the brain power to spare on quotidian matters such as breathing. I wish every swim was like this!

In a prior blog², I described how I extended the 'saved queries' table in order to store predefined complex sort conditions. Thus in terms of the data structures, I didn't have to change anything in order to store what I am calling 'bundles' - a saved list of multiple values from a table. To the two listboxes form I added the visual components of a saved query, but the code for saving and restoring the values was far different and somewhat complicated; eventually I figured it out (and improved it this morning).

One interesting observation is that these bundles are available wherever a query requires a customer or treatment type parameter, so once defined, they can be used in multiple forms. Another idea that occurred to me (once again inspired by Priority) but yet to be implemented is adding the capability of duplicating a saved query/sorter/bundle from one user to another. Using this, I can define the saved whatever without having to explain to the user how to define it, then allow the user to use it in practice. Whilst quite complicated to program, this is simple in conception.

In the course of writing this blog entry, I am reminded of what I wrote in my previous blog, Another addition was the possibility of displaying within the blog manager the list of blogs mentioned in the 'this day in history' list at the bottom of the blog. What that idea ignores are any blog entries mentioned in the text of the current entry (here there are three links to prior blogs). If I recall correctly, I was thinking that I would have to parse the text of a blog entry in order to find those links and that this idea did not appeal to me very much. Then I realised that I could easily display the 'this day in history' list in a suitable format for the blog manager program and forgot the idea of finding referenced blogs. That will have to wait for a few days.

[1] 1548
[2] 1680

---

This day in history:

Blog #	Date	Title	Tags
277	25/08/2010	More inbasket	In-basket, Psychology
396	25/08/2011	Spam mail	Spam
618	25/08/2013	Research questionnaire	ERP, Excel
754	25/08/2014	Computer woes	Personal, Computer
1415	25/08/2021	New oven gloves	Cooking
1660	25/08/2023	Preparing for the New Year ceremony	Musical group