Multiple Lookup Fields
I've inherited a db containing data about books. Amongst many other fields, each book has a Subect Field, which is linked via a relationship to a so-called BISAC code. When the user enters the subject (in English) the system automatically fills in the related BISAC code.
So far, so good.
What they've asked me to do is to allow up to three codes. They're happy for me just to triplicate the existing pair of subject-code fields and I'm happy not to get involved in portals if I don't have to.
Which leads me to my question. I can, of course, create three relationships:
Title::Subject_1 <=> BISAC_Codes::Subject
Title::Subject_2 <=> BISAC_Codes::Subject
Title::Subject_3 <=> BISAC_Codes::Subject
but I don't see how the system would know which relationship to use when calculating the values of BISAC_1, BISAC_2 and BISAC_3, so I doubt this would work.
I had a look at the Lookup function but that only lets me look up via a relationship so would suffer the same problem as the previous paragraph.
What I really want is something like a DLookUp in Access, or a VLookUP in Excel - I can supply a table name and the value of one field and extract the equivalent value from another field.
I'm sure this is simpler than I'm making it, but I can't see how to do this in FM. I'm using version 9 if that makes a difference.
Thanks in advance