# How To: Advanced Data Retrieval with Named Queries

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:

* `<class>none</class>`
* `<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](broken://spaces/WKNnJmhJBQwhdk5WBFsi/pages/vyh6YXRukMRCqXK1Qzlo).

### Pattern 1: Return aggregate values

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

{% code title="aggregate-named-query.xml" %}

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

{% endcode %}

{% code title="aggregate-output.txt" %}

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

{% endcode %}

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.

{% code title="offset-named-query.xml" %}

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

{% endcode %}

{% code title="offset-output.txt" %}

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

{% endcode %}

`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.

{% code title="filter-named-query.xml" %}

```xml
<!--{{!
<lineitemsSOQL>
  <class>none</class>
  <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>
}}-->
```

{% endcode %}

Then call the filter in the merge field.

{% code title="filter-output.txt" %}

```plaintext
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"}}
```

{% endcode %}

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:

{% code title="aggregate-relationship-query.xml" %}

```xml
<!--{{!
<lineitemsSOQL>
  <class>none</class>
  <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>
}}-->
```

{% endcode %}

Use this output pattern:

{% code title="aggregate-relationship-output.txt" %}

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

{% endcode %}

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

{% hint style="info" %}
Start with the basic named query working first. Then add one advanced feature at a time.
{% endhint %}

### Keep going

* Review [Named Query Working Examples](broken://spaces/WKNnJmhJBQwhdk5WBFsi/pages/vNM7wQLhQBZd5wMjqqrQ) for more copy-paste patterns
* Review [Named Query Structure](broken://spaces/WKNnJmhJBQwhdk5WBFsi/pages/rSGzCvGz6aA4F09QF9oN) for exact tag and merge field rules
* Review [Named Queries in DOCX and PPTX](broken://spaces/WKNnJmhJBQwhdk5WBFsi/pages/aqrFMMmbfsrmI8OD34jb) for format-specific wrapping syntax


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://help.sdocs.com/sdocs/advanced-template-logic/named-query/how-to-advanced-data-retrieval-with-named-queries.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.