Sample Scripts

CONVERT_ACCESS (VB script)

This example shows you the principles on how data can be interpreted by using an extra database (Access) in your script. It uses OLE DB4 and ADO techniques to do this.

So, this is not a standard function. What is outlined here must be interpreted for each case.

The sample: We created a website where people can order stuff. The order itself must be posted into your order handling system, which we will call ERP (Enterprise Resource Planning) in this sample. Because you want to give visibility to your customer on when delivery will happen, you post back this information to your website. However, at the time your customer creates his order on the site, you must give him an order reference. That's tricky because usually it's your ERP creating those references, and your ERP is not connected real-time with your website. Now we need to work with a website order number and an ERP order number. How to link those 2?

The challenge in this is to find a common primary key, which is not the order number. The primary key could be the customer id together with purchase order number of the customer. A key that was posted with the order, but not stored on the website. 

Knowing this, the solution could be:

• Make a separate Access database (OrderXreference.mdb) with one table:

• In your PRODBX configuration, when your order is posted from the website, make sure the website ordernumber is also written in WebId, the customer number in CustomerNo and the purchase order number in PONo. Of course, at this point you don't know the ERP order.
• When the order is handled by your ERP it posts the ERP order number, the customer number, the purchase order number and the delivery date (which you want to publish). To convert the ERP order number to WebId we created the following script (Providing you wrote the purchase order number to the stack and you're currently handling the customer number):

  Function ConvertAccess(InputValue,Stack,Parameter1,Parameter2,Parameter3,Parameter4)
  
       'Build the connection with the access database
       Set AdoConnection = CreateObject("ADODB.Connection")
       ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0"
       ConnectString = ConnectString & ";Data Source="
       ConnectString = ConnectString & "D:\OrderXReference.mdb"
       ConnectString = ConnectString & ";Jet OLEDB:Database Locking Mode = 1"
       AdoConnection.ConnectionString = ConnectString
       AdoConnection.Open
  
       'Make the SQL query
       Set AdoRecordset = CreateObject("ADODB.Recordset")
       SQL = "Select WebId From OrderXReference where CustomerNo=" & InputValue
       SQL = SQL & " and PONo ='" & Stack & "'"
  
       'Go and get the record
       AdoRecordset.open SQL,AdoConnection
       ConvertAccess=AdoRecordset("WebId")
  
  End Function

• When writing this last information you may also update the ERPOrder in the Access table. With that you would be able to find it back when the customer wants to update something, like his PO number.