Value list showing all records instead of only the ones I want.

[Don_Macaroni]

Breaking my brain here... I have a company table and a person table. Records from the company table need to show up in a value list in the person layout, so a company can be selected for a specific person. This is easy and works. Now, what I thought was also easy does not work. I have a field in the company table which makes a company active or not (1 or 0). So what I want is that the value list only shows the active companies (1). I made a global number field in the person table with value 1 and made a new relationship with the company table, linking the 2 fields. Supposedly when they're both 1 there's a link... But not. Please suggest.

[Don_Macaroni]

I knew I wasn't crazy... When I use the exact same relationship in a portal it does exactly what I want. But in a value list it just shows all records, not respecting the variables in the relationship. Very weird.

[Don_Macaroni]

I got it to work via a third (self) relationship, but man... it is SLOOOOOOOW. This can't be right. How come a portal displays the correct values and a value list, displaying data from the exact same relationship, doesn't (it shows all records). This smells like a bug, what am I doing wrong here?

[doughemi]

Did you set the value list definition to show only related records?

[Don_Macaroni]

Yes I did, using a third relationship. And it is dead slow! But that probably has to do that it has 566 records... But why does a portal work and a value list not?(using the same relationship)

[LaRetta]

So what I want is that the value list only shows the active companies (1).

Value lists are different from relationships when you are using a second description field. Are you? I suggest this ... In your Company table, create a calculation called cCompanyID (match data type to your CompanyID) with:

Case ( Status = "Active" ; CompanyID )

Base your 'active companies' value list on Companies table, left pane cCompanyID and right pane of CompanyName and specify all values.

added blue