Using data in a ChooseFromDataSource

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.

Although this article mentions using a single data source on your template, you can of course configure multiple data sources and connect your template to all of them! Perhaps a Staff database, as well as a Client/Matter database.

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:

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’.