Narrowing List of Related Records

[AndrewPurvis]

I have a database in which data could be in one of two places. This tracks games between two Magic players, and Games::FirstDeckName holds the name of the deck that pays first, with Games::SecondDeckName holding the other deck. I have a layout that presents the user with a means of selecting any deck that has played any number of games greater than 0 (Matchups::Friend), then selecting from a list of only those decks that participated in those games (Matchups::Foe). It then shows their records against one another and summaries of the games, with the summaries acting as links back to the related games.

That works great. However, the second menu uses a value list constructed by a relationship that has a child key field set to the names of both decks (carriage-return-delimited). If a (Friend) deck has played against five other decks, when I go to Matchups::Foe, I get, of course, six results: the five decks the Friend has played against AND the Friend deck itself. What I want to do is have a conditional value list that only includes values not equal to the primary key (Matchups:Friend).

All I can think to do at this point is use a Script Trigger (triggered on change) on the Matchups:Friend field to set a field that has the related values, though with the current Friend value removed with a Replace function, done in such a way as to eliminate any unsightly extra carriage returns. Then I could use that field as the value list (it can be, but need not be, Global, since there is never any cause to have more than a single record in the table).

Is there a programmatic solution that avoids writing a script, attaching a script trigger and creating a new field?

[mr_vodka]

Can you either post a sample file or at least your relationship graph?   From what you have written to far, you may have a structural issue.  You may be able to solve your issue by changing your structure.