The table structure you need will work the same as you would with SQL: Writers, Magazines, Writer_Magazine
Writers::WriterID = Writer_Magazine::WriterID (Enable creation of related records on Writer_Magazine)
Magazine::MagazineID = Writer_MagazineID (Enable creation of related records on Writer_Magazine)
If you place a portal to Writer_Magazine on a Writers layout, you can format the MagazineID field as a drop down for selecting a magazine and thus use this portal to link a the current writer record to an existing magazine record. You can include fields from Magazines in this portal to make it more informative to the user.
If you place a portal to Writer_Magazine on a Magazine layout, you can format the WriterID field as a drop down for selecting a writer and thus use this portal to link the current magazine record to an existing writer record. You can include fields from Writers in the portal to make it more informative.
If desired, you can use conditional value lists in your drop downs to list only relevant subsets of the total list of writers or total list of magazines.
Here's a demo file: http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html
Here's a thread on setting up conditional value lists: Custom Value List?
Thanks for the direction, Phil.
I think the only part that I'm missing to complete the loop is creating a conditional list based on user input, because I'm in a situation where I could easily have a thousand records on either side of the relationship. I thought I could see some possible routes to achieve this but they don't seem to be working out. Could you advise on what the preferred FMP way for this would be? I feel like I've nearly got it several times but it just slips away before I figure it out.
(If I'd been writing this app for my client in PHP & MySQL I'd have been finished by March! Ack!!)
I put a link to a thread on conditional value lists in my last post. Did you read it?
I did, but it didn't seem to address the part I asked for clarification on. Or if it did, not in a way I recognised as answering it. I need to base the condition specifically on user input - that's the bit I can't understand at the moment: how I can link user input with the creation of a conditional list. Couldn't find anything useful with searching for "conditional list" and "user input" either. :/
Having allowed everything I read yesterday to stew overnight, I think I'm going to be trying to route of a global field for user entry, and a relationship (for the list) based on a calculation field containing a concatenation of everything I want to search on in the destination table matching "*global_field*" from the source table - though I'm not sure what the syntax is for that. Does this sound like the right approach?
The tutorial that I posted a link to describes an approach (Option 2) that limits the values based on user input.
The user makes a selection in field 1 and that value "filters" the list of values for the value list in field 2 to just those values that fall in that category selected in the first field. That appears to be what you need here.
It might help to spell out exactly the situation you are dealing with here. Then I can suggest an approach using elements from your example to show you how it works.
In the meantime, heres a demo file with multiple level conditional value lists you may want to look over as a way to see how it works: http://www.4shared.com/file/gd6_fDgn/conditional_value_listDec.html