This article shows how to configure a sub-collection, and then how to use it in a LinkToDataSource.
As shown below, enter data such as an account number and press TAB. The Link retrieves data from the database based on the ID (to retrieve one record) or range (to retrieve multiple records).
Create a database with this structure:
To replicate such an example, create a database with the following structure (Excel is fine for proof of concept, and in Excel your tables would be worksheets):
Table “Clients”
ClientID
ClientName
[some other fields which aren’t used here such as ClientType,ClientMaritalStatus]
Table “Matters”
MatterID
MatterDescription
[some other fields which aren’t shown here such as MatterType, Jurisdiction]
ClientID
and enter some data into each, as shown below. Note how the Matters belong to the Client through the ClientID through a 1 to many relationship.
Now create your data source:
Using the instructions found in this article, create a data source to your Clients table. Now we will add a Collection to the Matters table.
- Click Use Data Source’s Editor.
- Click on the “Clients” Collection on the left, and then right click on it. Click New>Collection.
- Type in an Element Name; this is not prescribed but should make sense e.g. Matters. This Element name will be used on the template when you refer to this collection in the ForEach.
Now choose the Table/View name from the dropdown list.
- Click IdColumn on the left, and select the ID column of this table.
- Click Range. Choose the Column Name, and then the Value from the Parent Column. This is the link between the two tables i.e. the Column Name you select matches the value from the Parent Column.
- Save your Data source and test it.
- The XML results should return one client + many matters (depending of course if that client has many matters).
Next, create your template:
If you have used the same data elements as in the example, then you can copy and paste this example and it should work. The examples ‘785’ or ‘4565’ are based on the data as shown in the database.
«ClientID»
«Heading(ClientID,Type in a value into the Account number to retrieve some info.\nl\nlThe following numbers can be used as examples ‘785’ or ‘4565’.
\nl\nlAfter typing in the number press TAB.)»
«ClientName»
«LinkToDataSource(ClientID,Clients,RefreshNoSave,id=<ClientID>)»
«ForEach(Matters)»
«MatterDesc»
«End()»
«Caption(ClientID,Account number)»
Using LinkToDataSource
The LinkToDataSource command triggers on exiting a field in the interview. That field is specified inside the LinkToDataSource command (in the case of this example ClientID). There is also a command called LinkToDataSourceOnEnter, which triggers on entering a field. With a LinkToDataSource, when the user enters the ClientID and moves the focus out of that field in the interview, XpressDox will read the Clients data source, using the value of the ClientID data element as the Id, and retrieve the relevant row from the Clients table. This will populate the ClientID data element, and so the test against it will have the correct values to test against.
The reason for the Id=<ClientID> parameter is that by default, when a LinkToDataSource command is executed, if the Id= parameter is missing, then the value of the data element in the command is used as the Id of the linked data source. Therefore it is possible to specify a data element as the ID other than the field which is the link.
LinkToDataSource is very quick for the user – usually they would enter a number such as a matter number or account number, a number which is very familiar to them which then does the data retrieval. ChooseFromDataSource, which produces a search interface, would likely slow them down.
