Presenting Many-To-Many Relationships:
Part 2

by Thomas Ruby

Published 1999-11-16    Printer-friendly version

In the first article in this two-part series I explained some of the background behind many-to-many relationships, and discussed the "Form And Lookup" approach to presenting many-to-many data to the user. In this second part I cover the "Check List" and "Selection Pool" approaches.

NOTE: The example application contains completed procedures. If you want to follow along with the example application, you can either create new procedures with different names, or create a new example application and import demo app procedures as required.

Check List

A check box list presents a list of all the options available and indicates which are selected (See figure 5). I use it when the user thinks of tagging which options apply or don't apply. This is useful when the list of options is fairly restricted, but would get unmanageable if the list contained hundreds or thousands of items. To try this out, make a new menu option on your frame to call a browse of puppies, and make a new form procedure for the browse update buttons. On the new form, populate the puppy name field and a browse box.

Figure 5. A check box list.

In the file schematic for the browse box, select the Trick table and use the edit button to select the TrickName key so the tricks are listed alphabetically. In the list box formatter, add the Trick Name field, and select Normal under icons on the appearance tab. You won't need a range limit here since you want to show all the tricks.

The browse box will need to pick which icon to show in its SetQueueRecord method, and this information is in the DoesTrick table, so go to the procedure's file schematic and add the DoesTrick table under Other Files (See figure 6). You can have the SetQueueRecord method look in the table to see if there is a DoesTrick record for each trick, but in many database environments this would be inefficient. Granted, it probably wouldn't matter in this application, but go ahead and do it the hard way just for fun.

Figure 6. File schematic for a check box list.

Next make a queue to hold the TrickSysID of all the tricks this puppy knows how to do, and refresh the queue when the browse is reset. I built the queue in the data area for the procedure using the data button and gave it a prefix of DQ. (See figure 7)

Figure 7. A queue in the procedure's data.

You will need some code to fill this queue. I put it in a routine:

LoadDoesQueue ROUTINE
   FREE( DoesQueue )
   Access:DoesTrick.ClearKey(DOS:PuppySysIDKey)
   DOS:PuppySysID = PUP:PuppySysID
   SET(DOS:PuppySysIDKey,DOS:PuppySysIDKey)
   LOOP
     IF Access:DoesTrick.Next() <> LEVEL:Benign OR !
     DOS:PuppySysID <> PUP:PuppySysID THEN BREAK .
     DQ:TrickSysID = DOS:TrickSysID
     ADD( DoesQueue )
   END
   SORT( DoesQueue, +DQ:TrickSysID ) 

Put a call to this routine in the Reset method of the window (See figure 8), and in the ResetFromAsk method of the browse.

Figure 8. The ThisWindow.Reset embed point.

You will need to add a variable to the procedure. I called it BrowseIcon and made it a byte. Go to the browse actions tab, and slide the tabs along till you find the Icons tab. Look at the properties for the TRI:TrickName icon. Press Insert to add a condition. The condition will be BrowseIcon = 1, and the icon will be whatever icon you want displayed for selected tricks. If you want to show an icon for unselected tricks, put that icon in for Default icon. Back out to the window formatter.

Next, put some code in the SetQueueRecord embed. Double click the browse box in the window formatter and find the Local Objects|BRW5|SetQueueRecord code embed, and press Insert. Then select Source. The embed code will look like this:

DQ:TrickSysID = TRI:TrickSysID
 GET( DoesQueue, +DQ:TrickSysID )
 IF ERRORCODE() OR DQ:TrickSysID <> TRI:TrickSysID
   BrowseIcon = 0
 ELSE
   BrowseIcon = 1
 END 

You want this code before the generated code so the BrowseIcon variable will be set when the generated code looks at it to pick the icon. Otherwise, the icon will indicate whether the trick above it is selected.

Penelope (remember Penelope?) will want to be able to turn the icons on and off, so put a set of browse update buttons on the form, and fill in a new procedure name. I called it CheckPuppyTrick. You don't want to insert or delete tricks here, just insert or delete DoesTrick records, so hide the insert and delete buttons. You will need to tell the browse template that there aren't any insert and delete buttons, so go to the embed editor and find where the BRW5.InsertControl variable is set (about priority 8505). In the next available embed, put:

