Thanks to Stephen and Chaim for the advice.
As it turns out (!) it was nowhere as simple as I thought. What is
being selected through the pop-up menu is not data field but a key
field (using the very useful 'Also display values from second field'
and 'Show values only from second field'). This is to follow my rule
that the only foreign data that should be in any table are foreign keys.
So to cut a long story short, I had to create a new table, and update
it every time the other two are changed. As it was, I had to slightly
compromise my rule, but at least it's a field which is set by a
script, not a calculation, so I can use it in a relationship. And I
had to change the primary keys in the original two tables, so that one
is always even, and the other is always odd. That way I could easily
tell which table any record in the third table would relate to.
I don't know if I've explained this very well, but I spent a day and a
half trying four different ways of doing what I needed without having
to create a new table, and finally I gave up, and went for the
compromise which involved the smallest breaking of my rules. This is
the first time in the whole solution I've had to break one of my
rules, which considering it has 49 base tables and 419 table
occurrences (so far), isn't bad going. I hope!
Colm
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
