3 Replies Latest reply on Mar 5, 2017 5:03 PM by philmodjunk

    How can I script to set field of related records in a certain condition by using loop language?

    jiang_aq

      For example:

       

       

      Table 1:

       

      Invoice     Paid Date

      001           2/4

      002           9/3

      003           5/5

       

       

      Table 2:

       

      Invoice    Paid status

      001          Paid

      001          Paid      

      002          Paid

      003          Paid

       

       

      if in Table1: for Invoice=001, I wanna change Paid date to "",  then in table 2(related with table 1), I wanna all Paid Status for invoice#001 change to "Unpaid" by using loop script, as have many related records, how can I do it?

       

      Seems go to record command is just for local table, not for related table, right?

        • 1. Re: How can I script to set field of related records in a certain condition by using loop language?
          philmodjunk

          simplest approach would be to have one table instead of two.

           

          And updating a group of records does not require a loop. Replace field field contents can update them in a batch.

           

          That said, there are two basic approaches:

          1) use GoToRelatedRecords or a find to pull up a found set on a layout based on that table (can be in its own window hidden off the monitor edge) and update them there.

           

          2) use a relationship to access the records in the second table. In your loop, you either change records or update a match field with each iteration of the loop. With the second option, you pull up a list of invoice IDs in a variable and set your match field to a different value from that list each time.

           

          With all of these updates, you may have to also deal with the possibility that another user may have a record open for editing and thus keep the script from updating it. You

          • 2. Re: How can I script to set field of related records in a certain condition by using loop language?
            Malcolm

            In the example you've given there are two records for invoice 001 in table 2. That is a one to many ( 1:n ) relationship. If there was only one record in table 2 for each record in table 1 it would be a one to one relationship ( 1:1 ).

             

            When you are working in table 1 and you have one to one related data in table 2 you can make the changes to both pieces of data using the Set Field script step. In other words, your script can be written as if all the data was in table 1. You simply point to the correct fields and set the data.

             

            If you have one to many related data in table 2 you will have to use Go To Related Record to switch from table 1 to table 2. For each record in table 1, you have to switch to table 2 and loop through the records in table 2.

             

            malcolm

            • 3. Re: How can I script to set field of related records in a certain condition by using loop language?
              philmodjunk

              Good catch that this is "one to many". However, it is still possible to use a relationship to modify the data if you pull up a list of primary keys from the related table to use as match values in the relationship.