BRW5.InsertControl = 0
BRW5.DeleteControl = 0

Now, make the form procedure. I used the form template and selected Trick as the file being updated. List Puppy and DoesTrick under Other Files. You'll need to press the Window button to make a window so the template will be happy, but don't worry about what it looks like because the user will never see it.

Go to the embed tree for this "form;" look at the Local Objects|ThisWindow|Init embed and find where the files are opened. Put an embed after the files are opened which looks like this:

IF SELF.Request <> ChangeRecord
  SELF.Response = RequestCancelled
  RETURN LEVEL:Fatal
END
DOS:PuppySysID = PUP:PuppySysID
DOS:TrickSysID = TRI:TrickSysID
IF Access:DoesTrick.Fetch(DOS:PuppySysIDKey) |
     = LEVEL:Benign
  Relate:DoesTrick.Delete(0)
ELSE
  DOS:PuppySysID = PUP:PuppySysID
  DOS:TrickSysID = TRI:TrickSysID
  Access:DoesTrick.Insert()
END
SELF.Response = RequestCompleted
RETURN LEVEL:Fatal

This embed code does four things. First, it checks that it isn't being asked to insert or delete a record and sets SELF.Response to RequestCancelled so any calling browse will think the user opted to cancel. Second, it checks to see if there is a DoesTrick record for the Puppy and Trick. Third, if there is a DoesTrick record it deletes it, and if not, it adds one. Fourth, it sets SELF.Response to RequestCompleted and returns LEVEL:Fatal.

The RETURN:LevelFatal statement will cause the form to close without ever displaying the window, and since SELF.Respose has been set to RequestCompleted, the Browse box which called it will think the user changed the record and do its ResetFromAsk method to refresh the display.

Notice that this form procedure doesn't care if it is called from a puppy form or from a trick form, so you can use the same procedure from a trick form.

To make check lists, remember to:

• Browse the other table and put an icon.
• Make the icon conditional on some local variable.
• Use the SetQueueRecord method to look in the cross reference table and set the local variable.
• Make a form to add or delete the cross reference records but don't let it open its window.

Selection Pool

A selection pool is useful where a user thinks of picking an option to add. It shows two browses. One browse shows the selected options and the other shows the available options. This is useful when the user thinks about the relationship as "adding" one thing to the other.

Figure 9. A selection pool browse.

Make another option in your app frame menu to call up yet another browse, and make the browse show tricks. Make yet another trick update form, and place a control to edit the trick name. Make the form wide enough for two browses side by side with some space between them for buttons.

On the left side of the form, put a browse to show the DoesTrick table using the TrickSysIDKey key. Have it show the PuppyName out of the Puppy table. Range limit the browse on TrickSysID file related to the Trick table. Set an additional sort field to the puppy name so the puppies will be listed alphabetically.

Put another browse on the right side of the display to browse the puppy table. Yes, I know this table is used in the other browse, but don't worry, it will work fine (See Figure 10). Have it use the name key so the puppies will be listed alphabetically. You'll want to know the class names for these two browses, so look them up on their actions tabs and write them down or make them something meaningful. Mine are BRW5 and BRW6.

Figure 10. File schematic for a double browse form.

Now, look at the procedure's embed tree and go to Procedure Routines. Build yourself two routines. I usually put them in two different embeds so they both show in the embed tree. They should look like this:

AddPuppy ROUTINE
   IF CHOICE(BRW6.ListControl)
       BRW6.UpdateBuffer()
       DOS:PuppySysID = PUP:PuppySysID
       DOS:TrickSysID = TRI:TrickSysID
       Access:DoesTrick.TryInsert()
       ThisWindow.Reset(1)
   END

