1 2 Previous Next 17 Replies Latest reply on Oct 31, 2016 4:21 PM by keywords

    Loop vs Replace field contents

    ndveitch

      Hi There,

       

      Just a quick question, if I had a few thousand records that need to change, for example the "Amount" field needs to change to a negative, would it be better to use the replace field contents, or setup a loop and use set field? The records can range from a few hundred to a few hundred thousand depending on what the date range is set for.  There some calculations in the table, but not to many.

        • 1. Re: Loop vs Replace field contents
          mattel

          I've found replace field contents to be far faster than looping -- and that was on a record set of 100.  You should run a few timer tests and find out.

           

          But be very careful and make sure your found set is what you want to update before running it.     

          1 of 1 people found this helpful
          • 2. Re: Loop vs Replace field contents
            thomas_staehli

            I agree with mattel, I find replace field content usually faster. But just keep in mind that if you loop through the records and set fields, you can track individual errors (in case of record locking for instance). When you replace, it will just give you a global error making it difficult to track what record wasn't replaced.

            1 of 1 people found this helpful
            • 3. Re: Loop vs Replace field contents
              ndveitch

              Thank you for the quick response

              • 4. Re: Loop vs Replace field contents
                richardsrussell

                "Replace field contents" is almost always faster than looping — often very much faster — but I recommend against doing it directly into the target field. Every table I create has a field called "Temp" into which I can put stuff temporarily as needed. In this case, I'd use "Replace field contents" to fill Temp with FieldX * -1 and then spot-check the results before using the command again to move Temp back into FieldX.

                 

                "Replace field contents" with calculated result is an immensely powerful command, for good or ill. With great power comes great responsibility. Handle with care.

                1 of 1 people found this helpful
                • 5. Re: Loop vs Replace field contents
                  philmodjunk

                  I've seen this comment a lot in many places here:

                   

                  "Use a loop so that you can check for errors (such as another user locking the record)."

                   

                  If you use Replace Field Contents, it's possible to manage the same issue in most cases. You can use Get ( LastError) immediately after the Replace Field contents step to check to see if any records did not get updated. If so, you can then (this where it's a "most cases" method) perform a find for all the records that were not updated and take appropriate action to make the needed corrective action.

                   

                  Ideally, no batch update of large numbers of records should be done by either method at times or in a context where other users might be interacting with your data.

                   

                  That said, there is another reason why you might choose to use a loop and update records one at a time:

                   

                  With the proper setup, all the changes can be done as a single database transaction if you can loop through the records to be updated from the context of a single related record without doing anything to commit records in between each update. This protects your data from a situations where a client crash, server crash, network disconnect or other such system level glitch interrupts your batch update mid stream. If this happens, via this "single transaction" method, none of the data is modified and you aren't left with the much more troublesome result of having some records updated and not others.

                  1 of 1 people found this helpful
                  • 6. Re: Loop vs Replace field contents
                    ndveitch

                    Hi philmodjunk,

                     

                    Thank you for the reply, I just want to double check something. When you say "...If this happens, via this "single transaction" method,..." is the single transaction the Replace Field Contents? Is it the Replace Field that wont commit until the step has finished? If so that would be a far better option for this certain solution.

                     

                    I am importing data into a Temp table, before importing it into the final table. The Temp table is used to do all the calculations required, and right at the end I now have to change the Amount field to a negative value. In the past I would use a loop to get this right. Recently I have seen in demo files, the use of Replace Field and was just wondering the benefits.

                    • 7. Re: Loop vs Replace field contents
                      philmodjunk

                      If you read my last post again, you should see that I am talking about why you might use a loop instead of replace field contents for reasons other than speed. So this "transactional" method does not use replace field contents as it does not work that way. Note that I'm describing a very specific context as you would not even be on a layout based on the table of records you are modifying, but rather on a layout based on a related table.

                      1 of 1 people found this helpful
                      • 8. Re: Loop vs Replace field contents
                        StephenWonfor

                        I like the loops as they let you make a fatal error slower with some hope of escape.  Replace is simply too quick and final.  A loop also allows you to stash the prior values into a hash field that you can recover from in case you need to.

                        1 of 1 people found this helpful
                        • 9. Re: Loop vs Replace field contents
                          ndveitch

                          Hi philmodjunk,

                           

                          Looks like I have more reading to get through now . Thank-you again for the input, it is always greatly appreciated.

                          • 10. Re: Loop vs Replace field contents
                            fclark

                            Replace is good so long as all values to be entered are the same. It is hard to believe a field such as amount would be the same across 1000 records

                            • 11. Re: Loop vs Replace field contents
                              greatgrey

                              That's what the Replace with calculated result: option is for, when different values are needed for each record. I have used it serval times over the years.

                              1 of 1 people found this helpful
                              • 12. Re: Loop vs Replace field contents
                                fclark

                                Does that require "self" to work?

                                • 13. Re: Loop vs Replace field contents
                                  richardsrussell

                                  Nope. You can replace the field contents with any calculation you can imagine, including a reference to the original contents of the very field you're applying it to. (This is not recommended, however.)

                                  2 of 2 people found this helpful
                                  • 14. Re: Loop vs Replace field contents
                                    greatgrey

                                    No it does not. You just write your formula just like you would for a set field step. 

                                    I.e. say your Price on you products went up 10%.

                                    you just select your Price field then replace and enter [Price * 1.1]

                                    2 of 2 people found this helpful
                                    1 2 Previous Next