Adding Related Records to Reports

See also:

Advanced Template Creation

Adding related fields to a template is the same as adding native fields. You just select the "Insert Field" button on the editor toolbar, expand the Relationships node, then expand the relationship whose field you want to add. Then just click any field and it will be added to your template.

You'll notice that the placeholder for the field is in the format

{$relationship.field}

If you click the Preview button, you should see your report now includes data from your related record.

In this simple case only a value from the first related record is displayed. All subsequent records are omitted. Hence this simple method works great if either the relationship only has one record in it - or if you are only interested in showing the first record. But most of the time when you are designing templates that display related field information, you actually want all of the related records' values to be shown. And usually in some sort of list or table.

Creating a "Related" Table

In order to build an understanding of how the system works, we will begin by looking at the HTML source code for a template directly. You can view the HTML source in the template editor by clicking the Source button in the upper left. Click it again to toggle back to WYSIWYG mode. Later we'll see how to define these same constructs using the WYSIWYG editor without resorting to HTML directly.

Displaying a list of related values is actually quite easy. The report generator will look for a relationship attribute in table, , and tags. If it finds such an attribute it will render each row of the table body, or list once for each related record. Generally, then you would just create a table with a header and a single row in the body, and the report generator would know to re-render the single body row once for each related record.

Example:

<table relationship="Ingredientes">
<thead>
        <tr>
                <th>Ingredient Description</th>
                <th>Cost Per kg</th>
        </tr>
</thead>
<tbody>
        <tr>
                <td>{$Ingredientes.ing_descr}</td>
                <td>{$Ingredientes.cost1}</td>
        </tr>
</tbody>
</table>

In this example we have an HTML table that specifies that is should be used to render records from the Ingredientes relationship. The thead tag defines the heading. The tbody tag defines only a single row, but notice that this row contains placeholders for fields of the Ingredientes relationship. The report generator will loop through each record in the relationship when this report is run and render this row definition once for each record.

Creating a "Related" List

Creating a related list is almost the same as a related table. The only difference is that you use ol and ul tags and these have no headers as a table tag does.

E.g.

<ul relationship="Ingredientes">
    <li> {$Ingredientes.ing_descr} : Cost Per kg {$Ingredientes.cost1}</li>
</ul>

This produces the ingredients as an unordered list. Using an ol tag would have produced an ordered list.

Using the WYSIWYG Editor

Now that we can see the underpinnings of how related lists and tables work, we will learn how to add them using the WYSIWYG editor - skipping the HTML entirely.

Open the edit form for your report template and ensure that the HTML widget is left in WYSIWYG mode (i.e. not source mode).

1. Click in the editor to set the caret where you would like the table to be inserted. Then click on the Table button in the editor toolbar. This shoudl open a dialog as shown.
   
2. We are going to create a table with 2 rows and 2 columns, so enter "2" in the Rows field and "2" in the Columns field. Don't click OK yet.
3. We want the top row to actually be a header, so in the Header drop-down list, select "First Row".
4. Click the OK button. This should insert an empty 2x2 table into the editor.
5. Enter values for the header cells.
   
6. With the caret somewhere in the table (either the header or the body cells), click the Set Relationship button in the toolbar.
   
   This will pop up a small dialog box with a drop-down whose options are the relationships in the template's table (as specified by the field).
   
7. Select the relationship that you want this table to display. Then close the dialog box.
8. Finally, we populate the body of the table. Click in the first cell of the body, then click the Insert Field toolbar button. This will open the field browser.
9. Expand the Relationships node, then expand the node of the relationship that this table is to display. Finally select a field. This will insert a placeholder macro for that field in the table cell. Do the same for the 2nd column.
   

Let's check the Preview (i.e. click the Preview List button).

Presently since the report consists solely of a table of related records, the preview just shows a bunch of tables stacked on top of each other. In our case it is just a list of ingredients - but it doesn't say anything about which record those ingredients are related to (i.e. the source record). Let's add a heading to our template to show the name of the recipe.

1. Click in the editor before the related table that we inserted earlier, and press Enter to free up some space.
2. Now, with the caret on a line of its own at the top of the editor, click on the Insert Field toolbar button. This will open the field browser dialog.
3. Expand the Fields node and select a field that would work well as a title. You should see this field added to the editor as a placeholder macro at the top of the page.
4. Let's make the font on this title a little bigger so that it stands out. Select "Heading 1" from the Format menu (it likely just says "Normal" by default). This should make the type bold and big.
   

Now let's check the preview. It should look something like the following, with headings followed by a list of related records.

Troubleshooting

The following are some of the common problems that may be encountered when setting up related tables and lists in a template.

My Report Only Shows One Row For My Related Table

If your related tables are only being rendered as a single row when the report is run, it usually means that the relationship attribute has not been correctly applied to the table (or ul or ) tag. You can double check this by clicking the Source button on the editor toolbar to view your template HTML directly. The table tag should have a relationship attribute set to your relationship name. If it does not, that means that you need to either add it manually, or return to WYSIWYG mode, click the mouse in the table and click the Set Relationship button on the editor toolbar - then select the appropriate relationship.

My Related Tables Have Multiple Rows All With The Same Content

This can happen if the related fields in your table's body cells come from a different relationship than the relationship that is set for the table. Ensure that the relationship set for the table is the same as the relationship from which the fields in your body row originate from.

Field Grouping

The related lists and tables feature of templates is very useful for being able to display related details of the subject records for your reports. It may be tempting to use this feature as a means of grouping records together for reports. Indeed the example in this section almost looked like a report on the Ingredients table grouped by recipe. However it is better to use the grouping feature of the HTML Reports Module for grouping reports as it provides many more features in this area.

What's Next?

Grouping Reports

See also:

Advanced Template Creation