Introduction

Spindle Professional and Spindle Document Distribution allow us to look up data from an external source, which means that we are not confined to the data that we can place on the document.

Applies To

• Spindle Professional v6
• Spindle Professional 2012
• Spindle Professional 2009
• Spindle Professional 2007
• Spindle Professional 2005
• Spindle Document Management v7
• Spindle Document Management v8
• Spindle Document Distribution v7
• Spindle Document Distribution v8

Method

Create Data Source

To access data in another source (for example MS Access, SQL Server, Excel etc.) we need to have an ODBC connection and a driver for the data source. In the example here I will use a simple MS Access database containing one table (attached to this KBA) :

First we need to connect to the database itself. We do this by creating a Data Source. In SDD Tools, Navigate to External Data>Data Sources, right click in the white space, choose "New Data Source" and name it appropriately.

Click Edit to open the Data Link Properties window and choose the appropriate driver for your data source - in this case we will choose "Microsoft Jet 4.0 for OLE" as we will be connecting to an MDB database. 

Note: The drivers listed in this window will vary depending on the applications installed on the PC - the MS Jet 4.0 driver is installed by MS Office / Access, the SQL Native Client is installed by SQL Server, Sage 50 Accounts provides its own ODBC driver etc. If you cannot see the required driver in this window, you will need to add it using the control panel at Windows Control Panel>Administrative Tools>ODBC (32-bit).

Click Next>> to continue

In this next window we will specify the .mdb file we want to access and test the connection:

If the connection succeeds, click OK to finish. If you have any issues here, again refer to the ODBC (32-bit) control panel and ensure that if required (e.g. SQL Server, Sage 50 etc) any logins are correct and the connection string is correct.

We have now created our Data Source. 

Create the Data Link

With the source database specified, we can now create a Data Link to a specific table in the database. We can think of the Data Source as being the database, and the Data Link as specifying the table within the database. We can, if needed, create multiple Data Links for each Data Source. For this example right-click the white space by Data Links and choose New Data Link:

Name the link as appropriate, then specify the Data Source and the Table from which you want to extract data.

When the table link is configured, you will see the first couple of records in the table at the bottom of the window. You can page forwards or backwards using the arrow buttons at the bottom of the window to check the data are correct.

Next, we need to tell Spindle Document Distribution which data field is to be used to match against the table. Let's imagine that we have a field in our ERP that holds a ContactID Code of A, B, C, or D, and that we need to pull back from the database the email address and the contact's first and last names associated with the code.

Our document in our Accounts Package contains an expression such as:

This will be added to the Document as (for example): ##VAR10 A##

We need to tell Spindle Document Distribution to use this as the primary key lookup in the upper right of the Data Link window and to match it against the Code field:

We can now specify what commands we want the external data to be placed into. You can use any of Spindle Document Distribution's variables to hold the information, Spindle Document Distribution reserves 50 spare variables (DBVAR01 to DBVAR50) to hold these data so that no other fields need be overwritten, but if required, any command that already holds data will be updated with the values from the External data, overwriting the previous values. 

We can set FirstName to be ##DBVAR01##, LastName to be ##DBVAR02## and the email address to be ##EMAIL## overwritten with the new value from the database:

Note that we do not need to set Code to VAR10 here as we have already specified this at the top of the window.

In Pseudo-SQL this configuration means:

SELECT FirstName as DBVAR01, LastName as DBVAR02, Email as EMAIL from 'Demo Data Source'.'tblData' WHERE CODE = VAR10

Update the Document Operation 

We now need to add the External Data to the Automation:

If we imagine the Source Document from the accounting software contains these variables:

##CFG DemoInvoice##

##VAR1 Demo0001##

##VAR2 INV123456##

##VAR10 B##

##EMAIL info@company.com##

and we then process it through Spindle Document Distribution, then VAR10's value (B) will be extracted, matched against the CODE field of the database table and based on that record, DBVAR01 (Bob) and DBVAR02 (McRobert) will be populated and the email address will be replaced. After the data have been through this Data Link the commands would read as follows

##CFG DemoInvoice##

##VAR1 Demo0001##

##VAR2 INV123456##

##VAR10 B##

##EMAIL Bob@DemoTest.com##

##DBVAR01 Bob##

##DBVAR02 McRobert##

You will see that there is space for more than one set of External Data links, each of these will feed into the next, so we could if wanted, use DBVAR01 as a Primary key in a second External Data link and so on.

Troubleshooting

As with most troubleshooting of Spindle Document Distribution, if you get any issues, please check the Processor log file in the first instance. You will see the commands listed near the top as

20190604.173201 04: Adding command to list 'CFG DemoInvoice' 3 2
20190604.173201 04: ENTERING UpdateEMFRecords
20190604.173201 04: ENTERING ProcessCommand
20190604.173201 04: Adding command to list 'VAR10 B' 3 2
20190604.173201 04: ENTERING UpdateEMFRecords
20190604.173201 04: ENTERING ProcessCommand
20190604.173201 04: Adding command to list 'EMAIL info@company.com' 3 2
20190604.173201 04: ENTERING UpdateEMFRecords

Check that these fields are correct, if not then you will need to amend the report layout in your accounting package.

Next check that the Data Link is pulling data through from your data source:

20190606.092000 04: Attempting to open data source 'Demo Data Source'
20190606.092000 04: ENTERING CCmdParser::ParseString
20190606.092000 04: ENTERING CCmdParser::ParseString
20190606.092000 04: ENTERING ExtractParseCommand
20190606.092000 04: Extracted command name 'VAR10'
20190606.092000 04: ENTERING ProcessParseCommand 'VAR10'
20190606.092000 04: Query 'SELECT FirstName, LastName, Email FROM tblData WHERE (Code = 'B')'
20190606.092000 04: Attempting to open recordset
20190606.092000 04: Attempting to move to first record
20190606.092000 04: ENTERING CBaseRecordSet::MoveFirst
20190606.092000 04: Getting info on field 'FirstName'
20190606.092000 04: Read field value
20190606.092000 04: ENTERING CDataLink::SaveValueToList
20190606.092000 04: Field type '8'
20190606.092000 04: Field value 'Bob'
20190606.092000 04: Adding command to list 'DBVAR01 Bob' 2 0
20190606.092000 04: Getting info on field 'LastName'
20190606.092000 04: Read field value
20190606.092000 04: ENTERING CDataLink::SaveValueToList
20190606.092000 04: Field type '8'
20190606.092000 04: Field value 'McRobert'
20190606.092000 04: Adding command to list 'DBVAR02 McRobert' 2 0
20190606.092000 04: Getting info on field 'Email'
20190606.092000 04: Read field value
20190606.092000 04: ENTERING CDataLink::SaveValueToList
20190606.092000 04: Field type '8'
20190606.092000 04: Field value 'Bob@demotest.org.uk'
20190606.092000 04: Adding command to list 'EMAIL Bob@demotest.org.uk' 2 0

Knowledge Base Article Details