Help with creating a VF Email Template (Sales Orders / Sales Order Lines - Master Detail Relationship)

Hi, I am trying to create a Visual Force email template that would display recently shipped items and tracking.

From the App Exchange, I downloaded "Product Line Items in Email Templates."

Its semi helpful, but it includes Opportunity and Products which seems to be a related or maybe lookup relationship.

In our system, the related Objects are a Master Detail Relationship.
Accounts -> Sales Orders -> Sales Order Lines

I am trying to do two unique things.

1. In terms of who gets emailed, instead of a Contact, is it possible to send an email to an address in an email field on the Sales Order Record, and if that is blank/null, send it to an address an on the Account object?

2. The Sales Order Lines will likely include Products and tracking. (I might need to make additional tweaks)
To display Products, I'd like to show Sales Order Lines where Line Status equals Shipped today.
To display tracking, I'd like to show Sales Order Lines where Description starts with TRPK. (In our system, tracking lines display as TRPK 1Z11205, someting along those lines.)

If anyone can provide guidance to get me going, that would be appreciated.

Thanks,

Aron

April 26, 2016
·
Like
0
·
Follow
2

Best Answer chosen by Aron Schor [Dev]

Aron Schor [Dev]
This works!

<messaging:emailTemplate subject="{!relatedTo.Purchase_Order__c} Shipped" recipientType="User" relatedToType="Order_Header__c">
    <messaging:htmlEmailBody >
        <body style="font-family: 'Trebuchet MS'">
            <h4>
                Just letting you know Purchase Order - {!relatedTo.Purchase_Order__c},
                was shipped today.
            </h4>
            <hr style="border: solid thin #0099CC; margin-top: -18px;" />

            <br />

            <br />

            <table cellpadding="5" style="border-collapse: collapse" width="100%">
                <tr>
                    <td style="background-color: #0099CC; color: #FFFFFF">
                        <b>Stock Code</b>
                    </td>
                    <td style="background-color: #0099CC; color: #FFFFFF">
                        <b>Description</b>
                    </td>
                    <td style="background-color: #0099CC; color: #FFFFFF">
                        <b>Shipped Qty</b>
                    </td>
                    <td style="background-color: #0099CC; color: #FFFFFF">
                        <b>Status</b>
                    </td>
                </tr>
                <apex:repeat value="{!relatedTo.Order_Detail__r}" var="lineItem">
                <apex:outputPanel layout="none" rendered="{! lineItem.Line_Status__c != null}">
    <tr >
                        <td style="border-bottom: 1px solid #0099CC;">
                            <b>{!lineItem.Stock_Code__c}</b>
                        </td>
                        <td style="border-bottom: 1px solid #0099CC;">
                            {!lineItem.Description__c}
                        </td>
                        <td style="border-bottom: 1px solid #0099CC;">
                            {!lineItem.Shipped_Qty__c}
                        </td>
                        <td style="border-bottom: 1px solid #0099CC;">
                            {!lineItem.Line_Status__c}
                        </td>
                    </tr>
                </apex:outputPanel>
                </apex:repeat>
            </table>

            <br/>

            <h4>
                Tracking Code:
                <strong>
                    <apex:repeat value="{!relatedTo.Order_Detail__r}" var="lineItem">
                     <apex:outputText value="<BR />" escape="false" rendered="{!ISBLANK(lineItem.Stock_Code__c)}" />
                     {!IF(ISBLANK(lineItem.Stock_Code__c), lineItem.Description__c, NULL)}
                    </apex:repeat>
                </strong>
            </h4>
        </body>
    </messaging:htmlEmailBody>
</messaging:emailTemplate>

May 28, 2016
·
Like
0
·
Dislike
0

Elad Idan
1. You can use process builder with two paths, first to evaluate if the SO email record is not null, which will trigger the alert from the required field. Then if it is null, then use the account contact.

2. Can you share with us the code that you are working on, or the one that you're using as a template? It will help to evaluate what needs to be done.

April 26, 2016
·
Like
0
·
Dislike
0

Aron Schor [Dev]
For 1, can you send a screen shot of that? I see the option to select a template, not an option to select a field that would contain an email.

This is the template from the App, its probably going to need a lot of tweaking. Thanks!

<messaging:emailTemplate recipientType="Contact"
relatedToType="Opportunity"
subject="Productline Items for Opportunity: {!relatedTo.name}"
replyTo="support@acme.com" >

