Figure 1: Client list -- Your starting point for this exercise.


Figure 2: Primary relationship -- This is a filtering system, so you select a value (or values) in the global fields and that will display all matching records from your index value.


Figure 3: Index values -- The alpha listings in the file. As new records are added with different letters of the alphabet so the alpha listing will increase.


Figure 4: Results for checked index letters -- All matching records are now shown waiting for the user to make a selection or selections.


Figure 5: Complete relationship graph -- The complete relationship structure required for this technique to work.


Figure 6: Matching_Clients relationship -- Notice the non-equality between G_ClientID and ClientID.

For that article and technique, I used a set of conditional value lists cascading downward.

In this article, I'll discuss another use of conditional value lists and show you a technique that's powerful but simple to set up and use. For this example, say you have a large client list (figure 1) you want to be able to filter down and select just a few (or even many) clients.

The index field is simply a calculated field that provides the first letter of each name. The calculation:

left(name,1),

... is a text value and a stored calculation. You store it so you can use it in the destination side of your relationship.

In the home table (which you use to store the non-indexed side of the relationships), you have a global field called G_Index set to display all the values from the Index field as checkboxes. Figure 2 shows the relationship.

You set the G_Index field to display values from the recordset index field so the user only sees letters for which there are matching records (figure 3 and 4). If you used all the letters of the alphabet and numbers from 0-9, there would be many selections for which there would be no matching records and that could cause confusion.

So far, I've shown you nothing out of the ordinary, but now it gets fun. First add another global (text) field to the Home table and call it G_ClientID. You'll use this field in the relationships called Matching_Clients and Selected_Clients. In the Selected_Clients relationship, you're simply linking G_ClientID to ClientID (figure 5).

In the Matching_Clients relationship (figure 6), you'll see that Index = G_Index and ClientID ? (not equal to) G_ClientID.

You might be wondering why I'm doing things this way, but bear with me and it will make sense shortly!