Connect to a database – Configure a data source

Pull data from different data sources into your template

 

Introduction

XpressDox supports a number of database formats as “standard” data sources. These include Microsoft Access, Microsoft Excel (yes, the spreadsheet is regarded as a database), Microsoft SQL Server, MySQL, Salesforce, and then any database which has an ODBC driver installed for it.
Databases in general have a variety of ways of exposing themselves to the outside world and being used from within different applications. In order for XpressDox to present one uniform view of all databases to the template author,  regardless of the different engines used to implement the databases, the concept of an XpressDox Data Source was introduced. Then of course the Data Source needs to be described to XpressDox, and this is the process of configuration which is being described here.

Although technically Excel could be used as a data source, it is great for a proof of concept, and also for a small solution. It is not scalable however, and not advisable to use in a multi-user environment.

 

 

The first step is always to configure the Data Source

The configuration is a once-off step.

A quick note about configuration if this is the first time you are coming across it; it is important to take note of the path at the top of your configuration screen. All settings configured at this level are applied to this folder and sub-folders. If you want your data source to be available to all templates in this folder as well as all sub-folders, then configure it at the highest level. When you begin your configuration, XpressDox creates a DocumentAssembly.config.xml file that contains the settings.

Once the data source has been configured, you can access it on templates using commands such as ChooseFromDataSource or LinkToDataSource. To configure a data source, follow the steps as described below:

 

1. From the XpressDox ribbon, click Settings>Configure to open up the Configuration form.

 

2. In the drop-down box at the top of the form, choose the folder for which you want this Data Source configured, or click Browse to navigate to a folder. The configuration works in a hierarchical structure and so as mentioned above, if you configure the data source at the ‘highest level’ then it will be available to templates inside that folder as well as all sub-folders. If you configure it at a folder lower down in the structure, then it will be available to templates inside that folder only.

 

 

3. Next, click on the Data Sources tab, and then click the “New” button on the Data Sources toolbar (the button on the left with a green + sign on it).

 

 

4. Choose the database format from the set of radio buttons and then press Next.

 

 

5. Next is the connection string.  The questions in this wizard will change depending on what you chose as your data source type.
   For Access and Excel, browse to the database file and the connection string will be constructed for you.
   For SQL Server, it is constructed when the database is chosen and the user credentials are entered, and SalesForce after the credentials and token are entered. However, there are some database formats where the connection string will need to be entered manually.  The wizard has a link to the web site www.connectionstrings.com to help with this, but actually your Database Administrator should have this information readily on hand.
   Once the connection string has been populated or entered manually, press Next.

 

 

6. Next, you need to choose the table or view in the database that this Data Source is intended for.  Choose this from the drop-down box.  If the drop-down shows  list of tables and views, then you know you have configured the connection string correctly.  When the database is a spreadsheet, the table is a worksheet. On this same screen, XpressDox will suggest a data element name for the table itself.  If you were to ever refer to a collection of these data elements  in the template (i.e. when the data elements are repeated) you would probably want to say something like «ForEach(Customer)», not «ForEach(Sales.vIndividualCustomer)» or «ForEach(Sheet1)»  and so you now have the chance to indicate this by changing the data element name to Customer, or some reasonable name. Press Next.

 

 

7. Next, XpressDox needs to be provided with an “ID” for the table.  This is a column, or group of columns, whose value(s) uniquely identifies a row within the table.  This could be the Primary Key in the database, and it would probably make sense to choose this.  The Data Source wizard takes a stab at suggesting an ID column.  If it can’t find one column defined as unique, then it presents some columns for you to choose from. Most databases tend to have a single column primary key, and so the column that XpressDox suggests as the ID is probably correct.  In the case of a spreadsheet, there is no way for the spreadsheet author to indicate a primary key (or any kind of key), so XpressDox will examine all the columns of the worksheet and try to find at least one column that has unique values in it. Press Next.

 

