Let's start with this:
Title::Subject_1 <=> BISAC_Codes::Subject
Title::Subject_2 <=> BISAC_Codes::Subject
Title::Subject_3 <=> BISAC_Codes::Subject
This has some possible problems that could just be the way you typed it up or could be a real issue if you tried to set it up this way in Manage | database | Relationships.
If you did it this way, it will work:
Title::Subject_1 = BISAC_Codes::Subject
Title::Subject_2 = BISAC_Codes 2::Subject
Title::Subject_3 = BISAC_Codes 3::Subject
You'd need 3 table occurrences of BISAC_Codes. In Manage | Database | relationships, make a new table occurrence of BISAC_Codes by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Repeat these steps to get the third ocurrence.
Then you can set up 3 BISAC_Code fields--each with a looked up value setting that references a different occurrence of BISAC_Codes.
But what do you do if they then decide they want 4, 5 or even more subject/code pairings? Will you keep modifying the design of your databse to add more fields and more relationships?
What you have here is a many to many relationship and if you set up a join table between Title and BISAC_Codes, you could link a given record in Title to as many BISAC Codes as you need and not need to do any further redesigns of your database:
Title::TitleID = Title_BISAC_Code::TitleID
BISAC_Codes::Subject = Title_BISAC_Code::Subject
But this WILL require using portals to implement. A portal to Title_BISAC_Code on a Titles layout could list all the BISAC Codes for that title and a portal to Title_BISAC_Code on a BISAC_Codes layout could list all Titles linked to that subject.
I know that you said:
I'm happy not to get involved in portals if I don't have to.
But designing database solutions in FileMaker really can't get very far without portals. Can't remember the last time I created more than a simple test file that didn't have at least one and usually I need a bunch of 'em to get the job done.
Many thanks, Phil. You explained to me once before how to duplicate a table instance on the relationships form but I'd forgotten that they ended up with different names so, of course, they can be referred to separately.
I know that setting up a proper many-to-many relationship is the way to go but the database is in such a mess, it hardly seems worth the hassle. For example, there are 4 sets of fields for authors, each set of which has first, last and full name. It seems a little late in the day to be trying to impose order on the thing. There isn't a single portal on any of its 100 or so layouts.
At some point, they may ask me to tidy the whole thing up (start again, essentially) and then I'd be happy to do it properly. I've developed a completely new database for another company in the same group (with a lot of help from this forum) and I'd like to think that it's a far better design with portals all over the place!
It seems a little late in the day to be trying to impose order on the thing. There isn't a single portal on any of its 100 or so layouts.
I'd consider an incremental approach to cleaning up the structural issues with frequent testing and pausing to let the users get used to each change as it becomes a reality. If you can get their support to do that, you can gradually "grow" their system into something that's got a decent design.