2 Replies Latest reply on May 12, 2013 4:17 PM by James_L

    Same-Table Find, Loop, and Write

    James_L

      Title

      Same-Table Find, Loop, and Write

      Post

           I've got a table of records that need to be periodically sifted for field-entries corresponding to variables $$Search_Variable_1 and $$Search_Variable_2, with some fields from the resulting find written back into the same table with a third variable $$Type in the "Type" field.

           The trouble is that when I do my Find (using an Enter Find mode in the table's layout Table_1_Layout) and perform my loop to write the records, I run into some sort of snag in the loop, writing only one of the records.  I suspect that by going back into the layout Table_1_Layout to write the record, I invalidate the find or something.  But I'm only getting one record written.

           Here's my script:

           ------------------------

            Go to Layout ["Table_1_Layout" (Table_1)]

           Unsort Records

           Enter Find mode []

           Set Field [Table_1 :: COB_ID_FK; $$Search_Variable_1]

           Set Field [Table_1 :: COB_ID_Type; $$Search_Variable_2] 

           Perform Find []

           Go to Records / Request/Page [First]

           Loop

           Set Variable [$Person_ID; Value: Table_1 :: Personnel_ID_FK

           Go to Layout ["Table_1_Layout" (Table_1)]

           New Record / Request

           Set Field [Table_1_Layout :: Type; $$Type

           Set Field [Table_1_Layout :: Personnel; $Person_ID]

           Set Field [Table_1_Layout :: Inventory_ID_FK; $$Search_Variable_1

           Go To Record/Request/Page [Next ; Exit after last]

           End Loop

           -----------

           Is it possible to sift / find records, alter a couple of fields using variables, and then rewrite them back to the same table?   What am I missing? 

        • 1. Re: Same-Table Find, Loop, and Write
          philmodjunk

               When your script creates the new record that new record becomes the last record and thus the go to record/request/page [next ; exit after last] script step then executes from the last record and your loop exits on the first record.

               The "unsort" step and the Go to layout step inside the loop, BTW, are unecessary as they have no effect on the results of your script.

               Try it this way:

               Go to Layout ["Table_1_Layout" (Table_1)]
               Enter Find mode []
               Set Field [Table_1 :: COB_ID_FK; $$Search_Variable_1]
               Set Field [Table_1 :: COB_ID_Type; $$Search_Variable_2]
               Set Error Capture [on]
               Perform Find []
               If [ Get ( FoundCount ) //records were found ]
                   Go to Records / Request/Page [First]
                   Loop
                       Set Variable [$Person_ID; Value: Table_1 :: Personnel_ID_FK
                       New Record / Request
                       Set Field [Table_1_Layout :: Type; $$Type
                       Set Field [Table_1_Layout :: Personnel; $Person_ID]
                       Set Field [Table_1_Layout :: Inventory_ID_FK; $$Search_Variable_1
                       Omit Record
                       Go To Record/Request/Page [First]
                       Omit Record
                       Exit Loop If [ Get ( FoundCount ) = 0 ]
                   End Loop
               Else
                   Show Custom Dialog ["No records were found..."]
               End If
                

          • 2. Re: Same-Table Find, Loop, and Write
            James_L

                 Thanks, PhilModJunk.  Perfect.  I've got a couple of minor bugs floating around, but it's got something to do with my variables getting scrambled.  Your script is just what I needed.   Much cleaner than what I had going on.