8 Replies Latest reply on Dec 20, 2013 8:28 AM by philmodjunk

    portal loop in a loop

    vmtc

      Title

      portal loop in a loop

      Post

           I'm trying to create related records via a portal loop, but can't get the set field to work properly. My layout is based on a table called Formats, that connects two other tables (Copyright and Statement) which have a many-to-many relationship. I've created a List of the Copyright values in Formats, which give the values to be set in the Statement Portal.

           What happens is that it cycles through the loop, but only sets one field, and that is with the last value. Curiously, when the first record is already in the Statement Portal,  even though I've not bothered taking this into account with the script, it seems to skip over it. I say this because if there are 7 records in total, you end up with two portal values, being the first and the 7th, but not the ones in between. When I take the first value away, I end up with one portal row, with the last value in it. 

           Here are the steps I am using:

           Loop

           Set Variable ($RowCount; Value: Formats:CountCopyS (count of fields from copyright portal, ie rows to be copied)

           Set Variable ($row; Value: 0)

           Go to Object [Object Name: "StatementPortal"]

           Go to Portal Row [First]

           Loop

           Set Variable [$row; Value: $row +1]

           Set Variable [$CopyS; Value: GetValue (Formats:List Copyright; $row)]

           Set Field [Statement:CopyrightS; $CopyS]

           Exit Loop If [$row = $RowCount]

           Go to Portal Row [Next]

           End Loop

           Go To Record/.. [Next; Exit after last]

           End loop

            

           Any help would be much appreciated. I'm using Filemaker 10Adv

        • 1. Re: portal loop in a loop
          davidanders

               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.

          http://forums.filemaker.com/posts/f6ed4be796?commentId=222931#222931

               A Google Search for Join Table on these forums "join table site:forums.filemaker.com"

          https://www.google.com/search?q=join+table+site%3Aforums.filemaker.com

          • 2. Re: portal loop in a loop
            vmtc

                 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.

                  

            • 3. Re: portal loop in a loop
              SteveMartino

                   I think you need to put the 'Go To Record/.. [Next; Exit after last]"  before the first End Loop

              • 4. Re: portal loop in a loop
                philmodjunk

                     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.

                • 5. Re: portal loop in a loop
                  vmtc

                       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. 

                  • 6. Re: portal loop in a loop
                    philmodjunk

                    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-----<Statement>-----Format

                         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.

                    • 7. Re: portal loop in a loop
                      vmtc

                           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.

                      • 8. Re: portal loop in a loop
                        philmodjunk
                             

                                  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]
                             Loop
                                Set Variable [ $CopyID ; Value: Copyrights::__pkCopyrightID ]
                                Go to Layout ["Formats" ( Formats) ]
                                Show All Records
                                Go to Record/Request/Page [First]
                                Loop
                                   Set Variable [$FormatID ; Value: Formats::__pkFormatID ]
                                   Go to Layout ["Statements" (Statements) ]
                                   New Record/Request
                                   Set Field [ Statements::_fkCopyrightID ; $CopyID ]
                                   Set Field [Statements::_fkFormatID ; $FormatID ]
                                   Go to Layout ["Formats" (Formats) ]
                                   Go to Record/Request/Page [next ; exit after last ]
                                End Loop
                                Go to Layout ["Copyrights" ( Copyrights ) ]
                                Go to Record/Request/Page [Next ; Exit after last ]
                             End Loop