Working with LinkToDataSource – example with a highly normalised data base.
You need to access a database which has the following structure:
Table “Contact”
ContactFirstName
ContactLastName
ContactTitle
Address
City
Region
ContactTypeId
Table “ContactType”
TypeId
TypeDescription
TypeCode
You can see that the actual value of TypeDescription is not kept in the Contact table, but a link to it is kept in the Contact table via the column ContactTypeId which corresponds with the TypeId column in the ContactType table.
Now, there is some text in the template that looks something like:
Dear
«ContactTitle»«ContactLastName»We will take pleasure in meeting with you on
«FormatDate(AppointmentDate,'MMMM d, yyyy')»to conclude our contract.
«If(TypeCode = “CUST”)»Please make sure you bring your FICA details.«End()»…
It is clear that we would need to configure a data source to access the Contact table. Suppose this is done and the data source is called “Contacts”. We could then put a ChooseFromDataSource command in the template, expanding the above snippet to something like:
«ChooseFromDataSource(Contacts,Enter the contact Id or press the button on the right,RefreshSave)»Dear
«ContactTitle»«ContactLastName»We will take pleasure in meeting with you on
«FormatDate(AppointmentDate,'MMMM d, yyyy')»to conclude our contract.
«If(TypeCode = “CUST”)»Please make sure you bring your FICA details.«End()»…
The issue then is how to get hold of the ContactType information (so that the test for TypeCode will work correctly). One way would be to configure the ContactType table as a sub-collection of the Contact table in the Contacts data source (you can see an example of this in the configuration of the Northwind CustomerOrder data source in the My Documents\XpressDox\Samples folder). The problem with this is that, although it would work, it would make it appear that each row in a Contact table has multiple ContactTypes, which is not the case.
Another approach would be to go about it in this way:
-
- In addition to the Contacts data source defined above, define a ContactTypes data source, referencing the ContactType table, with an Id which is the TypeId column (fairly obvious).
- Now we need to select a kind of “trigger field” in the interview which will trigger the reading of that ContactTypes data source. A good candidate is the AppointmentDate data element, as it will appear in the interview after the fields referencing the Contacts data source. This is good, because when the Contacts data source is read, the data element ContactTypeId will be read from the Contacts data source, and it is the value of this ContactTypeId data element which is needed as the Id to retrieve the ContactType information.
- Then, use the following command:
«LinkToDataSource(AppointmentDate,ContactTypes,,Id=<ContactTypeId>)»
What will happen, then, is that when the user enters the AppointmentDate and moves the focus out of that AppointmentDate field in the interview, XpressDox will read the ContactTypes data source, using the value of the ContactTypeId data element as the Id, and retrieve the relevant row from the ContactType table. This will populate the TypeCode data element, and so the test against it will have the correct values to test against.
The reason for the Id=<ContactTypeId> 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 – and in this example, AppointmentDate is that data element, and obviously is NOT the Id of the ContactTypes data source.
Why not link to the ChooseFromDataSource?
In fact, there is no reason why not to link the ContactTypes data source to the data element of the ChooseFromDataSource command. Then the Contact Type information would be read “at the same time” as the Contact was chosen – or at least immediately after the Contact is chosen. The question is – what is the name of that data element? Because the command ChooseFromDataSource doesn’t specify a data element name, only a data source name. But in fact, for each ChooseFromDataSource command, XpressDox generates a dummy data element with name ‘XD’ followed by the data source name. So in this case the data element name is ‘XDContacts’. The ideal version of the above snippet would then be:
«ChooseFromDataSource(Contacts,Enter the contact Id or press the button on the right,RefreshSave)»
«LinkToDataSource(XDContacts,ContactTypes,,Id=<ContactTypeId>)»
Dear«ContactTitle»«ContactLastName»We will take pleasure in meeting with you on
«FormatDate(AppointmentDate,'MMMM d, yyyy')»to conclude our contract.
«If(TypeCode = “CUST”)»Please make sure you bring your FICA details.«End()»…
What about other linked database tables?
In practice, databases which are normalised as in this example will have numerous tables linked via “id” columns to the main Contact table. For example, the contact may have a home address, business address, postal address, billing address, etc. And in this kind of database, there would be a separate table “Address” containing only addresses, and in the Contact table, instead of there being columns containing the addresses themselves, there will be columns like “HomeAddressId”, “BusinessAddressId”, etc.
The way to handle this would be to configure a separate data source for each of the different address types, and use the configuration mapping feature to name the data elements uniquely – i.e. all the Home Address data elements would start with “Home”, all the Business Address elements would start with “Bus”, etc.
Then, if in a specific template you required the contact details as well as the business and postal address details (but not home address), then you would have a ChooseFromDataSource command to enable the user to select the contact, and then one LinkToDataSource command for the business address data source, and one LinkToDataSource command for the postal address details, something like this:
«ChooseFromDataSource(Contacts,Select the contact)»
«LinkToDataSource(XDContacts,BusinessAddresses,,id=<BusinessAddressId>)»
«LinkToDataSource(XDContacts,PostalAddresses,,id=<PostalAddressId>)»
