function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
YukinonYukinon 

Dynamic SOQL Query in each opportunity record email template

I want to show different data in the email template depending on the opportunity I'm using it on. Is it possible?

For Example:
When I'm on this opportunity record then it should only show the first product on the email template since it's the only related product on this record.
User-added image

Controller:
public class ActiveOpportunityLineItems {
    private final List<OpportunityLineItem> lineItems;
    	
    public ActiveOpportunityLineItems(){
       lineItems = [SELECT ProductCode,UnitPrice,Description,Status__c From OpportunityLineItem WHERE Status__c = 'ACTIVE';
    }
    
    public List<OpportunityLineItem> getActiveOpportunityLineItems(){
        return lineItems;
    }
    
}

Component
<apex:component controller="ActiveOpportunityLineItems" access="global">
	<apex:dataTable border="below" value="{!ActiveOpportunityLineItems}" var="active_opp" width="75%" style="text-align:center;" >
        <apex:column value="{!active_opp.ProductCode}" headerValue="Product Name" />
	    <apex:column value="{!active_opp.Description}" headerValue="Line Description"/>
         <apex:column value="{!active_opp.UnitPrice}" headerValue="Sales Price"/>
         <apex:column value="{!active_opp.Status__c}" headerValue="Status"/>
	</apex:dataTable>
</apex:component>

 
mukesh guptamukesh gupta
HI Yukinon,

Please use below code:-

Controller:​​​​​​
public class ActiveOpportunityLineItems {
    private final List<OpportunityLineItem> lineItems;
    public String oppId {get;set;}
    public ActiveOpportunityLineItems(){
       oppId  = ApexPages.CurrentPage().getparameters().get('id');
       lineItems = [SELECT ProductCode,UnitPrice,Description,Status__c From OpportunityLineItem WHERE Status__c = 'ACTIVE' AND OpportunityId =: oppId ORDER BY ProductCode ASC LMIT 1;
    }
    
    public List<OpportunityLineItem> getActiveOpportunityLineItems(){
        return lineItems;
    }
    
}

COMPONENT:-
<apex:component controller="ActiveOpportunityLineItems" access="global">
	<apex:dataTable border="below" value="{!ActiveOpportunityLineItems}" var="active_opp" width="75%" style="text-align:center;" >
        <apex:column value="{!active_opp.ProductCode}" headerValue="Product Name" />
	    <apex:column value="{!active_opp.Description}" headerValue="Line Description"/>
         <apex:column value="{!active_opp.UnitPrice}" headerValue="Sales Price"/>
         <apex:column value="{!active_opp.Status__c}" headerValue="Status"/>
	</apex:dataTable>
</apex:component>

if you need any assistanse, Please let me know!!

Kindly mark my solution as the best answer if it helps you.

Thanks
Mukesh

 
mukesh guptamukesh gupta
HI Yukinon,

Please use below code:-

Controller:​​​​​​
public class ActiveOpportunityLineItems {
    private final List<OpportunityLineItem> lineItems;
    public String oppId {get;set;}
    public ActiveOpportunityLineItems(){
       oppId  = ApexPages.CurrentPage().getparameters().get('id');
       lineItems = [SELECT ProductCode,UnitPrice,Description,Status__c From OpportunityLineItem WHERE Status__c = 'ACTIVE' AND OpportunityId =: oppId ORDER BY ProductCode ASC LMIT 1;
    }
    
    public List<OpportunityLineItem> getActiveOpportunityLineItems(){
        return lineItems;
    }
    
}

COMPONENT:-
<apex:component controller="ActiveOpportunityLineItems" access="global">
	<apex:dataTable border="below" value="{!ActiveOpportunityLineItems}" var="active_opp" width="75%" style="text-align:center;" >
        <apex:column value="{!active_opp.ProductCode}" headerValue="Product Name" />
	    <apex:column value="{!active_opp.Description}" headerValue="Line Description"/>
         <apex:column value="{!active_opp.UnitPrice}" headerValue="Sales Price"/>
         <apex:column value="{!active_opp.Status__c}" headerValue="Status"/>
	</apex:dataTable>
</apex:component>

if you need any assistanse, Please let me know!!

Kindly mark my solution as the best answer if it helps you.

Thanks
Mukesh

 
YukinonYukinon
Thanks for the help but It's still not fetching the data. I'm using the code you sent me. 

User-added image