Displaying dependent data from external data sources in repeating tables in InfoPath forms

Introduction

Displaying dependent information from external data sources is a very common requirement for InfoPath forms in SharePoint projects. Some scenarios can be easily covered with out-of-the-box means; others are trickier especially in web compatible forms. Below is a description of simple solution for displaying related information from external data sources in repeating tables.

Problem Description

This requirement occurred in one of real projects related to building time registering system based on SharePoint with extensive use of InfoPath forms for implementation of timesheets. There were lots of requirements to those timesheets like different versions for different departments, calendars with bank holidays, extra security for sensitive data and so on. 

One of the requirements was to display descriptions for specific absence reasons.  Here is a brief description of the problem and fragment of structural model. 

This is a fragment of structural model for the timesheet (this is just logical entity model with no implementation details yet). Timesheet contains a list of Absences. Each Absence has a reference to Absence Reason, which has a title and description.

Timesheet is implemented as InfoPath form. The requirement is to maintain a table of absence reasons selecting absence reason from drop-down with all absence reasons available in the system. When an absence reason is selected for record, comments corresponding to the selected reason should appear in another column in this table. 

All absence reasons available in the system are maintained in a SharePoint list.

Problem Solution

InfoPath form for Timesheet contains a repeating table for daily records where one of the properties is Absence Reason.
Absence Reasons lookup is added to the form as Data Connection to external source to the SharePoint list containing all values for absence reasons in the system.

Then in properties for field corresponding to Absence Reason in main data source (W1AbsenceReason) set default value as shown in figure below:

XPath expression for this value looks like this:
xdXDocument:GetDOM("Absense Reasons")/dfs:myFields/dfs:dataFields/dfs:Absense_Reasons/@Description[../@Code = current()/../my:W1AbsenseReason]

Limitations

This approach is compatible with Web forms, which makes it useful in wide range of applications. But be very careful with this approach on Web; when the form is rendered as a Web page and delivered to the browser, all data from external data source is loaded to the client, which can make the page too heavy if the number of records in referenced external data source is big enough.