Getting data from data sources into a template
Getting data from data sources into a template
Including data from a Data Source on a template with the IncludeDataSourceData, ChooseFromDataSource and IncludeFileData commands
The section on Data Sources describes in more detail what a Data Source is.
When a user runs a template from within Microsoft® Office Word, 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(Outlook Contacts)» or
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 ‘PersonalContact’.
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(Contacts Spreadsheet,,range=IncludeInMailshot = ‘yes’)»
This assumes that the data source ‘Contacts Spreadsheet’ 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 select a single row from a Data Source, using the ‘id=’ syntax, as in this command:
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#.
ChooseFromDataSource command is very similar to the
IncludeDataSourceData command, but will place a control on the Word Add-in data capture form, and the user will be able to choose from the data source and the data elements thus chosen will be included in the Data Set for the template. The second parameter is optional and is a caption to appear on the data capture UI, for example:
«ChooseFromDataSource(Clients,Select the Client)»
This will enable the user to choose a row from the ‘Clients’ data source and the data in that row will be included in the Data Set.
A further refinement of this command enables you to restrict the range of items from which the user can choose. For example, suppose you wanted to show only the clients whose date of birth was in the 20th century. The following would achieve that:
«ChooseFromDataSource(Clients,Select the Client,,Range=DateOfBirth <= '1999-12-31',RequireSearch)»
As noted above, 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
With effect from Version 5 of XpressDox, there is a 6th parameter which can be passed to the command, this being the name of a repeating data element. This has the effect of permitting the user to choose more than one entry from the search dialog. The entries thus selected are used to create and populate a set of repeating data elements. An example would look like:
«ChooseFromDataSource(Clients,Select the Client,,Range=DateOfBirth <= '1999-12-31',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, and those data elements will then appear in the treeview as a repeater (or, if the
«CaptureInGrid(Client)» command has been used, in a grid) on the interview.
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:
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.
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
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:
«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:
– 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
ChooseFromDataSource is ‘Refresh’. The default for
ChooseFromFile is also ‘Refresh’.
No ‘Save’ option is available for file data.