RemovePuppy ROUTINE
   BRW5.UpdateBuffer()
   IF Access:DoesTrick.Fetch(DOS:DoesSysIDKey) |
         = LEVEL:Benign
      Relate:DoesTrick.Delete(0)
      ThisWindow.Reset(1)
   END

Take a close look at these two routines because they do the work.

AddPuppy first checks to see if a puppy is selected in BRW6. Second, it calls BRW6.UpdateBuffer so the puppy record will contain the selected puppy. Third, it builds a DoesTrick record and inserts it. The routine uses TryInsert instead of Insert so if the user mistakenly adds a puppy twice the program will appear to do nothing rather than showing a strange looking error message. Fourth, it resets the window.

RemovePuppy does an UpdateBuffer on BRW5 so it knows which puppy it is removing. The browse is listing DoesTrick records, so BRW5.UpdateBuffer() will get the DoesTrickSysID into the DoesTrick record. It then fetches the DoesTrick record by the SysIDKey. Then it deletes the record and resets the window.

Now that you have these two routines, put DO AddPuppy in the embeds for the Add button (Control Events|?Button3|Accepted ). Put DO RemovePuppy in the embeds for the Remove button.

Penelope can now add and remove puppies to her heart's content using these buttons. Since she's "moving" records from one list to another, this would be a natural place to use Drag and Drop, so go ahead and add drag and drop IDs to the list boxes. On the left side, the drag ID will be RemovePuppy and the drop ID will be AddPuppy. On the right side, do just the opposite and make the drag ID AddPuppy and the drop ID RemovePuppy. Double Click the browse on the left to get the embed tree and go to Control Events|?List|Drop, and add DO AddPuppy there. Double Click the browse on the right and go to Control Events|?List2|Drop, and add DO RemovePuppy there.

You may want to restrict the right side browse to show only the puppies that are absent from the left side browse. I haven't figured out how to filter a view based on records that aren't there, so I built a queue in the procedure data to store the SysID's of puppies that do the trick and used the browse's verify record embed to check it. Performance shouldn't be too terrible unless there are 200,000 puppies and only two are included in the list. I gave the queue a prefix of PQ. An embed in ThisWindow.Reset() method fills this queue:

FREE(PuppyQueue)
 Access:DoesTrick.ClearKey(DOS:TrickSysIDKey)
 DOS:TrickSysID = TRI:TrickSysID
 SET(DOS:TrickSysIDKey,DOS:TrickSysIDKey)
 LOOP
   IF Access:DoesTrick.Next() <> LEVEL:Benign THEN BREAK .
   IF DOS:TrickSysID <> TRI:TrickSysID THEN BREAK .
   PQ:SysID = DOS:PuppySysID
   ADD(PuppyQueue)
 END
 SORT(PuppyQueue,+PQ:SysID) 

The embed to filter the browse looks like this:

PQ:SysID = PUP:PuppySysID
 GET(PuppyQueue,+PQ:SysID)
 IF ERRORCODE() OR PQ:SysID <> PUP:PuppySysID |
      THEN RETURN 0 .
 RETURN 2 

The code returns 0 if the puppy sys ID is not in the queue, and a 2 if it is. Be careful! If you return a 1, the browse class will decide this record is out of range and will not look any farther.

To show a selection pool, remember to:

• Browse the cross reference table displaying identifying fields from the other table.
• Browse the other table.
• Make Add and Remove routines to build and remove the cross reference records. These should reset the window when they're done.
• Make controls and/or drag & drop to call the Add and Remove routines.

Summary

There are many ways to present many-to-many relationships to the user, and if you're clever you can hide the cross reference from the user. The trick is to figure out how the user envisions the task, and make an interface accordingly. I hope you find these three approaches useful.

Downoad the example application

---

Tom Ruby, who is no relation to the man who shot Lee Harvey Oswald, is an independent contractor living in the middle of a hayfield in Central Illinois with his wife Susan and two red-headed sons, Caleb and Ethan. He has been using Clarion for Windows since the summer of '95. Before that, he was a "TopSpeeder" using Modula II, so he has never used the DOS versions of Clarion.

Printer-friendly version

Copyright © 1999-2008 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942