I want to create a value list that exlcudes part of a field. For instance, the value list may be based on the employee field but only those employees who are active.

In another case, I want to value list to be a combination of fields from 2 different tables. How do I do this?

abe nash wrote:I want to create a value list that excludes part of a field. For instance, the value list may be based on the employee field but only those employees who are active.

You'll start by creating a relationship that is only true for active employees. This could even be a self-relationship between two occurrences of the same table. One way to do this is to create a Global Calculated field where the calculation equals 1; that field would be the left-hand side of the relationship. The right side would be a calculation that returns a 1 if the employee is active.

Once that relationship is in place you'll see that the Define Value list dialog has a couple options you can make use of. When you select "use values from a field" you'll see an option to "use only related values" wherein you can specify the relationship you just created.

abe nash wrote:In another case, I want to value list to be a combination of fields from 2 different tables. How do I do this?

The trouble is that value list members need to be indexable, so you can't just calc stuff into as value list from anther file. You can, however, select your "second field" from a table that is different from the table used for the first field.

If that doesn't get you there, you may want to select your values from a portal instead of a value list. There are a ton of advantages to this, though saving screen real-estate obviously isn't one of them. I love replacing value lists with portals, so just a few of the advantages...

portals can show unstored fields
you can control their sort order
they persist in a scrolled state
portals can contain colors and/or graphics
you can dim inappropriate selections instead of removing them
you can easily run scripts when an item is selected

Pardon the rant.