Script to Change Invoice Lines

This example shows you how to edit invoice lines, giving quantity break pricing. We will also introduce data level scripting and use the Do ... Loop statement on invoice lines. We'll use a pretty simple rule for the script. If the quantity is:

Greater than 30 the customer gets Selling price 1 (Wholesale).
Between 15 and 29 the customer gets Selling price 2 (Trade).
Less than 15 the customer gets Selling Price 3 (Retail).

We'll assume the user will be running this script after entering the invoice and before printing or saving. So we can either use the same starting point we used earlier for entering a narrative line using a script, or we can record this starting point again and edit it. In this example, we'll use the existing starting point.

Open Script Editor and add the following code:
Dim frmINInvoice1 Object
frmINInvoice1 = GetActiveObject
If IsNull(frmINInvoice1) or Form1.ClassName <> "INInvoiceEntryForm" then Error "Wrong form class for script"
Save the script with the name SellingPrices.pfs.
We don't have to stick to the variable names or errors the recorder suggests. For example, we could change frmINInvoice1 to InvForm and change the error message:
Dim InvForm as Object
InvForm = GetActiveObject
If IsNull(InvForm) or InvForm.ClassName <> "INInvoiceEntryForm" Then Error "Wrong form for script"
To look up the selling prices we are going to need a product enquiry, so that's the next step. Add the following code to open the IC Product form:
Dim ProdForm as Object
ProdForm = CreateObject("Accredo.ICProductForm")
Since we'll refer to the invoice line several times in the code we can give it a short name: Line.
Dim Line as Object
Line = InvForm.Line
Then we need to go to the first line and work down the invoice lines with a Do ... Loop.
Line.First
Do Until Line.EOF
Line.Next
Loop
Now we need the action to be performed on each line within the loop. We only want to select for price breaks if we are dealing with a Product line, so that will use an If statement. Then we want to select what the quantity on the product line is, so we'll use another If statement, and set a variable Pricebreak based on that.
If Line.LineType = "Product" Then
If Line.QuantitySupplied > 30 Then
    Pricebreak = 1
ElseIf Line.QuantitySupplied > 15 Then
    Pricebreak = 2
Else
    Pricebreak = 3
End If

End If
Next, we want to look up the new price if we need one. This will find the first three prices from the Price grid on the Product form. We will set an Array called Prices[] and save the prices in this Array.
If ProdForm.FindExact(Line.ProductCode) Then
    Dim Prices[1 to 3] as number
    For i = 1 To 3
      Prices[i] = ProdForm.Price.SellingPrice
      ProdForm.Price.Next
    Next

End if
Finally we want to set the Selling Price on the line to the correct price from the Prices Array.
   If PriceList = 1 Then
      Form1.Line.SellingPrice = Prices[1]
    ElseIf PriceList = 2 Then
      Form1.Line.SellingPrice = Prices[2]
    Else
      Form1.Line.SellingPrice = Prices[3]
    End If
Now put them all together:
Dim Form1 as Object
Form1 = GetActiveObject
If IsNull(Form1) or Form1.ClassName <> "INInvoiceEntryForm" then Error "Wrong form class for script"

Dim ProdForm as Object
ProdForm = CreateObject("Accredo.ICProductForm")

Dim Line as Object
Line = Form1.Line

Line.First
Do Until Line.EOF

If Line.LineType = "Product" Then
If Line.QuantitySupplied > 30 Then
    Pricebreak = 1
ElseIf Line.QuantitySupplied > 15 Then
    Pricebreak = 2
Else
    Pricebreak = 3
End If

If ProdForm.FindExact(Line.ProductCode) Then
    Dim Prices[1 to 3] as number
    For i = 1 To 3
      Prices[i] = ProdForm.Price.SellingPrice
      ProdForm.Price.Next
    Next

   If Pricebreak = 1 Then
      Form1.Line.SellingPrice = Prices[1]
    ElseIf Pricebreak = 2 Then
      Form1.Line.SellingPrice = Prices[2]
    Else
      Form1.Line.SellingPrice = Prices[3]
    End If
End If
End If

Line.Next
Loop

Note: We need to move the End If and Loop statements to the end, to ensure the statements continue.
Save the script again and try running it.
Result: The script runs, but the product form flashes up on screen and disappears as it runs through each product record. What we are doing here, because we are using the forms, is called Form Level Scripting.
If you have the DI module installed, you can access data objects as well as form objects and do data level scripting. This would replace:
Dim ProdForm as Object
ProdForm = CreateObject("Accredo.ICProductForm")

with

Dim ProdForm as Object
ProdForm = CreateObject("Accredo.ICProductData")
Make that change and try running the script. Result: You won't see the IC Product form flashing up.
Now get the script to check if DI is available so it will work in either context. To do this, use the ModuleAvailable function:
Dim Form1 as Object

Form1 = GetActiveObject

If IsNull(Form1) or Form1.ClassName <> "INInvoiceEntryForm" then Error "Wrong form class for script"

Dim ProdForm as Object

If ModuleAvailable("EDI") then
ProdForm = CreateObject("Accredo.ICProductData")
Else
ProdForm = CreateObject("Accredo.ICProductForm")
End If

Dim Line as Object

Line = Form1.Line

Line.First

Do Until Line.EOF

If Line.LineType = "Product" Then

If Line.QuantitySupplied > 30 Then

Pricebreak = 1

ElseIf Line.QuantitySupplied > 15 Then

Pricebreak = 2

Else

Pricebreak = 3

End If

If ProdForm.FindExact(Line.ProductCode) Then

Dim Prices[1 to 3] as number

For i = 1 To 3

Prices[i] = ProdForm.Price.SellingPrice

ProdForm.Price.Next

Next

If Pricebreak = 1 Then

Form1.Line.SellingPrice = Prices[1]

ElseIf Pricebreak = 2 Then

Form1.Line.SellingPrice = Prices[2]

Else

Form1.Line.SellingPrice = Prices[3]

End If

End If

End If

Line.Next

Loop

If Not ModuleAvailable("EDI") then

ProdForm.Close
ProdForm = Nothing

End If