4 Replies Latest reply on Apr 19, 2015 10:08 AM by JavierDura

    Set field in several records

    JavierDura

      Hi.


      I am in LayoutA (from TableA).

      Using a script, I set a variable ($ids) with a list of ids of records from TableB:

       

       

      E26D13DE-277F-468A-A9F1-A80326768C80

      ABC90A1F-864B-4B1B-ACFE-6CC8104C7728

      564080C8-2E4E-4E9E-A9A6-D9CF7B604D02

       

      Using the same script, I set another variable ($values) with a list of new values:

       

      34

      12

      7

       

       

      TableB has two fields:

      id

      value

       

      My intention is to "set field" and insert the values from $values in the records of TableB according to the $ids list.

       

      So, 34 will be set in record E26D13DE-277F-468A-A9F1-A80326768C80,

      12 will be set in record ABC90A1F-864B-4B1B-ACFE-6CC8104C7728

      and 7 will be set in record 564080C8-2E4E-4E9E-A9A6-D9CF7B604D02.

       

      What would be the FASTEST way to do that?

      Thanks.

        • 1. Re: Set field in several records
          matthew_odell

          I do this type of thing all the time using something like this:

           

          Set Variable [ $valuecount ; ValueCount ( $ids) ]

          Loop

          Exit Loop If [ Let ( $i = $i + 1 ; $i > $valuecount ) ]

          New Record

          Set Field [ id ; GetValue ( $ids ; $i ) ]

          ....set as many fields as you need

          Commit Record

          End Loop

           

          And if you're doing this to hundreds or records, and this is most likely the only way records in table B are created, then you could add the calls in those set fields as Auto-enters on the fields, and remove those script steps. I've heard that technique is super fast with tons of records.

           

          Hope that helps,

           

          Matt

          • 2. Re: Set field in several records
            JavierDura

            Hi, Matthew.

             

            Thank you for your answer.

             

            What I need to do is not to create new records but to update records in TableB.

             

            So, I can use your technique if I use a global field to set a relation between those two tables:

             

            Set Variable [ $valuecount ; ValueCount ( $ids) ]

            Loop

            Exit Loop If [ Let ( $i = $i + 1 ; $i > $valuecount ) ]

            Set Field [ GLOBALFIELD ; GetValue ( $ids ; $i ) ]

            Commit Record

            Set Field [ tableA_TABLEB__update::value ; GetValue ( $values ; $i ) ]

            Commit Record

            End Loop

             

            It works as expected but I was wondering if there´s a way do it faster as maybe I will need to update hundreds of records.

            • 3. Re: Set field in several records
              matthew_odell

              The other option is to not have them as two different variables, but to create csv style text into a global field, export field contents to a csv file, then import that file. Only problem is it's difficult to know which records might not be updated because of record locking.

              • 4. Re: Set field in several records
                JavierDura

                Yes, it´s another option.

                I´ll check performance using both techniques once the solution is finished.

                Thanks!