Using the data source commands on your XpressDox template
If you have not yet configured a data source, you need to do before proceeding. You will find instructions on how to configure a data source here.
ChooseFromDataSource
The ChooseFromDataSource command will place a control on the interview, so you can choose one or more rows from the database. The data elements chosen will be included in the Data Set for the template.
A basic example of the ChooseFromDataSource command is shown below:
«ChooseFromDataSource(Clients,Select the Client)»
where “Select the Client” is the caption for the Data Source control.
A further refinement of this command enables you to restrict the range of items from which the user can choose. For example, suppose your database of clients contains a field for “Country” and you only want to show clients who are in the United States. The following example would achieve that:
«ChooseFromDataSource(Clients,Select the Client,,Range=Country = 'United States',RequireSearch)»
As noted above, “United States” is a constant and should therefore appear in quotes.
Using dates in the range:
When the data source is a SQL Server or MySQL database then the date constants in the range are delimited with quotes (as in the above example). But for Excel and Access data sources, the delimiter for dates is the # (pound) symbol.
Some examples of using dates in the range (SQL / MySQL syntax):
«Comment(an example using a hardcoded date)»
«ChooseFromDataSource(Clients,Select a client,RefreshNoSave,range=StartDate > ‘2024-03-01’ and StartDate < ‘2024-12-01’,AllowSearch)»
«Comment(An example using a dynamic date)»
«CaptureDataElements(CapturedDate1,CapturedDate2,date)»
«ChooseFromDataSource(Clients,Select a client,RefreshNoSave,range=StartDate GT ‘<CapturedDate1>’ and StartDate LT ‘<CapturedDate2>’,AllowSearch)»
«Comment(An example adding 30 days to today's date)»
«ChooseFromDataSource(Clients,Select a client,RefreshNoSave,range=StartDate > GETDATE() and StartDate < DATEADD(DAY, 30, GETDATE()),AllowSearch)»
Note the use of LT (less than) and GT (greater than) when using dynamic dates.
Using this command in a ForEach:
A 6th parameter can be passed to the command; the name of a repeating data element. Doing so places checkboxes in the search interface and enables you to select multiple rows. The selected rows are used to create and populate a set of repeating data elements. An example would look like:
«ChooseFromDataSource(Clients,Select the Client,,Range=Country = 'United States',RequireSearch,Client)»
Once the user has chosen the entries from the search dialog, Xpressdox will create one containing data element with name ‘Client’ for each of those entries i.e. «ForEach(Client)» , and those data elements will then appear as a repeater (or, if the «CaptureInGrid(Client)» command has been used, in a grid) on the interview.
Summary of all parameters in the ChooseFromDataSource command:
| Clients | The name of the data source as per the configuration |
| Select the client | The caption i.e. what you see on the interview |
| Refresh/Save | Refresh and save options as described below |
| Range | Optional. The default is to return all rows but this can be filtered to return only a range of data |
| RequireSearch | Optional. Disallow/require the search interface. The default is to allow. |
| Client | Optional. A collection (ForEach) name |
| Condition | Optional. Used in conjunction with the Save parameter. |
IncludeDataSourceData
When a user runs an XpressDox template, it is possible for them to select one or more Data Sources to provide data for that template. It is also possible for the template author to indicate that data for that template must be provided by a pre-configured Data Source so that the user doesn’t have to remember to choose it every time they run a template. This is done by use of the IncludeDataSourceData command:
«IncludeDataSourceData(Clients)» or «IncludeDataSourceData(Clients,,,Matters)»
The second example shows how the data from the data source is to be merged into the Data Set as part of the data element whose name is ‘Matters’.
When using a data source to produce a mail-merge type of document, then the IncludeDataSourceData command can include a ‘Range Restriction’, which in the data base sense is a ‘where’ clause (excluding the word “where”):
«IncludeDataSourceData(Clients,,range=IncludeInMailshot = ‘yes’)»
This assumes that the data source ‘Clients’ has a data field, or column, called ‘IncludeInMailshot’ and that all the rows which are required in the template have had the word ‘yes’ put into that column.
It is also possible to include a single row from a Data Source, using the ‘id=’ syntax, as in this command:
«IncludeDataSourceData(FirmAddressAndDetails,Refresh,id=1)»
This assumes that the Data Source has an ‘id’ configured for it which is numeric in type and the above command will select the data for the row which has an ‘id’ value of 1.
Note that if the ‘id’ column in the Data Source is not numeric, then the value must nevertheless not be in quotes. However, the syntax for the “range=” option requires that non-numeric values be in quotes and numerics not in quotes (as in the above example range=IncludeInMailshot = ‘yes’). Note also that when the value is a date, it must also be enclosed in quotes, except that for Excel and Access the delimiter is not a quote but the # symbol – hence range=DateOfBirth > #2001-01-31#.
IncludeFileData (not part of the data source commands but functions in a similar way)
The IncludeFileData command functions in a similar way to IncludeDataSourceData, but instead of referring to a data source in the command, a file is referenced (the format of that file is defined in the data capture section, that is ‘plain text’ or ‘XML’). The file does not need to be configured as a data source, but functions in much the same way, for example:
«IncludeFileData(lookups:Clients.xdtxt)» or «IncludeFileData(lookups:Transactions.xdtxt,,Account)»
where ‘Account’ is the name of the parent data element into which the data from the file should be merged.
When data elements are included in the data set via any of these commands, the data element names will be the same as
– for data sources: The column names in the database;
– for text files: The column names provided in the first line of the text file;
– for XML files: The names of the elements in the XML file.
For example, if a «ChooseFromDataSource(Clients)» command causes data to be read from the ‘Clients’ data source, and the database referenced has a column called ‘ClientName’, then the fillpoint «ClientName» will cause the name of the client to be merged into the document.
Note that the commands commencing with “Include” are executed BEFORE THE INTERVIEW is presented to the user. In particular, it means that ALL IncludeDataSourceData commands are executed before ANY ChooseFromDataSource commands.
A limitation that the file option has in that it cannot be filtered in the same way as the data sources can.
The Refresh and Save options
When running templates within the MS Word environment, the user has the option to run a template and then select ‘Use Other Data’ – which means use the Data Set saved from running this or another template on a previous occasion. This is a very useful feature, but has complications relating to data from Data Sources (and File Data).
The question arises about whether or not the data elements saved on the previous occasion should be used exactly as they were when the previous template was run. This is on the face of it what we would like. But, suppose some data elements were originally included from a data source, for example an Excel spreadsheet or a database, and the data elements in the data source have subsequently changed, should the new, changed data be used, or the data as saved when the last template was run?
In general, there is no hard and fast answer to this question, but in particular circumstances, the expectation would be that when data in a data source changes, it should affect not only new templates which reference that data source, but also templates using older data.
For this reason the IncludeDataSourceData, ChooseFromDataSource, IncludeFileData and ChooseFromFile commands all have a ‘refresh’ option. This indicates that when the user selects ‘Use Older Data’, then the data elements from the relevant data source must be refreshed from the data source and not copied directly from the originally saved data set.
Examples of usage are:
«IncludeDataSourceData(Clients,Refresh)»
«ChooseFromDataSource(Directors,Select the Director,Refresh)»
Related to this is the ‘Save’ option, which governs what happens when the user changes a value which originated from a data source. The template author can decide whether those changed items should be changed in the data source or not, or whether the user should be prompted to confirm that the data source should be changed.
The ‘Save’ option is connected to the ‘Refresh’ option in that if ‘Refresh’ is not chosen, then ‘Save’ cannot be chosen.
The full set of refresh and save options are, then:
– Refresh
– RefreshNoSave (which is the same as Refresh)
– RefreshOptionalSave: The user is prompted to save any changes.
– RefreshSave: Changes made by the user are saved without prompting the user.
The default for IncludeDataSourceData and ChooseFromDataSource is ‘Refresh’. The default for IncludeFileData and ChooseFromFile is also ‘Refresh’.
No ‘Save’ option is available for file data.
