Use Google to find help fast. For example, search on "xpressdox choosefromlist".

Download the AdventureWorks database

Some of the sample templates which are delivered with XpressDox make use of one of the early instances of the Microsoft AdventureWorks SQL Server database.  This database can no longer be obtained from any Microsoft web site, and so a backup is made available here.

You can download either the .bak file itself, by clicking on the following

.bak: Download AdventureWorks.bak file

or, if preferred,

.zip: Download AdventureWorks.zip file

Once this has been downloaded, you can use the Microsoft SQL Server Management Studio, or any other suitable application, to install the database on a test system.

Using a Stored Procedure as a Data Source

This Cookbook article refers to the AdventureWorks database which needs to be installed at a location available to your development environment. If you have not already done so, you can download the database from Download the AdventureWorks Database.

It is assumed that you already know how to configure a datasource for a SQL Server database.  If not, it is strongly recommended that you do so.  You can follow the process by looking at the Cookbook articles Use a SQL Server Data Source to include Customer information on a template and/or Configure A Data Source for an Elite Database.

The process of defining a stored procedure as a data source is almost the same as that for defining a normal table or view as a data source, with the following differences:

  • Where the configuration wizard presents a choice of tables and views, it will now also include the stored procedures in that list.
  • Once a stored procedure is chosen, then configuration wizard will end, and give you the opportunity to modify the Definition String, using the Use Data Source’s  Editor button for that.

Some concepts to bear in mind are (these concepts are all illustrated in the sample template Using a Stored Procedure as a Data Source.xdtpx which is delivered into the My Documents\XpressDox\Samples folder by the XpressDox desktop installer):

  1. All data sources require that an “ID” be defined, by which unique items can be retrieved from a data source.  For a table or view, the ID is typically the primary key, or some other unique identifier such as an account number, or person number.  In the case of a stored procedure, the ID consists at least of the arguments to the stored procedure, but may have more components.  For example, in the case of the AdventureWorks database, the stored procedure dbo.uspGetManagerEmployees will get all the employees for a given manager.  In this case the ManagerID is the single argument which needs to be sent to the stored procedure, but since this stored procedure returns a collection of Employee objects, an extra item, viz. the EmployeeID, is necessary as part of the ID of this data source.  This is to make sure that the system knows how to distinguish between the multiple Employee objects returned by this stored procedure, which will have the same ManagerID.
  2. The usual data source retrieval commands can be used to get the data returned by a stored procedure.  The above-mentioned sample template uses the LinkToDataSource Command and this is arguably the command which suits stored procedures best.
  3. As template author, you should have knowledge of what kind of information a stored procedure returns.  If it returns a scalar value, or a number of scalar values (such as the Sales.uspGetEmployeeSalesYTD stored procedure used in the above sample), then the LinkToDataSource (or other data source command) should specify the “id=<EmployeeID>” type syntax.  If, however, the stored procedure returns a collection, then the “range=…” syntax should be used.  For example, if dbo.uspGetManagerEmployees is called using something like “id=<ManagerID>” in the data source command, then only the first Employee for that ManagerID will be returned.  Calling the dbo.uspGetManagerEmployees stored procedure should be initiated using the “range=<ManagerID>” syntax in the data source command.

The GetDataSourceData, GetXMLElementValue and GetXPathValue functions

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

Define Folder Shortcuts

Suppose you have defined a set of folders to contain clauses for insertion using the My Clauses or Shared Clauses features in the XpressDox ribbon.  Normally you would group clauses together into folders containing clauses for similar uses.  For example, they might be “Contracts”, “Notices”, “Letters”, etc.

In some cases the folder and sub-folder structure can get quite extensive.  In particular, when choosing a clause from a folder  deep in the folder structure, you might just want to be able to navigate quickly to another folder containing, maybe, more generic clauses.  In case these generic clauses are not visible in the XpressDox Explorer, you, as a template author, could create shortcuts to those generic folders in the following way:

  1. Make a text file where each line consists of a shortcut definition.
  2. A shortcut definition is a description of the folder, a pipe symbol “|”, and then the folder itself.
  3. The folder can be a full absolute path, or else a path relative to the folder in question – e.g. ..\..\GeneralClauses
  4. Save the text file with the name XDShortCutFolders.txt in the folder where you want the shortcuts to show.
  5. The XDShortCutFolders.txt file will not be visible to template runners, only to template authors.

An example of a shortcut file would be:

English Contract Clauses|..\..\English\Contracts
French Contract Clauses|F:\Clauses\Francaise\Contrats

The shortcuts will be sequenced alphabetically in the Explorer along with the normal sub-folders. If you want the shortcuts sorted at the beginning, for instance, then start the “description” part of the definition (the part before the “|” symbol) with one or more spaces.

Although this example relates to shortcuts and clauses folders, the shortcut feature applies to any folder which is displayed by the XpressDox Explorer.

The UpdateTableFormattingForPdf Function

Sometimes, not in every case, when using the RemoveColumnIf command, in conjunction with The SaveAsPDF Command, it can turn out the the PDF renderer does not render the table where the column has been removed correctly.

The UpdateTableFormattingForPdf function will usually fix this table rendering.

Suppose you have a command «RemoveColumnIf(Amount = 0,1)» inside a table in the template. If it turns out, by trial and error, that when saved as PDF the table is not rendered properly, then including the following fillpoint should fix the table format:

«UpdateTableFormattingForPdf(Amount = 0)»

Note that the condition passed to the UpdateTableFormattingForPdf must result in the same value as the condition passed to the RemoveColumnIf.

If the assembled document is not to be saved as PDF, then the UpdateTableFormattingForPdf can still be included in the template, but will have no effect.

The SecondsBetween Function

The fillpoint

«SecondsBetween(Later,Earlier)»

will return the number of seconds between the value in data element Later, and that in Earlier.

For example:

The race started at «ChooseUsingTimePicker(StartTime)»«FormatTime(StartTime,'hh mm tt')» and ended at «ChooseUsingTimePicker(EndTime)»«FormatTime(EndTime,'hh mm tt')». The elapsed time was «SecondsBetween(EndTime,StartTime)» seconds, which was not a new record.

The CultureName Function

This function can be used to take different action depending on the “culture” active at the time. All possible cultures are defined in Table of Language Culture Names, Codes, and ISO Values.

The function has two possible arguments, viz. “Input” (or just “In”) and “Output” (or “Out”)

«CultureName('Input')»

will return the culture which is used for capturing information in the interview.

«CultureName('Output')»

will return the culture which will be used, specifically for date and numeric information, when rendering this information in the assembled document.

The AlignErrorMessages Command

This command allows the template author for Web interviews to specify where the error messages for data elements in the interview should appear.

«AlignErrorMessages(Top)»

will cause any error messages to be displayed above the relevant data element in the Web interview, and the converse is true for

«AlignErrorMessages(Bottom)»