Improving the dual list box dialog

Database theories state that there are three types of relations: one to one, one to many, and many to many. The first two are easy to model, and the third is generally implemented by some kind of link table. This link table can either masquerade as a normal table, if it includes other information above the simple many to many link, or it can be degenerate, in which it includes solely a link to one table and a link to the other table.

In the programs which I have written for the Occupational Psychologist (OP), there have always been at least one such degenerate link table. I've been trying to think of several past examples, but they all require understanding of the kibbutz way of life to make sense, so I'll  give an example from the medical world. If a doctor suspects that a person is suffering from complaint A, then he will order a blood test which will include testing for A1 (say glucose), A2 (say urea) and A3 (creatinine). The doctor might order a blood test for set B which will include A1 as well as B1, B2 and B3. The many to many relationship is that one specific blood test (glucose) can appear in many 'blood test sets', and each 'blood test set' can include several individual blood tests.

When one of the sets (in this case, the individual blood test) contains only a small, finite number of members (let's say 30), I often implement this with a 'dual list box dialog'. This is one of the standard dialog boxes in Delphi which I have been using for years and looks like this

Over the years I have refined this dialog box to work in Hebrew and to load its items from database tables. The right hand list box would be populated by (in our example) the individual blood tests which have already been assigned to the blood test set, and the left hand list box would be populated by the tests which have not been assigned to this set.

A database table would be defined with two fields: one containing the key of each blood test set and one containing the key of the individual blood tests. Thus in the simple example given above, the link table would look like this (using the actual names of the entities as opposed to their key numbers; of course, in real life, the keys are used)

Set	Test
A	A1
A	A2
A	A3
B	A1
B	B1
B	B2

Populating the right hand list box is easy: all one needs to do is write an SQL query which returns the tests when the set is A. Populating the left hand list box is less easy and for years I had been using the following method: iterate over a list of all the tests in the test table (ie A1, A2 etc), check whether the current value is in the right hand list box, and if it isn't, then add it to the left hand list box. Whilst this is clearly inefficient, I excused the inefficiency by noting that the list shouldn't contain more than 30 members.

When implementing this dialog box for the n'th time yesterday, it finally dawned on me that I could populate the left hand list box much more efficiently by means of a theta query : return only the values of the blood tests which aren't joined to any blood set test. This is easy to say but slightly difficult to define in SQL -

select items.name, items.id
from items
where not exists
(select 'x' from blood2item
where blood2item.item = items.id
and blood2item.blood = :p1)

'Blood2Item' is the name of the link table, 'blood' is the field holding the blood set key and 'item' is the individual blood test.

Another improvement suggested itself: the dialog box came with code which handles moving an item from one list box to the other -

procedure TDualListDlg.MoveSelected(List: TCustomListBox; Items: TStrings);
var
  I: Integer;
begin
  for I := List.Items.Count - 1 downto 0 do
    if List.Selected[I] then
    begin
      Items.AddObject(List.Items[I], List.Items.Objects[I]);
      List.Items.Delete(I);
    end;
end;

I had never really looked at this code, but yesterday I noticed that it moved an object, not just the name. Several months ago, I wrote about adding objects to a combo box; on the basis of this code I was able to improve my dual list box code when adding to the left hand list box

with qSrcList do
  begin
   params[0].asinteger:= akey;
   open;
   while not eof do
    begin
     srclist.items.addobject (fieldbyname ('name').asstring,
     tobject (fieldbyname ('id').asinteger));
     next
    end;
   close
  end;

Previously, when retrieving the value of an item, I would have to perform a table lookup to establish the value of an item's key from its name, but now the key has already been added to the list box and retrieving it means accessing the item's object value. The scales remaining in dstlist are ones which weren't there before we started, so add them to the 'blood2item' table

 with dstlist do
  for index:= 1 to items.count do
   with qInsert do
    begin
     close;
     parambyname ('p1').asinteger:= gid;
     parambyname ('p2').asinteger:= longint (Items.Objects[index - 1]);
     execsql;
    end;

I realise that this explanation is a bit muddled, but I'm fairly sure that anyone who uses this dual list box dialog will understand. The main thing is that I understand, so now I can improve all the previous uses of this dialog box and improve (once again) the efficiency of my programs.