The Named Query Feature

The Named Query Feature: Assigning SOQL Queries To Merge Fields

In S-Docs, related lists and direct SOQL lists have a massive amount of flexibility in how they are displayed when you leverage advanced template features, such as using HTML table markup in your related list's prefix and postfix attributes.

However, there are rare occasions where this flexibility just might not cut it, as well as times where you might just want to use our direct SOQL feature to query fields from just a single object, and then use these fields over and over again in your template. Simply copying and pasting your direct SOQL code over and over is a messy, difficult-to-maintain solution that might even cause performance issues. However, there's no need to distress in times like these, because the S-Docs named query feature's got your back:

<!--{{!<LineItemsSOQL>
<class>none</class>
<queryname>myQuery1</queryname>
<soql>
SELECT quantity, listprice, totalprice, productcode
FROM opportunitylineitem
WHERE opportunity.id='{{!opportunity.id}}'
AND description='A Unique Description'
LIMIT 1
</soql>
</LineItemsSOQL>}}-->


<!--{{!<LineItemsSOQL>
<class>none</class>
<queryname>myQuery2</queryname>
<soql>
SELECT quantity, listprice, totalprice, productcode
FROM opportunitylineitem
WHERE opportunity.id='{{!opportunity.id}}'
AND description='Another Unique Description'
LIMIT 1
</soql>
</LineItemsSOQL>}}-->


Data for opportunity line item with description "A Unique Description":
Quantity - {{!myQuery1.quantity}}
List Price - ${{!myQuery1.listprice #,###.##}}}
Total Price - ${{!myQuery1.totalprice #,###.##}}}


Data for opportunity line item with description "Another Unique Description":
Quantity - {{!myQuery2.quantity}}
List Price - ${{!myQuery2.listprice #,###.##}}
Total Price - ${{!myQuery2.totalprice #,###.##}}}


Product codes of products ordered:
{{!myQuery1.productcode}}
{{!myQuery2.productcode}}


And let's just put the quantities and product codes here again, just because we can:
{{!myQuery1.quantity}} | {{!myQuery1.productcode}}
{{!myQuery2.quantity}} | {{!myQuery2.productcode}}

If you were to use the above code as your template source, then when you generated an S-Doc, the result would look something like the following:

Data for opportunity line item with description "A Unique Description": Quantity - 100 List Price - $20.00 Total Price - $2,000.00 Data for opportunity line item with description "Another Unique Description": Quantity - 300 List Price - $40 Total Price - $12,000.00 Product codes of products ordered: GC1050 GC1140 And let's just put the quantities and product codes here again, just because we can: 100 | GC1050 300 | GC1140

Notice how the LineItemsSOQL statements themselves don't display any results like you'd normally expect; when S-Docs sees that you used the named query feature in a LineItemsSOQL statement, it knows to hide the data that would normally be there, and instead opens the door to reference the data elsewhere as merge fields in your document.

On a technical note, we know in advance that each item we've queried in this example will only return a single result - you can assume that in our imaginary database, we only have one opportunity line item with description "A Unique Description" and only one opportunity line item with description "Another Unique Description."

You can additionally create named queries that return multiple records. You can filter them and access different records like so: {{!myQuery.Name filter="1" offset="2"}}. This would return the third record in the list that matches the criteria defined in filter 1.

<!--{{!<LineItemsSOQL>
        <class>none</class>
        <queryname>myQuery</queryname>
        <filter id="1">StageName='Closed' AND Name LIKE '%Test%'</filter>
        <filter id="2">StageName!='Closed' AND (NOT Name LIKE '%Test%')</filter>
        <soql>SELECT Name, StageName FROM Opportunity</soql>
        </LineItemsSOQL>}}-->{{!myQuery.Name}}<br />
        {{!myQuery.Name filter="1" offset="1"}}<br />
        {{!myQuery.Name filter="2" offset="2"}}<br />
        {{!myQuery.Name filter="1" offset="3"}}<br />

Note that the filters use SOQL filter syntax, not S-Docs RENDER syntax. Each filter is added to the base query you defined between the <soql> tags, and one query is run per filter. For example, two queries will be run in the above sample since two <filter>'s are defined.

Aggregate Queries

The named query feature also supports aggregate queries, such as COUNT and SUM. To reference an aggregate function result in a merge field, you would simply use the field alias corresponding to that aggregate function result. For example:

<!--{{!
<lineitemsSOQL>
<class>none</class>
<queryname>numAccounts</queryname>
<soql>SELECT COUNT(id) cid, SUM(Amount)asum FROM Opportunity</soql>
</lineitemsSOQL>
}}-->


This org has {{!numAccounts.cid}} Opportunities with a total Amount of ${{!numAccounts.asum #,###.##}}.

By now, you've probably noticed that what's great about this feature is that you only have to run one query per child record to be able to use a given child record's fields like you would any ordinary merge field; the fields support attributes like format-number, and the fields can be used over and over again wherever you'd like in the document.

*Note: This feature was previously called the Queryname feature.

Tags: , , ,

Was this helpful?