I want to define a value list in file A that contains items from field X (in the same file) for records with matching field Y. The trick is that I want to exclude the current record's X value (X values are unique) when I'm looking at the value list. Thus, I'm looking for a dynamic value list that changes based on the current record that I'm browsing.

For example:

A1, X=1, Y=1

A2, X=2, Y=1

A3, X=3, Y=1

A4, X=4, Y=2

A5, X=5, Y=3

For record A1, I want a value list of "2|3"

For record A2, I want the value list to change to "1|3"

For record A3, we'd have "1|2"

Is this even possible?

My initial thoughts were to relate via a self-join based on Y:Y, giving a subset of A's records with the same Y value. Using this, I get very close but this is just one step away from what I need and I'm stumped on how to modify it.

Any ideas?

Check out Fenton's reply to my recent, similar, question:

http://www.fmforums.com/threads/showflat.php?Cat=&Board=UBB17&Number=85654&fpart=&PHPSESSID=

Awesome! That was exactly what I was looking for!

Thanks again!