Ugh, I'm completely stuck on this value list problem. I have a value list which is in a file that acts as the middle of a many-to-many relationship. I want it to show only values which have not previously been chosen for one of the related files.

Here's an example:

Table 1 : Projects

Table 2 : Employees

Table 3, many-to-many : Assignments (Choose both a project and an employee match)

In Table 3, I want to create a value list which ONLY shows Projects that have not yet been chosen. It's a usability thing - so that the client doesn't have to scroll through a whole bunch of already assigned projects, but will just see those that haven't yet been chosen and are 'open.'

What a mess. I'll be really grateful to hear any ideas anybody else has on this...

Thanks so much,

Syd

I'll assume you are linking to Projects with a project number field in your join file. Create a field in the join file:

Unassigned (calculation, number, indexed, uncheck "do no evaluate if all referenced fields are empty) = IsEmpty(Project Number)

Create a field in the file in which you want to display the open assignments:

One (calculation, number, indexed) = 1

Create a relationship with One matching Unassigned.

-bd

Aha! I knew there must be a way. Thanks so much!