Script to Synchronize Customer Billing Address in Sales Order Data Entry

SOLVED

I was attempting to create a VB Script (to be executed via an External Link button) that would update the billing address within a sales order from the customer's AR address. Currently our admins do not have the ability to manually change a customer's address - this is only done through accounting.

Consider the following events:

  1. Admin/clerk enters a sales order via S/O Data Entry
  2. Accounting updates the billing address via Customer Maintenance 

The issue then arises that the sales orders that were created prior have the old address information. So I was thinking that it could be possible to create a simple script to update these fields in S/O Data entry, which looks like the following:

oUIObj.SetControlProperty "BILLTONAME", "Text$", SO_SalesOrder_bus_BillToName
oUIObj.SetControlProperty "UDF_BTA", "Text$", ""
oUIObj.SetControlProperty "BILLTOADDRESS1", "Text$", SO_SalesOrder_bus_BillToAddress1
oUIObj.SetControlProperty "BILLTOADDRESS2", "Text$", SO_SalesOrder_bus_BillToAddress2
oUIObj.SetControlProperty "BILLTOADDRESS3", "Text$", SO_SalesOrder_bus_BillToAddress3
oUIObj.SetControlProperty "BILLTOZIPCODE", "Text$", SO_SalesOrder_bus_BillToZipCode
oUIObj.SetControlProperty "BILLTOCITY", "Text$", SO_SalesOrder_bus_BillToCity
oUIObj.SetControlProperty "State", "Text$", SO_SalesOrder_bus_BillToState
oUIObj.SetControlProperty "BILLTOCOUNTRYCODE", "Text$", SO_SalesOrder_bus_BillToCountryCode

Took me a second however to figure out why it wasn't working, and of course it hit me that this is pulling from the same information that it was pulling from to begin with (SO_SalesOrder tables instead of AR_Customer). The issue is that "MS Script Variable Selection" only contains fields from the SO tables and nothing from AR:

Is there an easy way to pass these AR variables to this script without having to create an ODBC connection to the data tables?

  • 0

    After a couple of hours of research I think I am getting closer, but I am just not very familiar with the Sage object model...

    I assume I am needing to use GetObject() to return the data table I need for AR. Also figured out that I should be using Value$ instead of Text$ for the property name. I feel this may be close, but it still doesn't work:

    Dim oARCustomer
    Set oARCustomer = oSession.AsObject(oSession.GetObject("AR_Customer_bus"))
    oARCustomer.SetKeyValue "ARDivisionNo$", SO_SalesOrder_bus_ARDivisionNo
    oARCustomer.SetKeyValue "CustomerNo$", SO_SalesOrder_bus_CustomerNo
    oARCustomer.Find()

    Dim BTName, BTAddr1, BTAddr2, BTAddr3, BTZip, BTCity, BTState, BTCountry
    oARCustomer.GetValue "CustomerName", BTName
    oARCustomer.GetValue "AddressLine1", BTAddr1
    oARCustomer.GetValue "AddressLine2", BTAddr2

    'This should be re-populating the fields in the UI, but instead they get a value of '0'
    oUIObj.SetControlProperty "BILLTONAME", "Value$", BTName & " (Test)"
    oUIObj.SetControlProperty "BILLTOADDRESS1", "Value$", BTAddr1
    oUIObj.SetControlProperty "BILLTOADDRESS2", "Value$", BTAddr2

  • +1 in reply to SoonerFan21
    verified answer

    Are you trying to correct multiple orders because if you are, you would probably be better off running a VI job on SO_SalesOrderHeader with ODBC as the source, point it to the SOTAMAS90 DSN, you can optionally save the login credentials or have the user login every time they run it.  You could then link SO_SalesOrderHeader and AR_Customer and have a WHERE clause so it only selects records where there is a discrepancy among the address fields.  This way you could just run it periodically to update all orders.  There are pros and cons to this approach.  The biggest con is probably the fact that whoever runs the job will be the user who gets tracked as the user to update the records last.  The pro is that you don't have to do it on an order by order basis or have someone forget to update an order with the button script.

    If you are going to go the button script route, you should use the existing AR_Customer_Svc child handle.  You should not be use *_bus objects for read only activities, use the *_svc objects for this.

    When using a child handle object, I find it best to use the business object's ReadAdditional method and pass the child source name to it to force it to reread the child source record to make sure you get the expected values.  Depending on whether or not you are using National Accounts, and the order in question has a different bill to customer, then you will want to use either the "CustomerNo" or "BillToCustomerNo" child handle.

    sBillToCustomerNo = "" : oBusObj.GetValue "BillToCustomerNo$", sBillToCustomerNo
    If sBillToCustomerNo <> "" Then
    	oBusObj.ReadAdditional "BillToCustomerNo"
    	Set oAR_Customer_Svc = oSession.AsObject(oBusObj.GetChildHandle("BillToCustomerNo"))
    Else
    	oBusObj.ReadAdditional "CustomerNo"
    	Set oAR_Customer_Svc = oSession.AsObject(oBusObj.GetChildHandle("CustomerNo"))
    End If
    sAddressLine1 = "" : oAR_Customer_Svc.GetValue "AddressLine1$", sAddressLine1
    oUIObj.InvokeChange "BillToAddress1", sAddressLine1
    Set oAR_Customer_Svc = Nothing

    When the UI is present, like it is in a button script, you can use oUIObj.InvokeChange which should be used instead of SetControlProperty.  This makes sure the appropriate logic takes place.  When the UI is not present, you should be using oBusObj.SetValue.

  • 0 in reply to David Speck

    This works great, and thank you for explaining bus vs svc usage!