1 2 Previous Next 17 Replies Latest reply on Aug 6, 2017 11:27 PM by Malcolm

    Loop thru Portal records

    flamecoder

      In my script I want to make changes to all related records shown in a portal. Is there an easy way to make the change or do I have to grab the primary key, go to other table, do a search with the foreign key then loop thru them making the change?

      If I try to just set a field without looping it's only going to change the first record in portal.

        • 1. Re: Loop thru Portal records
          DanielShanahan

          You can loop through the portal records.  Use the Go to Portal Row [First] like you would Go to Record [First]

           

          Go to Portal Row [First]

          Loop

           

              Do something inside the loop

           

          Go to Portal Row [Next; Exit after last]

          End Loop

           

          You may already have given your portal object a name but just in case you didn't, its a good idea, especially if you have more than one portal on the layout.  Then you would Go to Object [] before going to the first portal row.

          • 2. Re: Loop thru Portal records
            MaxEh

            If the portal already displays all the records you want (it is not filtered in any way) wouldn't clicking into the field you want to change and using the "Replace Field Contents" menu item be easier?

             

            A script could be used to make sure you are in the correct field which I think would have to be on the portal, could ask for the updated information to use as the replacement, set the field and then do the replace.

             

            Martin

            • 3. Re: Loop thru Portal records
              BruceRobertson

              Note that replace works on the found set.

              So you might be thinking about the particular record + child records you are looking at. But if other records are in the found set - they will be updated too.

              • 4. Re: Loop thru Portal records
                BruceRobertson

                I don't like walking portals myself. But let's step back a bit - what are you trying to achieve? What do these records represent and why do you need to modify them? Is this in support of a particular business process?

                • 5. Re: Loop thru Portal records
                  nicolai

                  flamecoder

                  I have to grab the primary key, go to other table, do a search with the foreign key then loop thru them making the change?

                   

                  This is one way of doing this, the simpler is to Go to Related Record script step and use Replace.

                   

                  Daniel's suggestion will work, but if you have more than one portal you need to Go to Field first and specify a field from the portal

                  • 6. Re: Loop thru Portal records
                    siplus

                    is your portal filtered ?

                     

                    If not, nicolai is right.

                    • 7. Re: Loop thru Portal records
                      realgrouchy

                      To reiterate Bruce Robrtson's warning, don't use MaxEh's suggestion. Using the "Replace All" in a related field in a portal will change that field for all related records in the portal for the current parent record, as well as for all related records in the same portal for any other records you have open in the current found set.

                       

                      For example, if you have a database of employees, and within each employee's record you have a portal with an entry that has their address on Jan 1 of each year, and you realized Phil's address was the same each year but said "123 FileMaker St" instead of "123 FileMaker Ave", if you try to replace the address using "replace all" in the address portal in Phil's record, all the addresses will be changed to "123 FileMaker Ave" for all years for all employees you have open (If you only have Phil's record open, this would work as desired without affecting the other records).

                       

                      Use one of the other suggested options instead.

                       

                      - RG>

                      • 8. Re: Loop thru Portal records
                        MaxEh

                        The Replace Field Contents is a powerful command. Caveat Empor or the equivalent User Beware (I'll defer to Siplus here). Mea Culpa - I should have been more careful in my reply - Bruce asked the questions I should have. I do something similar now and it has been working well for me with how I have my relationships set up etc., typically only one parent record and scripted for Admin users only (with a built in Undo system).


                        But then who knows perhaps you do want to change the data for the found set and it is the same for all of them - wrong spelling of the address' state?

                        • 9. Re: Loop thru Portal records
                          Malcolm

                          MaxEh wrote:

                           

                          But then who knows perhaps you do want to change the data for the found set and it is the same for all of them - wrong spelling of the address' state?

                           

                          Hi MaxEh,

                           

                          I have to chime in and beat up on you too. The key to safe programming is to do only what is required. You don't want to do less than required nor do you want to do more.

                          • 10. Re: Loop thru Portal records
                            DanielShanahan

                            There are a couple of things I like about looping through the portal:

                             

                            1. I don't have to change context.  Although changing windows and running a procedure is often so fast the user never knows it, sometimes I don't want to change the context (e.g. slow WAN).

                             

                            2. I don't have to open a new window.  Similar to changing context, sometimes when running a process on another table I do it in a new window.  However, if using WebDirect, then I may want to reduce (or eliminate) New Windows.

                             

                            3. Looping works on the displayed found set.  If the portal is filtered, the loop still works on the displayed set, unlike GTRR.

                             

                            As I mentioned in my original reply, I name portal objects and first use Go To Object [p_InvoiceLines] before looping through.  Also, I use the other methods as well (GTRR, new window, etc.).  So I'm NOT making a stand that this is the best method.

                            • 11. Re: Loop thru Portal records
                              flamecoder

                              Thanks for all the answers!!! My database is tracking projects. I have a main record describing the project and I have tasks related to the project. The main record has project statuses, two of which are Hold & Closed. When I click on Hold I must also loop thru the related portal tasks and put them on Hold so they are now longer counted in the backlog. Also when Closed is clicked I need to loop thru the portal tasks and make sure all the tasks have a status of Checked Out and alert user if one is still open. I'm just making a one click operation to verify or change several records. The Go To Portal Row script step works for my case very well compared to the many steps I was doing to go to another layout & doing a search.

                              • 12. Re: Loop thru Portal records
                                Malcolm

                                They are three strong points.

                                 

                                Changing context does mean loading a new record set into memory. Engineers say that the load is the visible record set plus twenty five. If your related record set is less than twenty five then switching to the RR context may be a disadvantage. The windowing is also a drain on resources and needs to be controlled.

                                 

                                You could add to that that working within a portal brings you closer to a transactional model which can be advantageous.

                                • 13. Re: Loop thru Portal records
                                  DanielShanahan

                                  Thanks Malcolm.

                                   

                                  I don't have the under-the-hood knowledge that you do, so your post was helpful and informative for me.

                                   

                                  One thing I failed to mention in my original reply to the OP is that looping through the portal is slightly different, depending on whether new records can be added via the portal.  If "Allow creation of records..." is checked in the relationship graph, then Go to Portal Row [Next; Exit after last] won't work.  It just creates new records in an infinite loop.  Using something like Exit Loop If [isEmpty ( INVOICELINE::ID)] will work.

                                  2 of 2 people found this helpful
                                  • 14. Re: Loop thru Portal records
                                    Malcolm

                                    I use this format to walk through found sets and portals.

                                     

                                    set variable [ $i ; 0 ]

                                    set variable [ $n ; (count of records/rows)]

                                    loop

                                        exit loop if [ let ( $i = $i + 1 ; $i > $n ) ]

                                        go to record / portal row [ by calculation ; $i ]

                                        -- do something

                                    end loop

                                     

                                    It's very flexible, it avoids a 101 error, and it avoids the endless loop on portals that allow you to create new records.

                                    3 of 3 people found this helpful
                                    1 2 Previous Next