Displaying Estimated Total Order Weight on the SOP Transaction Entry Window

By Steve Endow

I have a client that ships thousands of pounds of products and equipment to customers, often filling small "consolidator" warehouses with the goods.

Because of the size and weight of the products, most orders required them to get a shipping estimate from a freight provider prior to finalizing the order. Dynamics GP doesn't display the total weight of an order during order entry, so the client asked me to create a custom field that would calculate the order weight and display it on the Sales Transaction Entry window.

Naturally, you need to have the Shipping Weight field populated for all of your inventory items in order for the order weight to be accurate. And of course this total order weight doesn't necessarily equate to the weight value that you will actually end up with when shipping, but it can be a decent estimate.

I wrote this several years ago, but since a similar request came up in the Dynamics GP newsgroup, I thought I would share the code.

First, you use Modifier to add a custom field to the Sales Transaction Entry window. (I'll assume that you know how to do that, or can find a tutorial on that elsewhere)

You'll then add some VBA to the sales window. I came up with the following code. Note that you may need to adjust this query to meet your specific needs.



Public Sub UpdateOrderWeight()

If Trim("" & SalesTransactionEntry.DocumentNo.Value) <> "" Then

Dim cn As New ADODB.connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

Set cn = UserInfoGet.CreateADOConnection
cn.DefaultDatabase = UserInfoGet.IntercompanyID
cmd.ActiveConnection = cn

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn

cmd.CommandText = "SELECT ISNULL(SUM(ROUND(l.QUANTITY * (i.ITEMSHWT / 100.00), 2)), 0.00) AS ORDERWEIGHT " & _
"FROM SOP10200 l JOIN IV00101 i ON i.ITEMNMBR = l.ITEMNMBR " & _
"WHERE l.SOPNUMBE = '" & Trim(DocumentNo.Value) & "'"

Set rst = cmd.Execute

'7/26/2013: S. Endow: There is a bug / quirk in GP VBA that sometimes causes a field value assignment
        'to generate an "Error 1007 Unsafe Operation" error.
        'http://support.microsoft.com/kb/856199
        'Instead of setting focus, then assigning a value, or just assigning a value, you have to
        'set the Focus and assign the value in the same step.
        
        'TotalOrderWeight.Value = FormatNumber(rst.Fields("ORDERWEIGHT").Value, 2, vbTrue, vbFalse)
        TotalOrderWeight.Focus (FormatNumber(rst.Fields("ORDERWEIGHT").Value, 2, vbTrue, vbFalse))

rst.Close

cn.Close

End If

End Sub

I then added a call to that sub from the Document Number field and the line grid so that the number is refreshed if a new document is opened, or if lines are added or removed from the grid.



Private Sub DocumentNo_Changed()

Call UpdateOrderWeight

End Sub


Private Sub Grid_AfterLineGotFocus()

Call SalesTransactionEntry.UpdateOrderWeight

End Sub


And this is the result:

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com