LinkToDataSource links any control on the interview to a data source (a control is what the user uses to enter data, such as a straight forward text box, or a checkbox, or a dropdown, etc.). It means that when the focus moves off that control, a call to the data source is made, to retrieve the data.
The simplest example is where a value like an account number is entered in the interview, and the system is required to read the data for that account number from the data source.
Note that with effect from version 4.3.1 of XpressDox, this feature works slightly differently with checkboxes, radio button lists and drop down lists with fixed values (i.e. not those created for ChooseFromSamples): in these cases the data are retrieved from the data source not when the control loses focus, but when the user chooses a value from the control. This seems to be more intuitive in these instances.
Basic form of the command.
Suppose a data source has been configured to access a database table called Account, and the data source itself is named Account. Further the Id of the data source is defined to be a single column (it doesn’t matter what the column name is, as long as it is configured correctly in the XpressDox configuration for the data source). Further, there is a data element in the interview called AccountNumber, and the user is expected to enter the value of the Id of the data source here.
The command
«LinkToDataSource(AccountNumber,Account)»
will link the data source named Account to the control which captures the value of AccountNumber, and when the focus moves out of that control in the interview, the value of that control, which is now the value of the AccountNumber data element, will be used as the Id to retrieve the relevant row from the data source’s table.
Notice that the LinkToDataSource command does not specify what sort of control the AccountNumber data element is to be captured with. That means that constructs like:
«ChooseFromList(AccountNumber,A0001,A0002,B1010)»
«LinkToDataSource(AccountNumber,Account)»
would mean that the user would choose one of those three values from the drop-down list, and whichever one they chose would cause the relevant record to be read from the data source.
Composite Id
Suppose a data source called Cases has been configured, but this data source has a composite id, in other words the Id consists of more than one database column. In this example, suppose those columns are the Client Number and the Case Number.
Then, the interview could be constructed so that the user is requested to type in a data element called ClientNumber and another data element called CaseNumber. Assuming the ClientNumber is typed in first, then the idea would be that as soon as the CaseNumber is typed in that the system will have enough information to retrieve the relevant record from the Cases data source. The command
«LinkToDataSource(CaseNumber,Cases,RefreshSave,'id=<ClientNumber>,<CaseNumber>')»
will construct the value of the Id from the two data elements ClientNumber and CaseNumber, separated by a comma (which is the default separator between elements of a composite Id) and request the value of the data for that Id from the data source. This will all be triggered after the user has entered the value in the control on the interview for CaseNumber, and has moved the focus (e.g. by pressing the Tab key) out of that control.
Note the quotes around ‘id=<ClientNumber>,<CaseNumber>’. These are to prevent the comma between <ClientNumber> and <CaseNumber> from being taken as a delimiter between parameters to the command.
Note also the < … > surrounding ClientNumber and CaseNumber. These are to indicate that ClientNumber and CaseNumber are the names of data elements, and that the values of the data elements should replace them in constructing the value of the Id.
The RefreshSave parameter (and the other possible values that it can have) is described in full in the articles Control how the user can change data source data and Other advanced features in XpressDox
As with all other commands which retrieve data from a data source, when the data are retrieved, ALL the columns in the row retrieved from the data source become part of the data set, even those not referred to in the template. This issue is discussed in detail in XML and Data Sets, and also in this article.
Selecting a Range of Data
Two data base tables can be related in a master/detail relationship. An example of this is an account (the master) and the transactions on that account (the detail). If an interview has already captured the values for data elements StartDate and EndDate, and assuming that the data source Accounts retrieves the value for data element AccountNumber then the following commands will retrieve the account details that the user chooses, as well as the transaction details for transactions within the date range specified by StartDate and EndDate:
«ChooseFromDataSource(Accounts,Choose the account,RefreshSave,,AllowSearch)»
«LinkToDataSource(XDAccounts,Transactions,NoRefresh,range=AccountNumber = '<AccountNumber>' AND TransactionDate >= #<StartDate># AND TransactionDate <= #<EndDate>#)»
Note (1) the # delimiters around the dates: these are necessary if the data source is an Excel spreadsheet or an MS Access database. Where the underlying database is MySQL or SQL Server, the delimiter is not the # character but the single quote (as in the example where <AccountNumber> is enclosed in single quote marks).
Note (2) the data element called “XDAccounts” above – that is a data element which is created by the ChooseFromDataSource command and it is that data element which appears in the interview and thus needs the LinkToDataSource attached to it. The name is created from the data source name (i.e. “Accounts”) with “XD” prefixed to it.
