EDIT:
Is there a recommended way for a VF email template that uses a VF component to display in the SFDC UI such that you don't get the error message "Error occurred trying to load the template for preview: List has no rows for assignment to SObject. Please try editing your markup to correct the problem"
when saving the template? As the original problem description states below, I get this message when saving the VF template. The only workaround (see component controller code) that seems to work for me is to read a "test" opportunity such that the VF template has something to display, but this seems to be a complete hack – and I'm not guaranteed to have a "test" opportunity in any environment.
ORIGINAL:
I'm trying to use a component in a VF email template that queries opportunity line items from an opportunity lookup in a custom object. I get the error "List has no rows for assignment to SObject" if I don't query at least one opportunity object, which is the basis of my question.
The gist of the VF template is this;
<messaging:emailTemplate subject="Deal Desk Template Details: {!RelatedTo.Name}" recipientType="User" relatedToType="Deal_Details__c">
<messaging:htmlEmailBody >
<html>
<font face="verdana" size="1">
[...]
<c:findDetailData xstrOpptyId="{!relatedTo.OpportinityId__c}"/>
[...]
</font>
</html>
</messaging:htmlEmailBody>
</messaging:emailTemplate>
The Component looks like this;
<apex:component controller="findDetailData" access="global">
<apex:attribute name="xstrOpptyId" description="opportunity ID" type="String" assignTo="{!strOptID}"/>
<table>
<tr>
<td>Name</td>
<td>Quantity</td>
<td>UoM</td>
<td>NRC</td>
<td>Price</td>
<td>Variance</td>
<td>MRC</td>
<td>Waived</td>
</tr>
<apex:repeat var="opp" value="{!opptyLines}">
<td>{!opp.PriceBookEntry.name}</td>
<td align="right">{!ROUND(opp.Quantity,0)}</td>
<td align="left">{!opp.UoM__c}</td>
<td align="right">{!ROUND(opp.NRC__c,0)}</td>
<td align="right">{!ROUND(opp.Variance__c,0)}</td>
<td align="right">{!ROUND(opp.TotalPrice,0)}</td>
<td align="left">{!opp.Waived__c}</td>
</apex:repeat>
</table>
</apex:component>
And the controller is this;
public class findDetailData {
public List<OpportunityLineItem> opptyLines = new List<OpportunityLineItem>();
public String strOptID{get;set;}
public OpportunityLineItem ox = new OpportunityLineItem(Quantity=1,UoM__c='', NRC__c=0,TotalPrice=0,Waived__c=false);
public findDetailData () {
System.debug('Constructing findDetailData : ' + strOptID);
}
public void queryDetailData() {
<b>if(strOptID == null) { // <===== ADDED FOR PREVIEW MODE
Opportunity tempOppty = [SELECT Id FROM Opportunity WHERE Name LIKE '%Test%' LIMIT 1];
strOptID = tempOppty.Id;
}</b>
Opportunity lo;
lo = [SELECT Amount, Id, Name, (SELECT Quantity, NRC__c,
PriceBookEntry.UnitPrice, PricebookEntry.Name, UoM__c,
Variance__c, TotalPrice, Waived__c FROM OpportunityLineItems)
FROM Opportunity WHERE Id = :strOptID LIMIT 1];
System.debug('opptyLines.size(): ' + opptyLines.size());
For(OpportunityLineItem oli :lo.OpportunityLineItems) {
opptyLines.add(oli);
}
}
public void putstrOptID(String xs) {
strOptID = xs;
System.debug('opptyLines. ID: ' + strOptID);
}
public List<OpportunityLineItem> getopptyLines() {
System.debug('opptyLines(1) ID: ' + strOptID);
queryDetailData();
return opptyLines;
}
}
But what I don't like is I'm burning an extra SOQL statement just for preview of the VF page. If strOptID is null (during preview) I have to query an "artificial" opportunity that "should" be there ("%Test%")…..
I don't like this solution and I'm sure there's a better way, and I was hoping someone might point me to a more appropriate way to accomplish this.
Thanks in advance.
Best Answer
General rule - don't return result of SOQL to an object, but return it to a list of objects. After querying items, check the list.
Don't do
but do
In your case Opportunity. But you have two places where this error may appear.