How to Show Records With Their Linked Records and Totals

This guide shows you how to make a block query return rows of chosen fields, totals and linked Records, read in one request: for example, every Stage in its order, with the total value of its Opportunities and the Opportunities themselves listed under it.

Prerequisites

• An Experience open in the Experience Designer
• A Schema whose Records you want to list, linked to the Records you want to show under them (for example Stage, linked to Opportunity)

Step 1: Add the Query

1. Select the block that holds the list, for example a box.
2. In its settings, turn on Query and pick the Schema, for example Stage.
3. Set Order By and Order, for example Order ascending, and a Limit.
4. Optionally, add a Filter. It can use page data and experience parameters.

Step 2: Choose the Columns of Each Row

1. Under Output, set Results to Selected fields, aggregates and groups. The Output window opens. To change the columns later, click Edit output.
2. Under Columns, click Add field for each property to show. Its Output name is the property's name; change it if you want another, for example Name. When you pick another property, an output name that was the old property's name changes to the new one.
3. To show a total of linked Records, click Add aggregate. Choose the Function (such as Sum), the linked Records under Of (such as Opportunities), and the Property (such as Estimated TCV).

4. To list the linked Records, click Add records:

   1. Under Of, choose the linked Records, such as Opportunities.
   2. Under Fields of each record, click Add record field for each property to show, such as Name and Estimated TCV, and remove the id field if you do not need it. Keep it if the linked Records should be editable.
   3. Optionally, choose Sort by and Order, and set Limit: how many linked Records each row lists (5 by default, at most 50). A query has at most 5 records columns.
   4. Set its Output name, for example Opportunities.

The query's datum now holds one row per Record, with one value per column, under its output name.

Step 3: Show the Rows

1. Inside the block with the query, add a block for one row, such as a box, and turn on For Each. Under Reference, pick the query.
2. Inside it, add text blocks that show the row's columns: pick the For Each item and the column, such as Name or Total TCV.
3. To list the linked Records, add a block inside the row, turn on For Each, and pick the row's records column, such as Opportunities. Inside it, show the fields of each linked Record.

What Viewers See

• The whole list, with every total and linked Record, loads in one request, rather than one request per row.
• Rows, totals and linked Records include only the Records the viewer can read. When the viewer cannot read the linked Schema, its totals and lists have no value.
• Each row lists at most its records column's Limit of linked Records.

Limitations

• With totals or records columns, a query reads at most 100 rows: a larger Limit is lowered to 100.
• A query with selected columns cannot be a Live Query: it does not update when Records change until the page reloads.
• Aggregates and records columns of a block query cannot have their own filter. Filter the queried Records with the query's Filter.
• Rows can be edited only when the query selects the Record's id (and a records column, the linked Records' id); totals are read-only. See Editable Rows.

Related

• Record Queries
• The Record Query Shape
• How to Chart Grouped Records