I've designed several membership applications for customers that have wanted to assign multiple values to each of their members.  For example, one member might participate in several activities, or have several skills.  

One possible solution would be to create a set with a one-many link to a child table that would keep track of these values.  However, these values could not be easily searched (flattenquery¹ will work, but I felt that it was more complicated than my customers would like).  So, I came up with another solution that has worked quite well, is very simple for the user to search, and is pretty easy to implement.

Here is a little background.  Each membership application has a set with MEMBERS as the parent table, and TRANS, in which the members' transactions are recorded, as the one-many child.  In MEMBERS, I included a field (CODE) that is long enough for 10-15 codes (2-4 characters per code).  The codes come from a lookup table called CODES.

However, I had several problems to solve:

1.	First, I wanted this to be as simple to use as a normal table lookup.  However, a normal lookup will not work for a field in which you want to store more than one value.  Therefore, I had to figure out how to allow many values in one field, but make sure each value exists in the lookup table, and no other values are allowed in the field.
2.	Next, I had to come up with a way to allow the user to search for records with more than one code, realizing that they might not have put the codes in the field in the same order.
3.	Finally, I wanted to display the field so that the user could easily see which codes were present.

I started out by writing a script that is called from a button on the form.  This script checks the field to see if any codes in the field are not valid, and warns the user if one is found.  Next, it creates a list of valid codes from which the user can select.  Finally, it inserts the code at the end of the field.  Click here to see the Lookup script.

A few notes about the script:

1.	In the first few lines of the script, I defined nm as the suffix for the message (i.e., nm = "st" for 1st).  This isn't necessary for the function of the script, but it makes the message a little nicer.
2.	I put the codes in MEM_SUB, which is referenced in the FOR...NEXT statement.  Note that if an invalid code is found, the user is warned, and the script ends.  This prevents a new code from being entered if an invalid code exists (which could have happened prior to adding this script).
3.	Under CREATE LIST FOR ARRAY, I created an array using MEM_SUB.  Note that the sort order is such that any blank codes (from the SUBCODE field) end up at the end of the array.
4.	Under APPEND SELECTED CODE, the script checks to see if the user is in Enter or Change mode, retrieves the current value from the current field (SUBGROUP), adds the new code to the end, and saves the record.

The next step was to provide the user with a way to search for more than one code in the field, regardless of the order in which they were entered.  This required another script attached to a button on a form designed specifically for the data-entry form's Query by Form.  The design of the form was quite simple -- I saved the data-entry form as a new name (QUERY_MEMBERS), and then made a few changes, including the new button.  Click here to see the Search script.

You'll note that this script is very similar, in that it creates an array from the same lookup table (MEM_SUB).  The main difference is that it places the result in the CODE field as an expression "X"$CODE, where X is the code selected from the list.  If the user selects more than one code, the script appends the new code to the end of the string with an .AND. separating the two.  This tells the program to find all records that have all the codes selected by the user.

¹ Please see Jim Chapman's article, Guaranteed 1-Hour Guide To Becoming A Query Filter Expert, in last month's (April 1) newsletter.


[PRINTER FRIENDLY VERSION]