Cross-Functional Tabular Reporting
A Tabular Report displays a table of information from an EForm’s Records, with many options allowing for customized columns, filters, grouping, sorting rules and more. New in 4.12 is the ability to source data from another EForm in your Tabular Reports. This article describes the process for adding a ‘remote column ‘ to a Tabular Report. Remote columns can be a sub-table of related data. In this example, the ‘Pets’ column is a remote column that shows a list of Pets owned by each Person.
Remote Columns are a new type of column option in the Tabular Report’s Columns editor. To add a remote column to a Tabular Report:
Click the ‘Edit ‘ button in the ‘Columns ‘ section of the sidebar.
Click the red arrow to insert a Remote Column above the current row. The green arrow adds a normal column, and the bin icon removes the column.
Remote Columns have some different parts to regular columns:
Remote Columns can be given their own names – they cannot have the same name as a column on the local EForm.
In order to specify which records should be selected for the remote column, a Relationship Chain must be defined. The selection shown above describes that the Pets records are Child Records of the Person records.
The Data Selection part of a remote column allows you to specify which columns should be selected from the remote eform. This needs to be done after the relationship chain is specified.
The ‘Show View Links’ option allows remote column tables to display a clickable ‘View’ link which will take you to the relevant record.
A Relationship Chain is used to describe the relationship between the Source records and the desired Remote records . In the example above, the relationship is simple: Person records can have Pets records as children, so only one step is needed.
Here is a more complex example: For each Person record, display in a sub-table a list of pets in the same City, excluding pets the source Person owns.
To achieve this, a multi-step Relationship Chain is needed – first to select all the other people in the City, then to select their Pets.
The first step in this relationship chain is from the People EForm to the People EForm. Using the ‘Filter Only ‘ Relationship Type, all records that match the Conditional Filter Expression seen below will be selected:
This filter specifies that the selected records must have the same City as the source Person record, and not the same RecordID.
After selecting a set of People records that match the filter, the next step of the relationship chain selects the Pets of those People records.
In the resulting sub-table, Dieter’s two pet rats are not listed – instead, we have a list of all other pets in Perth.
Single-Value Remote Columns
If instead of seeing a list of the Pets, you’d like to simply see how many there are, you can create a Single-Value Remote Column. This is done in the Data Selection editor for a Remote Column – list a single column, and then check the ‘Show Aggregates Only?’ column. Make sure that the Aggregate is set to Count or something useful for the data you would like an aggregate for.