<messaging:htmlEmailBody >
<html>
<body>
<STYLE type="text/css">
TH {font-size: 11px; font-face: arial;background: #CCCCCC; border-width: 1; text-align: center }
TD {font-size: 11px; font-face: verdana }
TABLE {border: solid #CCCCCC; border-width: 1}
TR {border: solid #CCCCCC; border-width: 1}
</STYLE>
<font face="arial" size="2">
<p>Dear {!recipient.name},</p>
<p>Below is a list of products related to the Opportunity:<b> {!relatedTo.name}</b>.</p>
<br/>Account: <i> {!relatedTo.Account.name} </i>
<br/>Opportunity Owner: {!relatedTo.owner.name}
<br/>Opportunity Amount: {!ROUND(relatedTo.Amount,0)}
<br/>Opportunity Close Date: {!relatedTo.CloseDate}

<p/>
<table border="0" >
<tr >
<th>Action</th><th>Product Name</th><th>Quantity</th><th>Unit Price</th><th>Total Price</th>
</tr>
<apex:repeat var="opp" value="{!relatedTo.OpportunityLineItems}">
<tr>
<td><a href="https://na1-blitz01.soma.salesforce.com/{!opp.id}">View</a> |
<a href="https://na1-blitz01.soma.salesforce.com/{!opp.id}/e">Edit</a></td>
<td>{!opp.PriceBookEntry.name}</td>
<td>{!ROUND(opp.Quantity,0)}</td>
<td>{!ROUND(opp.UnitPrice,0)}</td>
<td>{!ROUND(opp.TotalPrice,0)}</td>
</tr>
</apex:repeat>
</table>
<p />
</font>

</body>
</html>
</messaging:htmlEmailBody>

<messaging:plainTextEmailBody >
Dear {!recipient.name},

Below is a list of products related to the Opportunity: {!relatedTo.name}

Account: {!relatedTo.Account.name}
Opportunity Owner: {!relatedTo.owner.name}
Opportunity Amount: {!ROUND(relatedTo.Amount,0)}
Opportunity Close Date: {!relatedTo.CloseDate}

[ Product Name ] - [ Quantity ] - [ Units ] - [ Total Price ]
-------------------------------------------------------------------------

<apex:repeat var="opp" value="{!relatedTo.OpportunityLineItems}">
[ {!opp.PriceBookEntry.name} ] - [ {!ROUND(opp.Quantity,0)} ] - [ {!ROUND(opp.UnitPrice,0)} ] - [ {!ROUND(opp.TotalPrice,0)} ]
</apex:repeat>

</messaging:plainTextEmailBody>

</messaging:emailTemplate>

April 26, 2016
·
Like
0
·
Dislike
0

Elad Idan
1. You'd need to create two separate email alerts, each will hold a different field as the email source. You can then choose the correct one for each node on the process builder.

2. I'm honestly not sure about the visualforce part, not my strong point. Hopefully someone will be able to offer some guidance.

April 26, 2016
·
Like
0
·
Dislike
0

Ohadios
Hi Aron,

For you number 1 - the solution Elad provided should work well.
I recommend that you just TRY to implement what he is suggesting - even if you are not 100% sure what you are doing... that's what sandboxes are for! It will probably give you a better idea of what questions to ask.

For you number 2- it's a bit difficult to answer fully without having some more details.
I assume that you will be sending an email with the "Sales Order" as the RelatedToType, and include informaiton from the Sales Order Line as the child object to iterate through (i.e. present multiple records of Sales Order Line).
You can't really put filters on the selection query with this method, but you can NOT DISPLAY the lines that you don't want.

For Product lines - I'm not sure what you mean by 'Shipped Today' - will you have a date field for shipment date? is this a status field that you are automatically updating somehow?

Are you planning to create separate SO Line for tracking?
why not include the tracking number on the product line as an additional field?

Finally - how exactly do you envision this thing will look? Do you have a mock-up?

Ohad

April 26, 2016
·
Like
0
·
Dislike
0

Aron Schor [Dev]
Hi Ohad.

1. I am not clear why there needs to be two email templates?
2. ShippedToday is an option for the Status field which appears in both the Sales Order Line and Sales Orders objects.
3. Good idea about adding tracking to Product Line, but sometimes orders have multiple tracking numbers and I can't exactly tie each number or a group of tracking numbers to a specific item. If someone buys 10 products, 1 product might have 10 related numbers and the remaining 9 might be associated with 1 tracking number.

Here is a sample email, showing what I'd like it to show and one with fields. I guess its a start?

Thanks!

Just letting you know Purchase Order ORD20160427, our Sales Order 355555 shipped today.

Here is what shipped
Widget111 Red Widget 100
Widget222 Blue Widget 75
Widget333 Green Widget 9

Here is tracking
TRKPR 01 1Z89000100300750500

----->Here is my best guess for what it would like with fields.

<messaging:emailTemplate recipientType="TransmissionEmail__C"
subject="Purchase Order Shipped"
replyTo="support@acme.com" >

<messaging:plainTextEmailBody>

Just letting you know Purchase Order Purchase_Order__c, our Sales Order Name (Name is the Field Name) shipped today.

Here is what shipped (Note Sales Order Line info below)
Order_Detail__r.Stock_Code__c Order_Detail__r.Description__c Order_Detail__r.Shipped_Qty__c
(Note, there can be numerous Sales Order Lines, each with a different Status. I'd want to only display Lines where Order_Detail__r.Line_Status__C = shipped.)

Here is tracking
Order_Detail__r.Description__C contains TRKPR
(Note, I plan on copying the tracking over to the Description field, I'd ideally only want the highest numbered Sales Order Line number in case there were multiple shipments)

<messaging:plainTextEmailBody >
</messaging:emailTemplate>

April 27, 2016
·
Like
0
·
Dislike
0

Browse by Topic

Welcome to Support!

Show

sorted by

Help with creating a VF Email Template (Sales Orders / Sales Order Lines - Master Detail Relationship)

All Answers

You need to sign in to do that.