Caveat: heavy programming content ahead.
Nearly two years ago, I
wrote1
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
blog2,
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 |
