The common problems with many to many relationships are typically addressed with a join table.
This is referred to as normalization. The first five links here address it.
A Google Search for Join Table on these forums "join table site:forums.filemaker.com"
Thanks David, but it's not really the relationship that is the problem since the formats table acts as a join table: it connects the other two, and the values all display properly on the formats based layout. So I have two portals, and I have the List (Copyright) -- which is a calculated field in the formats table based on the Copyright table.
The issue seems to be with creating a new record in the StatementPortal to set the variable, even though it allows add new records via the relationship. I can manually cut and paste the codes from one to another, and I thought using a variable would be more effective. But the loop gets stuck going to the next line of the portal, then it cycles through all the variables until it reaches the last one at which point it manages to set the field, and moves on to the next record. I can't understand why it does this. Why not just set the first one?
I did have this working, and I don't know what's changed. I was hoping it was something in the script that someone else would spot.
I think you need to put the 'Go To Record/.. [Next; Exit after last]" before the first End Loop
Let's see, you have a many to many relationship between statement and copyright? And formats is the Join table?
That would be: Statement------<Formats>-------Copyright
But then you say that your layout is based on Formats?
But if that's the case any portals to copyright or statement should only show one record and there are no portal rows to loop through.
Thus, it would seem that some part of your system design does not match what I have just described.
I'm sorry, you're quite right, I don't know what I was thinking. Statement is the join file which links formats to copyright. I initially created entries from formats through the following relationships:
Copyright < _____Books_____< Formats_____<Statement
Initially I created entries in Statements by copying the Copyright Key through the formats layout. This brought in the Copyright and Formats keys, but of course only for those entries in Formats. I thought my script would add the multiples from Copyright, but it gets an error 101 at the Set Field stage.
Copyright <------Books------< Formats------<Statement
Also does not show a many to many relationship. If Statement is the join table to link Copyright and Formats, you would need this relationship:
Copyright::__pkCopyrightID = Statement::_fkCopyrightID
Format::__pkFormatID = Statement::_fkFormatID
You can place a portal to Statement on the Copyright layout to list and select a Format record for each given Copyright record. Fields from Format can be included in the Portal to show additional info about each selected Format record and the _fkFormatID field can be set up with a value list for selecting Format records by their ID field.
Sorry, it's a join file in the sense that there's an entry in Statement for every Copyright and every Format, but the records are created elsewhere in the database, and now I need to link them together. The Statement file is basically updated every year to work out annual royalties.
Using Copyright layout would be straightforward if I wanted to create entries in Formats, but I only want to create an entry in Statement that links existing entries in Copyright to existing entries in Formats (so if there were two entries in Copyright and three in Formats, there would be 6 in Statements).
I can do the drop down menu in the Statement portal, showing the relevant Format ID, but I don't know how to go from there to script the creation of entries. I thought the way I devised would work, and I'm not clear why it doesn't. Also, there are added complications from using the copyright layout because I can't include different formats by, say, their publication date in my find. I've played with this for a while and I seem to be going around in circles.
the records are created elsewhere in the database,
Does that mean that you have the relationship I've posted elsewhere in your relationship graph?
but I only want to create an entry in Statement that links existing entries in Copyright to existing entries in Formats (so if there were two entries in Copyright and three in Formats, there would be 6 in Statements).
That would require that each record in Copyright link to each record in formats to produce 6 Statements records. Is that correct?
So if you looped through every record in Copyright, you'd loop through all records in Formats for each copyright record in order to create a record in Statements for each possibly pairing of a record in Copyright to Format?
That can certainly be produced using the relationship that I've posted using a script, but I wouldn't use a portal to do so. I'd use layouts for each of the three tables and have a script that switches back and forth between layouts while creating the needed new records in the Statements table.
Go to Layout ["Copyrights"]
Show All Records
Go to Record/Request/Page [First]
Set Variable [ $CopyID ; Value: Copyrights::__pkCopyrightID ]
Go to Layout ["Formats" ( Formats) ]
Show All Records
Go to Record/Request/Page [First]
Set Variable [$FormatID ; Value: Formats::__pkFormatID ]
Go to Layout ["Statements" (Statements) ]
Set Field [ Statements::_fkCopyrightID ; $CopyID ]
Set Field [Statements::_fkFormatID ; $FormatID ]
Go to Layout ["Formats" (Formats) ]
Go to Record/Request/Page [next ; exit after last ]
Go to Layout ["Copyrights" ( Copyrights ) ]
Go to Record/Request/Page [Next ; Exit after last ]