How To: Advanced Data Retrieval with Named Queries

Use aggregates, filters, offsets, and grouped results to return exactly the values you need from a named query.

Use this guide when basic named queries are not enough.

These patterns help you return totals, target specific rows, and reuse one result set in multiple places.

Before you start

Make sure you already know the basic named query structure:

• <queryname>YourQueryName</queryname>
• a valid <soql> block
• merge fields in the form {{!QueryName.FieldName}}

If you need a refresher, start with Creating Your First Named Query.

Pattern 1: Return aggregate values

Use aggregate SOQL when you need totals or counts in plain text.

aggregate-named-query.xml

<!--{{!
<lineitemsSOQL>
  <queryname>OpportunityStats</queryname>
  <soql>
    SELECT COUNT(Id) cid, SUM(Amount) asum
    FROM Opportunity
    WHERE AccountId = '{{!Account.Id}}'
  </soql>
</lineitemsSOQL>
}}-->

aggregate-output.txt

This account has {{!OpportunityStats.cid}} opportunities totaling ${{!OpportunityStats.asum #,###.00}}.

Rules to remember:

• Give each aggregate field an alias like cid or asum
• Reference the alias in your merge field, not the full function
• Add number formatting after a space when needed

Pattern 2: Target a specific row with offset

Use offset when your named query returns several rows but you need one exact result.

offset-named-query.xml

<!--{{!
<lineitemsSOQL>
  <queryname>Approvers</queryname>
  <soql>
    SELECT Name, Title
    FROM Contact
    WHERE AccountId = '{{!Account.Id}}'
    ORDER BY CreatedDate ASC
  </soql>
</lineitemsSOQL>
}}-->

offset-output.txt

First approver: {{!Approvers.Name offset="1"}}
Second approver: {{!Approvers.Name offset="2"}}
Third approver title: {{!Approvers.Title offset="3"}}

offset="1" returns the first row.

offset="2" returns the second row.

Pattern 3: Reuse one query with filter and offset

Use filter when you want multiple output variations from one query result.

Define the filter inside the named query block.

filter-named-query.xml

<!--{{!
<lineitemsSOQL>
  <queryname>FilteredItems</queryname>
  <soql>
    SELECT Name, TotalPrice
    FROM OpportunityLineItem
    WHERE OpportunityId = '{{!Opportunity.Id}}'
    ORDER BY TotalPrice DESC
  </soql>
  <filter id="1">TotalPrice >= 1000</filter>
  <filter id="2">TotalPrice < 1000</filter>
</lineitemsSOQL>
}}-->

Then call the filter in the merge field.

filter-output.txt

Highest line item over $1,000: {{!FilteredItems.Name filter="1" offset="1"}}
Second line item over $1,000: {{!FilteredItems.Name filter="1" offset="2"}}
Highest line item under $1,000: {{!FilteredItems.Name filter="2" offset="1"}}

Rules to remember:

• filter="1" points to <filter id="1">
• offset="1" returns the first matching row
• You can combine both on the same merge field

Pattern 4: Handle aggregate lookup fields carefully

Aggregate queries return aliases and grouped values.

That changes how you reference relationship fields.

Example query:

aggregate-relationship-query.xml

<!--{{!
<lineitemsSOQL>
  <queryname>AccountCounts</queryname>
  <soql>
    SELECT COUNT_DISTINCT(Id) cid, Account.Name
    FROM Contact
    WHERE AccountId != null
    GROUP BY Account.Name
    ORDER BY COUNT_DISTINCT(Id) DESC
  </soql>
</lineitemsSOQL>
}}-->

Use this output pattern:

aggregate-relationship-output.txt

Top account: {{!AccountCounts.Name}}
Distinct contact count: {{!AccountCounts.cid}}

In grouped aggregate results, reference the grouped field name directly.

Do not prepend the relationship path in the merge field.

Use {{!AccountCounts.Name}}, not {{!AccountCounts.Account.Name}}.

Common fixes

• Blank output: Make sure the field appears in SELECT
• Wrong row: Check your ORDER BY before using offset
• Filter not working: Match the merge field filter value to the <filter id>
• Aggregate error: Add aliases for aggregate functions
• Relationship field is blank in an aggregate query: Use the grouped field name directly

Start with the basic named query working first. Then add one advanced feature at a time.

Keep going

• Review Named Query Working Examples for more copy-paste patterns
• Review Named Query Structure for exact tag and merge field rules
• Review Named Queries in DOCX and PPTX for format-specific wrapping syntax