6 Replies Latest reply on Jul 31, 2014 8:13 AM by AmberDavis

    Creating a Loop for a portal

    AmberDavis

      Title

      Creating a Loop for a portal

      Post

           I have this portal that has many portal rows with information. In a script (Update HandShop) I created it pulls the information from the portal row and updates the database. The problem is when I press the button with script (Update HandShop) It will only update the last portal row. And the Units Out box will only go to the last item.

           Example:

           (Portal)

           Item: Item A    Qty Out: 1

           Item: Item B    Qty Out: 1

           Item: Item C    Qty Out: 1

           (Result)

           Item C:  Qty Remaining: -3

           (Wanted Result)

           Item: Item A    Qty Remaining: -1

           Item: Item B    Qty Remaining: -1

           Item: Item C    Qty Remaining: -1

            

      Screen_Shot_2014-07-29_at_4.36.00_PM.png

        • 1. Re: Creating a Loop for a portal
          nihmbrisby

               Not 100% sure I understand your scenario, but fwiw the Go to Portal Row [next] with the "exit after last" option checked handles looping through portal rows to 'get' or 'affect' every row:

               http://www.filemaker.com/help/13/fmp/en/html/scripts_ref1.36.23.html

          • 2. Re: Creating a Loop for a portal
            philmodjunk
                 

                      Go to Portal Row [next] with the "exit after last" option checked handles looping through portal rows

                 But there are several "gotchas" to look out for when using that script step. If "allow creation of records via this relationship" is enabled, you have one more portal row than you have records. And go to portal row doesn't specify which portal on your layout to interact with, it will act on whatever portal currently has the focus. If you have more than one portal on the layout, you need to give the portal an object name in the inspector and use go to object to put the focus on the correct portal before executing go to portal row.

                 And note that future design changes to your layout--such as adding another portal or changing an object name can break your script without any warning that it is broken until you run the script.

                 Thus, I'd use one of two methods whenever there is a need to loop through portal records:

                 a) Use Go To Related Records to pull up a found set of these portal records on a layout based on the portal table. Loop through the records on this layout instead of the portal rows. If the portal is filtered, use constrain found set to first reduce the found set to just those shown in the portal.

                 Main draw backs: This method can trip a very large number of script triggers on each of the two layouts involved. You may need to use a global variable to keep such trigger performed scripts from doing anything while this script is changing to and from layouts. And the portal will "snap back" to the first portal row after you return to the original layout.

                 b) Use List () or ExecuteSQL() to set one or more variables to a return separated list of values from the portal. Then loop through the values using getValue ( $Variable ; $LoopCounter ) to access each value (or set of values) in turn. This avoids any need to change layouts and may avoid any focus changes that make the portal snap back.

                 Main drawback: This is good for "reading" data from the portal table, but not as straightforward as "writing" data to the portal table, though this can be done without changing layouts with some creative use of relationships and table occurrences. And if the portal is filtered, you have to use an SQL expression in ExecuteSQL that replicates the filtered portal results in order to get the correct list of records and ExecuteSQL is one of the most difficult functions to use in current versions of FileMaker.

            • 3. Re: Creating a Loop for a portal
              nihmbrisby

                   Thanks Phil.  A question- if you can't create new records via the portal's relationship and you precede the portal navigation steps with a go to object step- then do you still have reservations about looping through portal rows?  I only ask because I have found it really convenient and use it a bunch.

              • 4. Re: Creating a Loop for a portal
                philmodjunk

                     I would still avoid this option if I can. To repeat from my last post:

                     

                          And note that future design changes to your layout--such as adding another portal or changing an object name can break your script without any warning that it is broken until you run the script.

                     I once was called in to evaluate and suggest improvements for a database created by an in house developer. He wasn't happy with me when I described his design as "brittle". I called it that because any number of simple layout design changes would cause his scripts to fail without warning short of running the script and not getting the expected results.

                     Using Go to portal row and looping through the rows is one such example of "brittle" script design so I avoid it if possible.

                     Please note that this is a relative situation. Current versions of FileMaker are all very "layout sensitive" and you cannot fully avoid this type of vulnerability, but I reduce this where I can.

                • 5. Re: Creating a Loop for a portal
                  nihmbrisby

                       Thanks.

                  • 6. Re: Creating a Loop for a portal
                    AmberDavis

                         Thank you Phil! And I will be referencing your other post for global variables help  http://forums.filemaker.com/posts/19d0a6c546 Very helpful, Thank you!

                         Keep on keeping on!