Script to Find and Replace

This example shows you how to record a script to find X and replace with Y across all records. We will use a Do... Loop, and use "If X do this, else do that", If-Then-Else statements. The example we will use is changing IC Creditor codes.

1. First, we record what we can. This is a quick way to get the right names for forms and controls, but this does not result in the complete script.

• Click Record Script (Alt+F1) to start recording.
• Go to Navigator > Maintain > Inventory Control > Products > Edit (F11).
• Set the Creditor to FORD.
• Click Save (F9).
• Press F8 to select the next record.
• Click Stop Recording (Alt+F1) to turn off the script recorder.
  Result: Script Editor opens and displays what it has recorded:

  Dim frmICProd1 as Object
  frmICProd1 = CreateObject("Accredo.ICProductForm")
  frmICProd1.Edit
  frmICProd1.CreditorCode = "FORD"
  frmICProd1.Save
  frmICProd1.Next

1. Save this with the name ChangeCreditorCode.pfs and run it.
   Result: The script will select the first record and change it.
2. We need to edit the code so it will "loop" through the file, changing all the records. To do this we use a Do ... Loop MaxBasic Statement. See Online Help for the right syntax.

   Dim frmICProd1 as Object
   frmICProd1 = CreateObject("Accredo.ICProductForm")
   Do until frmICProd1.EOF
     frmICProd1.Edit
     frmICProd1.CreditorCode = "FORD"
     frmICProd1.Save
     frmICProd1.Next
   Loop

   If you leave out the frmICProd1.Next you will end up with an infinite loop which never reaches the EOF. To break out of this, press ESC or Alt+F1.
   Result: This script will change every creditor code to FORD regardless of what the original code is.

3. Now we need to further edit the code to change only one creditor code, say CONWAY to FORD. To do this we can use an If ... Then ... Else MaxBasic Statement inside our Do ... Loop. See Online Help for the right syntax.

   Dim frmICProd1 as Object
   frmICProd1 = CreateObject("Accredo.ICProductForm")
   Do Until frmICProd1.EOF
     If frmICProd1.CreditorCode = "CONWAY" then 
       frmICProd1.Edit
       frmICProd1.CreditorCode = "FORD"
       frmICProd1.Save
     End If
     frmICProd1.Next
   Loop

4. The next enhancement we might want to make is to generalise this, so we ask the user which Creditor code they want to find and which code they want to replace it with. We'd do this using a pair of Input code prompts.

   FromValue = InputCode("APCRED", "From Creditor Code", "From Code")
   If IsNull(FromValue) Then Abort("Cancelled by user")
   ToValue = InputCode("APCRED", "To Creditor Code", "To Code")
   If IsNull(ToValue) Then Abort("Cancelled by user")
   Dim frmICProd1 as Object
   frmICProd1 = CreateObject("Accredo.ICProductForm")
   Do Until frmICProd1.EOF
     If frmICProd1.CreditorCode = FromValue then 
       frmICProd1.Edit
       frmICProd1.CreditorCode = ToValue
       frmICProd1.Save
     End If
     frmICProd1.Next
   Loop
   frmICProd1.Close

5. Finally, we might want to look at adding a counter to count how many replacements we make and report it. To do this we set a variable Count, increment it for each match, and use the print function to output it at the end.

   FromValue = InputCode("APCRED", "From Creditor Code", "From Code")
   If IsNull(FromValue) then Abort("Cancelled by user")
   ToValue = InputCode("APCRED", "To Creditor Code", "To Code")
   If IsNull(ToValue) then Abort("Cancelled by user")
   Count = 0
   Dim frmICProd1 as Object
   frmICProd1 = CreateObject("Accredo.ICProductForm")
   Do Until frmICProd1.EOF
     If frmICProd1.CreditorCode = FromValue then 
       frmICProd1.Edit
       frmICProd1.CreditorCode = ToValue
       frmICProd1.Save

   Count = Count+1
     End If
     frmICProd1.Next
   Loop
   frmICProd1.Close
   Print Count & " instances of Creditor Code " & FromValue & " found"
   Print "and replaced by Creditor Code " & ToValue & "."

6. Save the script with the name ChangeCreditorCode.pfs.