If you need to get data from a data source and have those data available in an interview (and hence in the data set), then the commands IncludeDataSourceData and ChooseFromDataSource would be what you would use.
If, while authoring the template, you come to a point where you need one or more the values of fields in a data source, and the ID of the data source is known to you, then you can use the GetDataSourceData() function to retrieve the field(s) in question.
In order to discuss these functions, imagine a scenario where you know the identity of the user who is running the template – you would use the WindowsLogonUser() function to find this. Imagine also that you have set up a data source with all of the users’ details in it (called “USERS”), where the ID of the data source is that username.
Getting the value of one field
At some point in the template you want to put the user’s first name (e.g. after “Dear”). This would be achieved like this:
«
GetDataSourceData(‘USERS’,WindowsLogonUser(),‘FirstName’)»
This will get the row from the USERS data source where the ID is the WindowsLogonUser, and retrieve the ‘FirstName’ column from that row and put its value into the document.
Getting more than one field value
Imagine now that you would like to, say, end off a letter template with all of the user’s contact information, like full name, email address, direct phone line, etc. You could issue a GetDataSourceData for each of these fields, but each call to the function will cause a new access to the database, which can be inefficient of resource usage and time.
There is another form of the GetDataSourceData() function which omits the field name, and when this call is executed it returns, not a field/column value, but an XML representation of the data source row. Then the new function GetXMLElementValue will be used to retrieve individual field values from that XML. An example of that would be:
…
«SetVr(‘xml’,GetDataSourceData(‘USERS’,WindowsLogonUser()))»Signed
«GetXMLElementValue(Getv(‘xml’),'FirstNames')» «GetXMLElementValue(Getv(‘xml’),'Surname')»
Email Address: «GetXMLElementValue(Getv(‘xml’),‘EmailAddress’)»
Direct Line: «GetXMLElementValue(Getv(‘xml’),‘DirectLine’)»
Complex Operations
If you are familiar with XPATH, then you can use XPATH functions to extract information from the XML string that has been retrieved. For example, in order to get the count of the number of Product elements in a retrieved XML string, this would be the code to use:
The number of products are: «
GetXPathValue(Getv(‘xml’),‘count(//Product)’).
You could even get the sum of the cost price for all the Product elements like this:
The total cost price of all products is: «
FormatNumber(GetXPathValue(Getv(‘xml’),‘sum(//Product/CostPrice)’)).