8. At this point XpressDox needs to know the primary usage of this Data Source in terms of the number of database rows to be returned in the Data Source data set.  If you choose the option to enable the user to select a row (see screenshot below), then XpressDox will automatically construct a Search dialog which will be presented to the user (you will be able to see what this Search dialog looks like when the wizard is complete and you use the Test Data Source feature). This is the option (i.e. “The user must be able to choose a row from this table.” option) the that you would be using in 99% of the cases, and almost definitely if this is the first data source that you are configuring, because most of the time you would have a template where the «ChooseFromDataSource()» command is used to enable the user to choose a row (or multiple rows) from the data source.

 

In order to construct this search dialog, XpressDox needs to know a number of things, which are presented to you in the wizard.

 

• • Firstly you are asked to choose the columns which are presented in the Search Dialog.  Remember this is just the Search Dialog; all data will be returned but here you are simply selecting what the user should see when they choose a row from the data source. The wizard will suggest a few, but in the end you will know best how to present the data so that the user will easily identify the row of the customer they would like to select.  Do this by double-clicking the relevant column name(s)  in the Columns to Display list.

 

• • Part of the Search dialog is a filter, which enables the user to narrow down the search and the amount of data displayed.  The filter works on one column and it should normally be chosen to be either a surname or a company name or some string which the user would normally use to identify a row in the table.
    Press Finish.

 

 

9. The Data Source will now be displayed in the main Data Sources configuration form, with the name “New …. database”.  Type in a meaningful data source name, and click Save.

 

 

10. The Configuration should now be saved. When the connection string was constructed, if you used an Excel or Access database then that connection string contains an absolute file path. If you want to use this data source on the cloud, you will need to copy this DocumentAssembly.config.xml onto the server but it cannot contain an absolute file path. To change it into a relative path, click on the “Use Data Source Editor” button, and then on “Make Relative File Path”.

 

11. Now you can test that your new data source is working. To do this, use the Test Data Source button on the Data Sources toolbar (the one with the green check mark in it).  XpressDox will present the Search dialog to you.  You can then choose a row, and you will be shown, in a separate window, what the XML dataset for your choice looks like.

 

 

12. You will now be given the option of creating a schema for this data source.  It is recommended that you do this, because then you will be able to include that schema into the Command Editor, which will help you to choose the right column names from the data source (although they are at this point known as Data Elements, not columns).  Click Yes, and save the schema with a file name which will help you identify it.  The name suggested is the same name as the Data Source, which is probably a good idea. It does not matter where you save it, but it makes sense to save them in your XpressDox folder structure.

Note that the steps for creating any Data Source using this wizard are almost exactly the same whether the underlying data base is SQL Server, MS Access, Excel or any ODBC database.  The only difference will be in steps 5 and 6.

 

 

Naming of columns

There is an important point to bear in mind with regard to column naming. In some data base systems, and this applies specifically to an Excel spreadsheet as a database, the columns are permitted to have names which do not conform to the XML naming rules. This will cause trouble with XpressDox, because data needs to be presented to the XpressDox merge engine in XML format.
That means that prior to attempting to configure a data source, it would be best to inspect the tables that you would like to configure, and make sure that the column names comply with the rules for XML element names. If not, then you could either change them (if you have control over the names), or perhaps create a view which uses the same data but has appropriately named columns.

 

 

Using the Data Source in a Template

In the template that is to use this Data Source, use the Command Editor to include a command like:

«ChooseFromDataSource(Clients,Select a client,RefreshNoSave,,AllowSearch)»

This will create a control in the Data Capture dialog which will give the user the chance to choose the customer (or whatever the data source represents) for this template.

If you agreed to the Test Data Source feature creating a schema for you, then an entry will have been made for that data source in the Command Editor – below the list of commands and functions.  You will then be able to include fillpoints in the template which refer to those data element names from that Data Source just by double-clicking the name where it appears in the Command Editor.