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.
Configure the Data Source
1. Open the XpressDox configuration form – in the Manage Settings group in the Word Ribbon.
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.
3. 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. You are now in the tricky part – the connection string. For Access and Excel, XpressDox constructs this for you once you have chosen the database file using the Browse button. For other database formats, the connection string needs special attention:
- If you are using SQL Server Express on your PC, then the configuration of the connection string will be done for you automatically after you have used the Browse button to find the database’s .mdf file.
- If you are not using SQL Server Express, then 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 (DBA) should have this information readily to hand.
- Once the connection string has been entered, press Next.
6. You now 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.
7. XpressDox will suggest a data element name for the table itself. If you were to ever refer to a collection of these data elements (i.e. when the data elements are repeated) you would probably want to say something like «ForEach(Customer)», not «ForEach(Sales.vIndividualCustomer)» (or in the case of the default name for a worksheet, this would be 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.
8.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 modern database designers tend to choose a database primary key to be a single column, 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
9. 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 to 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 from the data source.
10. In order to construct this search dialog, XpressDox needs to know a number of things, which are presented to you in the wizard.
11. Firstly you are asked to choose the columns which are presented in the Search Dialog. 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 in Table list.
12. 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.
13. The Data Source will now be displayed in the main Data Sources configuration form, with the name “New …. database”. You need to give the Data Source a better name than this – just choose something meaningful like “Customer” (if that’s what it is), type it in and press TAB.
14. The Configuration should now be saved. Then you can 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.
15. 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.
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, 6 and 7.
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 datasource, 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(DataSourceName,Choose the Customer)»
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.
