I have two tables, INVOICE_ITEMS_TEMP and INVENTORY. The common field is itemNo.
Each week, I truncate the IIT table, and import new invoice records. The IIT table typically has 100 records. The second (INVENTORY) table has 30K records.
For each record in IIT, I would like to copy a value (unitPrice) to the INVENTORY table via a script. This will ensure the INVENTORY table always last the latest unitPrice for each item.
One option is to loop through the 100 records, open a new window, go to the second layout, perform a find, use set field, and close the window. The big drawback to this option is window flashing.
Another option is to loop through the 100 records but without using “open new window.” The drawback to this option is (in general) potential issues with the found set, and any potential triggers associated with returning to the original layout.
If this process is only run once a week, and is only for about 100 records, then I really don't think it will have much perfromance impact on the solution at all. In fact, if run once a week I'd be scheduling this to occur on server (if you host it) , or using "Perform Script on Server". - this way you don't have to deal with window flashing issues at all (more on this later though).
looping 100 records doing anything really isn't going to take that long to be honest, so I'd just be taking that approach. There is really no reason to open multiple windows for each record. If you're concerned about messing up your existing context/found set, juste create a window to do the full operation and close when done, e.g.:
New Window [ Temp ; 0 ; 0 ; 0 ; -5000]
Enter Find Mode [ Pause: off]
Go to Layout [ Invoice Items Temp ]
Show all Records
Go to Record/Request/Page [ First ]
Loop
Set Variable [ $UnitPrice: InvoiceItemsTemp::UnitPrice ]
Set Variable [ $id_item ; InvoiceItemsTemp::ItemID ]
Enter Find Mode [ Pause: off ]
Go to Layout [ Inventory ]
Set Field [ Inventory::ItemID ; "==" & $id_item ]
Perform FInd
If [ get ( foundcount ) = 1 ]
Set Field [ Inventory::UnitPriceLast ; $UnitPrice ]
End If
Go to Layout [ Invoice Items Temp ]
Go to Record/Request/page [ next ; exit after last ]
end loop
Close WIndow [ current window ]
In short:
• Open a new temporary window for execution , I use -5000 left coordinate so it never appears on screen (no flashing)
• Get found set of invoice item temp records
• Loop through each record
• For each record, grab the item ID and unit price,
• Go find the corresponding inventory record
• Update the Unit Price on the found record
• Go back and continue loop until end....
• Close temporary window when done.
I agree this was not complex, my primary concern was window flashing versus potential found set issues. In the end, I created a script that opens only one window. Tomorrow, I’ll try running this script as PSOS.