Join Value Lists

[Peter1]

I have a feeling this just ain't going to be possible, but you never know.

I want to join 2 (or more) value lists together. In a typical situation I want to create a value list of Consultant IDs with "All Consultants" at the top, and preferable a divider. For example:

All Consultants

-------

REP001 Cameron

REP004 Sandra

REP005 Angelina

The name is from a second field.

I know of some very "unelegant" ways to achieve this, so currently I have to create custom lists (which need to be updated for rep changes) and then extract the RepID from that. Seems very amateurish. Any ideas? Any plug-ins?

Thanks - Peter

[Brian C]

When I ran into a limit of 50 max files open a long time ago in Filemaker 6, I decided to begin combining many of my small value lists which were stored in multiple files into single files and using relationships to grab the specific value lists I needed.

I accomplished this by having a each value list have its own uniquely named fields.

Example:

All in the same Table:

---------------

Colors Fields:

Colors_Rel_ID, Colors_ID, Colors_Desc

Shapes Fields:

Shapes_Rel_ID, Shapes_ID, Shapes_Desc

----------------

The Rel_ID fields would always be a stored calculation which was equal to 1.

All of my tables needing to access any value lists would have a single unstored calculation field that was always equal to 1.

Using the relationships I could then create a relationship from my database to the table to pull up that particular value list. I would then make sure my value list used this relationship to bring up the particular valid entries.

Using this concept you can create a set of custom calculation fields to bring across the values and IDs of the entries that you want so that you can essentially combine the content of 2 or more separate value lists.

These calculation fields can be set to be equal to "-" for your separaters if the descriptions on either side of the particular record are empty.

To go a little furter you can add a separate alternate description field to input your "All Consultants", and by using a case statement, have it show correctly in your value list.

Clear as mud? If you have any questions on clairifications, just ask

[Brian C]

Note: If you do not want one of your valid selections to be a blank value, you will need to have the Rel_ID fields be user input instead of a stored calc =1. This will prevent records which belong to other value lists that have no entries in the specific fields for a given value list from having their result displayed.

Having a value list entry = to an empty field can have its uses however if the resulting description is "BLANK". It just depends on what you are trying to accomplish.