Creating new Data in a Data Source

With effect from version 6, XpressDox offers a number of features which help the template author to develop templates which add new data to data sources. For example, to open a new account in an accounting database.

The commands which are used to create data in a data source are the same as those which are used to retrieve data, viz. ChooseFromDataSource, IncludeDataSourceData, LinkToDataSource and LinkToDataSourceOnEnter.

These three commands include the AllowInsert option. When the user enters an Id for a data source row in the text box part of the ChooseFromDataSource control in the interview, if there is a row for that Id, then the ChooseFromDataSource works exactly as if the RefreshSave option had been provided – i.e. the data for that row are read from the data source, and the interview populated with the data element values retrieved; then if any of them have changed during completion of the interview, those changes are saved to the data source. If the user enters an Id for which there is no row in the data source, then the AllowInsert option will cause a set of empty data elements corresponding to what would have been read from the data source to be created, and the interview is populated (probably the interview will remain as it was as the data element values will all be empty). Thereafter the operation is the same as for a RefreshSave option – i.e. if any values are provided by the user in the data element fields which “belong” to that data source, then they will be written into a new row in the data source, using the Id which was captured (subject to the issues discussed below).

The operation of the AllowInsert is, then, quite straight forward. However, creating a new row in a data source table is usually surrounded by a number of business rules, and in the end the template author becomes more and more of a programmer in order to cater for all the various issues surrounding adding new data into data sources.

Setting the Id

Suppose the data source being used here represents a table in a database called “Accounts”, and the data source itself is also called “Accounts”. Suppose also that the Id of the data source is a column called “AccountNumber”.

Choosing a new Id in the interview

As outlined earlier, if a user wishes to open a new account, they have to know what account number to assign, and they need to know that it is a non-existent number. There are at least two ways that this can be achieved:

1. The user uses the “Search” button on the ChooseFromDataSource control in the interview to show all the rows in the data source.  As long as the template author configured the data source to show the AccountNumber in the search interface, this will be possible.  The user can then sort the rows retrieved by AccountNumber and find the last one and then select as their new number something bigger than what is displayed.This will probably work if the users know the account number structure, and as long as only one user is adding new accounts at a time – otherwise there is the risk that two users will try to open two new accounts with the same number.
2. A better approach will require some database design, and uses the Auto Number (in Access and MySQL) or IDENTITY (SQL Server) features.  This means that the database system will automatically assign the next account number when a new row is inserted. Usually these automatic numbers start at 1 (and if it’s optional to start at zero then the DBA should design it to start at some value other than zero), so the user need only type in the value 0 for the Id and a new row will be created.If you are unable to change table design in this way (e.g. if it is a database created for a third party accounting system), then a way around it is to create a “staging” table which is designed with its primary key as an AutoNumber/IDENTITY, and write a trigger which will take data inserted into the staging table and create a new row in the official Accounts table.

Setting the Id in the Data Set

The user chooses the new account number

When a new row is “retrieved” from the data source, all of the data elements which correspond to columns in the data source table will be empty. This does NOT include the column(s) for the Id – in our example the data element called AccountNumber.   XpressDox will populate the Id data element(s) with the value that the user supplied in the interview.

In addition to populating the Id data element, XpressDox will also create a new system data element called XDNewData<data-source-name>, which will be given the value ‘1’ for a new row, and an empty value for an existing row.  In our example, the data element would be called XDNewDataAccounts.

1. The template author is able to test whether a new row is being created or not.  Consider the following:
   «ChooseFromDataSource(Accounts,Enter a new number,AllowInsert)»
«Rule(XDAccounts,hard,XDNewDataAccounts = '1','You must enter an account number which is not yet in the system, not an existing number.')»
«OnExitSet(XDAccounts,XDAccounts,FootingText,(XDNewDataAccounts = ''),'This account already exists','',EvenWhenNotEmpty)»
   …
   …
   A test (either in an If or OnEnter/ExitSet or in a Rule) which compares the value of XDNewDataAccounts with the value ‘1’ will render true if the data retrieved for the ChooseFromDataSource were for a non-existent row.
2. Notice the OnExitSet – it will display a message under the ChooseFromDataSource control notifying the user of the existing number, before the Rule is executed.

Using the AutoNumber/IDENTITY feature

If you wanted to use ChooseFromDataSource and that the user could either find an existing row in the data source, or type in “0” to make a new row, then the processing would be as above.
However, using the Auto Number feature of the database, it is possible for the template author to know what the value of the Id for a new row is – it is always zero. It isn’t necessary to ask the user to type in a zero.

Consider the following:

«LinkToDataSourceOnEnter(AccountName,Accounts,AllowInsert,Id=0,(AccountName = ''))»



The LinkToDataSourceOnEnter will be executed when the focus enters the control for the AccountName data element in the interview. If the value of the AccountName data element is empty, then the data source is queried using the Id of zero. Because there is no row in the corresponding table with an Id of zero, an “empty” row is returned and added into the interview’s data set (i.e. all control for the data elements for that data source are populated set to empty).
When the interview is complete and the document assembled, XpressDox will populate the part of the data set which belongs to the new data with what was ultimately inserted into the data base.  In particular, the value of the Id data element will get the Auto Number value as calculated by the data base engine.  This means that the value can be used in the document being assembled.