When Is Direct SOQL Useful?
The S-Docs Direct SOQL feature is a valuable option when your standard related list option still does not meet your requirements.
The following are use cases where Direct SOQL may useful:
- The related list you want to include is not related to (no linked relationships exist) your underlying record. The underlying record is the value in the "related to type" field on the template record. For example, if you wanted to include a related list of similar Accounts (perhaps in the same industry) in your Account document.
- You have more than 100 records in the related list that you want to merge into your document. Direct SOQL is able to work with more records before hitting any governor limits.
- You want your related list to refresh with the latest data every time the S-Doc is VIEWED from the S-Docs related list. This is a critical distinction, as the document can change over time without being regenerated. If you want a point-in-time snapshot, you should also use the “additionally save an attachment” feature.
- You want to use very specific or advanced SOQL statements that may include features such as aggregates.
- You want to include the same related object multiple times in your template but have each occurrence use a different filter criteria or display different columns. While you can use the groupby in the standard related list feature to break a table by a data column, you have more control with Direct SOQL.
Direct SOQL key differences
- Uses <LineItemsSOQL> instead of <LineItems>.
- The <listname> should be set to the object API name, not the relationship name. This means that you should use Opportunity, not Opportunities, or OpportunityLineItem not OpportunityLineItems, or for custom objects you should use CustomObj__c not CustomObj__r.
- Direct SOQL data is not saved with the S-Doc snapshot record. This means that each time the s-doc is viewed within Salesforce, it is regenerated and therefore it executes the query and retrieves the latest data. If you email your document or save it as an attachment, then that document is unaltered because it is saved as a physical PDF or Word file. However, if you click on the View button within the S-Docs related list, the related list data is queried again, and may have changed since the document was originally generated. Therefore, we strongly recommend you check auto create salesforce attachment in the template editor in order to keep a hard copy of your document.
Using Direct SOQL
Open your template in the editor and then click on the Source button. You will need to add a section similar to the example below. Place it where you want the related list table of data to be inserted.
[code lang="html"]<table>
<tr><td>stage</td><td>name</td><td>Acct</td><td>amount</td></tr>
<!--{{! <LineItemsSOQL>
<class>table1</class>
<ListName>Opportunity</ListName>
<column>stagename</column>
<column>name</column>
<column>account.name</column>
<column format-number="##,###" prefix="$">amount</column>
<where>stagename in ('Prospecting','Closed') and amount>90000 </where>
<groupby>stagename</groupby>
<orderby>stagename, amount desc</orderby>
</LineItemsSOQL> }}-->
</table>[/code]
You can use this snippet above with any template because it runs as an independent SOQL query that returns the same data set regardless of the template used. The example above shows how you could select all opportunities that match a particular criteria (opportunity stage name and amount) and would include the retrieved records into a 4-column table within your document.
In this example, the query runs irrespective of your base record (that you used to generate the S-Doc from) or whether your base record is linked to these opportunities returned in the query or not. The SOQL query also runs under the users permissions and therefore does not violate any sharing rules.
If you want to limit the related list items to only those records linked to your specific record, then you would need to expand the <where> condition and specify that those line items have a lookup value equal to your specific record ID. For example, if you want to include only opportunities linked to your account record you would need to include a condition to filter Opportunity.AccountID value equal to your record Account.ID. Obviously, there needs to be some linked relationship field for this to work. To do this in the template, you need to specifically use the syntax '{!ObjectID}' that is dynamically replacing the record's Salesforce ID value. In our example, you would add the following: <where>AccountId='{!ObjectID15}'</where>
Advanced SOQL
If you want even more control, you can type the exact SOQL statement you want executed by adding a <SOQL> tag.
Adding the following into your template source (this example assumes your base object is opportunity) will sum your quantity and totals per product and return just one line by leveraging SOQL aggregate functionality to remove duplicates. You will note how the <SOQL> statement is used with an alias, and then matched to the columns.
[code lang="html"]<table border="0" class="table1" width="100%">
<tbody>
<tr>
<td class="table1header">
Product</td>
<td class="table1header">
Quantity</td>
<td align="right" class="table1header">
Total Price</td>
</tr>
<!--{{! <LineItemsSOQL>
<class>table1</class>
<ListName>quotelineitem</ListName>
<soql>Select PricebookEntry.product2.name, sum(o.TotalPrice)tp, sum(o.Quantity)tq, o.PricebookEntryId From OpportunityLineItem o where opportunityid='{{!ObjectID15}}' group by pricebookentryid, PricebookEntry.product2.name</soql>
<column>Name</column>
<column format-number="#,###" prefix="$">tq</column>
<column format-number="#,###.##" prefix="$">tp</column>
</LineItemsSOQL> }}-->
</tbody>
</table>[/code]
The results from the SOQL query will be matched to the column names. Here we used aliases tp and tq that mapped to columns. You don't need to map all columns. If you use this option, you need to correctly create the SOQL query to include required where, group by and order by syntax.
If you want to display data without the table formatting, you can specify <class>none</class>. This will remove all table, row and cell formatting and will return just the data. Here is an example that will return just names delimited by a comma.
[code lang="html"]<td> Account Team Members:
<!--{{! <LineItemsSOQL>
<class>none</class>
<soql>
select user.name from accountteammember where accountid='{{!account.id}}'
</soql>
<column postfix=", ">user.name</column>
</LineItemsSOQL> }}-->
</td>[/code]
However, consider the event in which the SOQL query returns no records for a certain document; we wouldn’t want “The Account Team Members are:” to be displayed when there aren’t any account team members! To solve this, we can use the prefix and postfix tags. Text placed between these tags will not render if no SOQL queries are returned. Use prefix tags for text that precedes the SOQL queries, and use postfix tags for text that follows the SOQL queries. Consider the following simple example:
[code lang="html"]<!--{{! <LineItemsSOQL>
<prefix>The Account Team Members are: </prefix>
<class>none</class>
<soql>
SELECT user.name FROM accountteammember WHERE accountid='{{!account.id}}'
</soql>
<column postfix=", ">user.name</column>
<postfix>. Please see page 3 for their contact information.</postfix>
</lineitemsSOQL> }}-->[/code]
Now, consider the following use case: we would like to prompt users to take note of a tracking number after a shipping confirmation. However, if they do not have a tracking number, we do not want to tell them to take note of a non-existent tracking number:
[code lang="html"]<p>Your order has been shipped.</p>
<!--{{! <LineItemsSOQL>
<class>none</class>
<prefix>
<p><span style="font-family:arial,helvetica,sans-serif;">
Please take note of your tracking number:
</prefix>
<soql>SELECT (SELECT id FROM opportunitylineitems WHERE PricebookEntry.Product2.productcode like '%ITG' limit 1)
FROM opportunity WHERE id='{{!ObjectID15}}' AND id IN (SELECT opportunityid FROM opportunitylineitem WHERE opportunityid='{{!ObjectID15}}' AND PricebookEntry.Product2.productcode like '%ITL') LIMIT 1
</soql>
<column prefix="<span style='display:none'>" postfix="</span>">sub.opportunitylineitems.id</column>
<postfix>
. Please use our order-tracking service to track your order.</p><p><span style="font-family:arial,helvetica,sans-serif;">
</span>
</p>
</postfix>
</LineItemsSOQL> }}-->
<p>Thank you for your business.</p>[/code]
In the above template, the customer will see “Your order has been shipped” and “Thank you for your business” even if they don’t have a tracking number, since these are entirely outside the LineItemsSOQL tags. “Please take note of your tracking number:” will appear before the tracking number and “Please visit our order-tracking website to track your order” will appear after the tracking number if the customer has a tracking number, and these two statements will not appear if the customer does not have a tracking number.
We also see that the text between the postfix tag begins with a period and a space, and ends with a closing paragraph tag but does not begin with one. This is because the opening paragraph tag is at the very beginning of the prefix. This means that the prefix, SOQL return statement, and the postfix will all appear in the same paragraph. Then, since the postfix begins with a period and a space, there will be a period and a space between the tracking number and “Please visit our order-tracking website to track your order,” since they are in the same paragraph. Thus, if the tracking number is 098726928, the resulting document will appear like so:
Your order has been shipped.
Please take note of the following tracking number: 098726928. Please use our order-tracking service to track your order.
Thank you for your business.
Similarly, if there is no tracking number, the resulting document will be:
Your order has been shipped.
Thank you for your business.
S-Docs also supports complex SOQL statements with sub-queries. You can include a related list of child record, along with its child records. You do this by including a standard SOQL subquery, and a column name that begin with “sub.” to indicate sub query data. Note that only one sub query is allowed here. Additionally, subquery items need to appear after all parent columns are processed; the capability to insert them in between parent columns is not supported. Here is an example that selects the chatter feed for a record along with the feed comments for each chatter record:
[code lang="html"]<table align="left" class="table641">
<tbody><!--{{!
<lineitemsSOQL><class>table641</class>
<listname>feeds</listname>
<soql>
SELECT createdby.name, Body, createddate,
(SELECT createdby.name, createddate,CommentBody FROM FeedComments)
FROM FeedItem WHERE ParentId = '{{!ObjectID15}}'
ORDER BY createddate desc</soql>
<column header="Created By">createdby.name</column>
<column header="Date" format-date="M/d/yy HH:mm">createdDate</column>
<column newrow="true" colspan="2" type="rtf">body</column>
<column header="Comments">sub.FeedComments.createdby.name</column>
<column header="" format-date="M/d/yy HH:mm"> sub.FeedComments.createdDate</column>
<column newrow="true" colspan="2">sub.FeedComments.CommentBody</column>
</lineitemsSOQL>
}}-->
</tbody>
</table>[/code]
The output below shows each chatter feed record (white background), along with the feed comments (subquery records with gray background).
Final note: All SOQL runs as the running user. This prevents any user from viewing any information that they are not entitled to through Salesforce permissions (object level, record level and field level).
