Here's a little tutorial on adding a custom field to sales orders lines.
1. Script into the table.
Firstly we need to add a row into the SO_LineCustomFields table, which defines the column we're adding. Things like field name, display name and field type (text, numeric, etc) are defined here.
We'll create a text field named "MyCustomField", which has a display caption of "My Custom Field" with the following SQL :
- Code: Select all
IF NOT EXISTS (SELECT TOP 1 FieldID FROM SO_LineCustomFields WHERE FieldName = 'MyCustomField')
INSERT INTO SO_LineCustomFields
SELECT LEFT(REPLACE(NewID(), '-', ''), 20),
'MyCustomField',
'My Custom Field',
1,
'',
'My Custom Field'
GO
2. Interact with the field using breakout script
Next we'll want to interact with the field, by setting it to some value. We firstly add a little helper function in the Form Loaded of the sales order form which makes the setting of the value (done later, in the Sales Order Line Changed breakout) a little simpler.
- Code: Select all
' Form Loaded Breakout (AFTER the End Sub)
Public Sub AddCustomFieldValue(SalesOrderObject, SalesOrderLineObject, FieldName, FieldValue)
Set lLineCustomField = SalesOrderObject.LineCustomFields(FieldName)
If Not lLineCustomField Is Nothing Then
Set lLineCustomValue = SalesOrderLineObject.LineCustomValues(FieldName)
If lLineCustomValue Is Nothing Then
SalesOrderLineObject.LineCustomValues.Add "", _
SalesOrderLineObject.InvoiceLineID, _
lLineCustomField.FieldID, _
lLineCustomField.FieldName, _
lLineCustomField.FieldType, _
FieldValue
End If
End If
End Sub
Lets say we want to set the text value of our field to be the Quantity Delivered whenever that changes - we'd add the following code to the Sales Order Line Changed breakout to do that :
- Code: Select all
' Sales Order Line Changed Breakout
If FieldName = "QuantityThisDelivery" Then
Set lLineCustomField = SalesOrderObject.LineCustomFields("MyCustomField")
If Not lLineCustomField Is Nothing Then
Set lLineCustomValue = SalesOrderLineObject.LineCustomValues("MyCustomField")
If lLineCustomValue Is Nothing Then
AddCustomFieldValue SalesOrderObject, SalesOrderLineObject, "MyCustomField", ""
Set lLineCustomValue = SalesOrderLineObject.LineCustomValues("MyCustomField")
End If
End If
If Not (lLineCustomValue Is Nothing) Then
lLineCustomValue.Contents = SalesOrderLineObject.QuantityOrdered
End If
End If
End Sub
So, now when the Quantity Delivered changes on the form, the "My Custom Field" value will be set to the same value. Note that we created the field as a text field, so when we assign the numeric value to it of QuantityThisDel, it gets automatically converted to the text representation (ie: 1 becomes "1").
In the above example, we needed the AddCustomFieldValue function to create a LineCustomValue record - this is because we don't want the SO_LineCustomFieldValues table (the table that stores the actual custom values associated with a sales order line) to have a row unnecessarily for each sales order line - the table would get quite large.
Of course, you could have a SO_LineCustomFieldValues record for each sales order line per custom field if you wanted to - just call the AddCustomFieldValue when the sales order line is added.
