Many a database administrator and ERP consultant has gnashed their teeth over the love affair the business world has with Excel. End users frustrated with the rigidity of highly structured database clients often resort to keeping separate spreadsheets of identical information because it allows them add formulas, highlights and other custom attributes at will. No amount of features added to the ERP system will convince these people to part with their beloved spreadsheets and rely solely on the xTuple Application client. If we can not bring the spreadsheet to xTuple, why don't we bring xTuple to the spreadsheet?
Below are a couple examples of quick-and-dirty spreadsheet applications using Visual Basic for Applications (VBA) macros to connect to an xTuple database. Though they are fully functional, they are not necessarily meant to be deployed in a production environment, but rather to give ideas as to how the xTuple API can be used to integrate office applications to xTuple ERP.
Interactive Customer List
This Office 2003 spreadsheet will allow a user to load the list of customers in our PostBooks® demo database alter them or add new ones, then save the information back to PostBooks®. A copy should have been included in the zip file you downloaded from the introduction page. To run it you must:
- Change the credentials on the "Login" tab
- Use ctrl+l to "Load" your customer data
- Use ctrl+s to "Save" your changes back to the database
Note: You will need to configure Microsoft Office to enable Macros for this application to work correctly.
The examples shown below are built in an Office 2007 environment. To build this application from scratch, do the following:
Add Header fields to Customers page
Open a new Excel spreadsheet workbook and change the name of the first page to "Customers." Enter header titles on the first row as shown.
Add login credentials to Login page
Change the name of the second page to "Login" and enter database connection parameter names in column "A" as shown. The actual values in column "B" should reflect your local database connection parameters.
Now click on the "View" tab in Excel and select the macros drop down as circled above and select "View Macros."
Create the Load macro
On the Macro name line type "Load" and click "Create."
Add Reference to ADODB
The VBA Editor should appear. Before we can add our macro code, we need to include a reference use ActiveX data objects. Go to Tools > References to bring up the references dialog. Scroll down and select the Microsoft ActiveX Data Objects 2.5 library. There will likely be several versions. We used 2.5 library for this example so it would also work on older installations of Office, but you should be able to use the latest version available. Click "OK."
Now it's time to add code. This discussion assumes the user has an understanding of Visual Basic and ActiveX data objects (ADODB). These are very broad and deep topics that goes beyond the scope of this paper, but for which thousands of articles and books have been written. We have, however, included comments to help users new to these technologies follow along on what's happening
First we'll need to add some global variables. We need to do this so Excel can maintain the connection and data between the time when we load our data, change it and send it back to the database. Type or paste this code above the Load sub routine that was created:
'Set up Global data objects Public pbConn As New ADODB.Connection Public custRcd As New ADODB.Recordset Public strConn
Next we'll type or paste in the code to get customer data into our Load subroutine:
Sub Load() 'Get Connection information from worksheet If strConn = "" Then strConn = "Driver=" & Sheet2.Range("B1") & ";" & _ "Server=" & Sheet2.Range("B2") & ";" & _ "Port=" & Sheet2.Range("B3") & ";" & _ "Database=" & Sheet2.Range("B4") & ";" & _ "Uid=" & Sheet2.Range("B5") & ";" & _ "Pwd=" & Sheet2.Range("B6") & ";" 'Pass connection parameters and Open the connection pbConn.Open strConn Else 'Reset the connection pbConn.Close pbConn.Open strConn End If 'Get the data custRcd.Open "SELECT * FROM api.customer ORDER BY customer_number", _ pbConn, adOpenDynamic, adLockPessimistic 'Clear the worksheet Range("A2", "I50000").ClearContents Dim intRow intRow = 2 'Now populate new data Do While Not custRcd.EOF 'Start looping through the records until we reach 'End of File' Range("A" & CStr(intRow)).Select ActiveCell.Value = custRcd("customer_number") Range("B" & CStr(intRow)).Select ActiveCell.Value = custRcd("customer_name") Range("C" & CStr(intRow)).Select ActiveCell.Value = custRcd("customer_type") Range("D" & CStr(intRow)).Select ActiveCell.Value = custRcd("sales_rep") Range("E" & CStr(intRow)).Select ActiveCell.Value = custRcd("default_terms") Range("F" & CStr(intRow)).Select ActiveCell.Value = custRcd("ship_via") Range("G" & CStr(intRow)).Select ActiveCell.Value = custRcd("credit_limit") Range("H" & CStr(intRow)).Select ActiveCell.Value = custRcd("credit_rating") Range("I" & CStr(intRow)).Select ActiveCell.Value = custRcd("notes") intRow = intRow + 1 custRcd.MoveNext 'move on to the next customer Loop 'Move focus to top Range("A2").Select End Sub
The result should look something like this:
Visual Basic Editor
Next let's go ahead and create the "Save" sub routine to upload our changes. Type or paste this code after the end of the Load procedure:
Sub Save() Dim intRow intRow = 2 On Error GoTo ErrorHandler 'Move back to the first customer record and loop through 'to update from changes made on Excel Range("A" & CStr(intRow)).Select 'Move focus to first cell custRcd.MoveFirst 'Move to first customer record in xTuple Do While Not ActiveCell.Value = "" 'Loop through spreadsheet as long as we find something If custRcd.EOF Then 'If no xTuple customers left, must be a new one custRcd.AddNew End If custRcd("customer_number") = ActiveCell.Value Range("B" & CStr(intRow)).Select custRcd("customer_name") = ActiveCell.Value Range("C" & CStr(intRow)).Select custRcd("customer_type") = ActiveCell.Value Range("D" & CStr(intRow)).Select custRcd("sales_rep") = ActiveCell.Value Range("E" & CStr(intRow)).Select custRcd("default_terms") = ActiveCell.Value Range("F" & CStr(intRow)).Select If ActiveCell.Value <> "" Then 'Ignore blanks so we get default custRcd("ship_via") = ActiveCell.Value End If Range("G" & CStr(intRow)).Select If ActiveCell.Value <> "" Then 'Ignore blanks se we get default custRcd("credit_limit") = ActiveCell.Value End If Range("H" & CStr(intRow)).Select If ActiveCell.Value <> "" Then 'Ignore blanks se we get default custRcd("credit_rating") = ActiveCell.Value End If Range("I" & CStr(intRow)).Select custRcd("notes") = ActiveCell.Value intRow = intRow + 1 'Increment row Range("A" & CStr(intRow)).Select 'Move focus back to first column for next loop custRcd.MoveNext 'Move on to the next xTuple customer Loop 'Look Ma, No SQL! 'Refresh the data Load Exit Sub ErrorHandler: MsgBox Err & ": " & Error(Err) custRcd.CancelUpdate End Sub
Save your changes and close the Visual Basic editor. From Excel, go back to view the macros and run the Load macro. Your spreadsheet should be populated with the list of customers from your database!
To make things a bit easier, from the macro list highlight the Load macro and select Options. Type "l" in for the shortcut key. Do the same for the Save macro using "s."
Now with the quick ctrl+l keystroke you can refresh your customer list. Try making changes to a customer name, then ctrl+s to save them. Try entering an invalid customer type and save. What happens? Enter a new customer line and save. Note the api has the same validation rules as the UI, but none of that had to be dealt with explicitly in the macro code. It's all taken care of by the xTuple API and ActiveX objects.
Electronic Order Form
The electronic order entry form is a concept that could potentially solve a couple of problems. One is a situation where users don't want all the features in the standard client and simply want a stripped down order entry screen. With some improved security measures, this idea could also be the beginning of a solution where customers could fill out this form and email it to customer service where it could be uploaded into an xTuple database with only a keystroke. To use it simply do the following:
- Change the credentials on the login tab to point to your local environment
- Back on the order form tab, enter the required order information.
- Hit ctrl+s keys to save.
We won't bother walking through the steps of setting up this application as it is pretty much the same as the customer list above. You can review the comments in the macro code to see how it works. However there is an important configuration change in PostBooks® that needs to be made to allow this application to work:.
In xTuple ERP, go to System > Configure Modules > Sales and make sure Sales Order# Generation is set to "Automatic, Allow Override." This will allow the Excel application to assign an order number.
Electronic Order Form
Some interesting things to note about this form are that first if a customer number is not specified, or one is but is not found, both the customer and the order are created together on save. Also note the order number is automatically generated, and some error trapping is included that transcends xTuple logic (like making the contact information required) for this particular business need.
Hopefully this tutorial has helped you understand better the flexibility and benefits of using xTuple databas API views. We think it would be helpful if the community could help contribute other application examples written in languages such as Java, .NET, Python, or Ruby. In addition, it would also be useful to see examples of object wrappers for these views, which should be a very straightforward task since no translation or mapping would be required. If you have an interest in adding to our library of examples, please post your idea on our developer forum.