Views:

Scenario Overview

In this scenario we would like to send nicely formatted quote emails to customers which contain the product line items and totals in a table. The out of the box method of running the Quote Report, saving as a PDF and attaching to an email can be replaced by simply running a workflow.

This is a fairly advanced formula scenario and requires HTML formatting knowledge if you wish to tweak the output from what we provide.

This same method could be used to create emails with Order and Invoice data, or wherever you need to build up an HTML table of data from a set of records.

North52 Decision Suite Solution

The solution is made up of 3 Process Genie formulas and a real-time workflow and works like this:

Workflow is triggered by the user on the relevant Quote record
The first step of the workflow generates a draft email record with the Quote ID as the Subject – we use this ID in the Process Genie formulas to identify the email to update
The second step of the workflow is a Process Genie step that references our 1st formula which contains the HTML table header (the table headings)
The third step of the workflow is a Process Genie step that references our 2nd formula which contains the HTML table rows (the product line items)
The fourth step of the workflow is a Process Genie step that references our 3rd formula which contains the HTML table footer (the subtotal, discount, freight, tax and total information)
The fifth step of the workflow is an update step to change the Draft Email Subject to something more meaningful. It also means that if the workflow is run again the formulas don’t try and update the wrong email record

What the workflow looks like to generate the email,

North52 Decision Suite Steps

The following steps outline how to create these formula:

HTML Table Header

Create a new formula of type Process Genie
Set the Source Entity to ‘Quote’
Name the formula ‘Quote - Process Genie - Table Header’
Copy and paste the formula below into the formula description field & click save
This formula uses the following functions
- UpdateRecord – to update the email description field with the table header content
- FindValue – to determine the ID of the email record to update, we find the relevant email using the Quote ID that was set in the subject of the email created in step 1 of the workflow
- SetAttribute – updates the description by appending the table header HTML to the current description contents (using the second FindValue function in the formula)
- ToString – to format the values returned by CRM into numbers with 2 decimal places ‘0.00’

Formula

UpdateRecord('email',
                      FindValue('email', 'subject',[quote.quotenumber],'activityid','',false),
                      SetAttribute('description', 
                                          FindValue('email', 'subject',[quote.quotenumber],'description','',true,false) +
                                              '<table style="border:1px solid #eeeeee;border-collapse: collapse;" width="600" border="0" cellpadding="0" cellspacing="0"><tr>' +
                                              '<th style="background:#e1e1e1; text-align:left; padding:3px" width="200">Product</th>' +
                                              '<th style="background:#e1e1e1; text-align:right; padding:3px" width="100">Price Per Unit</th>' +
                                              '<th style="background:#e1e1e1; text-align:right; padding:3px" width="100">Quantity</th>' +
                                              '<th style="background:#e1e1e1; text-align:right; padding:3px" width="100">Discount</th>' +
                                              '<th style="background:#e1e1e1; text-align:right; padding:3px" width="100">Extended Amount</th>' +
                                              '</tr>'
                                 ))

HTML Table Body

Create a new formula of type Process Genie
Set the Source Entity to ‘Quote’
Name the formula ‘Quote - Process Genie - Table Body’
Copy and paste the formula below into the formula description field & click save
This formula uses the following functions
- ForEachRecord – to loop through the Quote product line items and build up the HTML table rows
- FindRecords – to find the associated Quote product line items
- UpdateRecord – to update the email description field with the table row data (each product line item)
- FindValue – to determine the ID of the email record to update, we find the relevant email using the Quote ID that was set in the subject of the email created in step 1 of the workflow
- SetAttribute – updates the description by appending the table body HTML to the current description contents (using the second FindValue function in the formula)
- ToString – to format the values returned by CRM into numbers with 2 decimal places ‘0.00’

Formula

ForEachRecord(
 FindRecords('quotedetail', 'quoteid',[quote.quoteid], '*'), 
 UpdateRecord('email',
              FindValue('email', 'subject',[quote.quotenumber],'activityid','',false),
              SetAttribute('description', 
                          FindValue('email', 'subject',[quote.quotenumber],'description','',true,false) +
                           '<tr><td style="text-align:left; padding:3px">' +  
                           CurrentRecord('productdescription') + 
                          '</td><td style="text-align:right; padding:3px">' +
                          ToString(CDecimal(CurrentRecord('priceperunit')),'0.00') +
                         '</td><td style="text-align:right; padding:3px">' +
                         ToString(CDecimal(CurrentRecord('quantity')),'0.00') +
                         '</td><td style="text-align:right; padding:3px">' +
                         ToString(CDecimal(CurrentRecord('manualdiscountamount')),'0.00') +
                         '</td><td style="text-align:right; padding:3px">' +
                         ToString(CDecimal(CurrentRecord('extendedamount')),'0.00') +
                        '</td></tr>'
                                 ))
)

HTML Table Footer

Create a new formula of type Process Genie
Set the Source Entity to ‘Quote’
Name the formula ‘Quote - Process Genie - Table Footer’
Copy and paste the formula below into the formula description field & click save
This formula uses the following functions
- UpdateRecord – to update the email description field with the table footer content
- FindValue – to determine the ID of the email record to update, we find the relevant email using the Quote ID that was set in the subject of the email created in step 1 of the workflow
- SetAttribute – updates the description by appending the table footer HTML to the current description contents (using the second FindValue function in the formula)
- ToString – to format the values returned by CRM into numbers with 2 decimal places ‘0.00’

Formula

UpdateRecord('email',
    FindValue('email', 'subject',[quote.quotenumber],'activityid','',false),
    SetAttribute('description', 
                  FindValue('email', 'subject',[quote.quotenumber],'description','',true,false) +
                  '<tr>' +
                 '<td colspan="4" style="text-align:right; background:#e1e1e1; padding:3px">
                  <strong>Detail Amount:</strong></td>' +
                 '<td style="text-align:right; background:#e1e1e1; padding:3px">
                  <strong>' + ToString([quote.totallineitemamount],'0.00') + '</strong></td>' +
                  '</tr>' +
                  '<tr>' +
                 '<td colspan="4" style="text-align:right; background:#ffffff;
                  padding:3px">Less Discount %:</td>' +
                 '<td style="text-align:right; background:#ffffff; padding:3px">' +
                  ToString([quote.discountpercentage],'0.00') + '</td>' +
                 '</tr>' +
                 '<tr>' +
                 '<td colspan="4" style="text-align:right; background:#ffffff;
                 padding:3px">Less Discount:</td>' +
                 '<td style="text-align:right; background:#ffffff;
                  padding:3px">' + 
                  ToString([quote.discountamount],'0.00') + '</td>' +
                '</tr>' +
                '<tr>' +
                '<td colspan="4" style="text-align:right; background:#e1e1e1;
                 padding:3px">
                 <strong>Pre-Freight Amount:</strong></td>'   +
                '<td style="text-align:right; background:#e1e1e1; 
                 padding:3px"><strong>' +
                 ToString([quote.totalamountlessfreight],'0.00') + '</strong></td>' +
                  '</tr>' +
                  '<tr>' +
                '<td colspan="4" style="text-align:right; background:#ffffff;
                 padding:3px">+ Freight Amount:</td>' +
                  '<td style="text-align:right; background:#ffffff; padding:3px">' + 
                   ToString([quote.freightamount],'0.00') + '</td>' +
                  '</tr>' +
                  '<tr>' +
                 '<td colspan="4" style="text-align:right; background:#ffffff;
                  padding:3px">+ Tax Amount:</td>' +
                 '<td style="text-align:right; background:#ffffff; padding:3px">' +
                  ToString([quote.totaltax],'0.00') + '</td>' +
                  '</tr>' + 
                  '<tr>' +
                  '<td colspan="4" style="text-align:right; background:#e1e1e1; padding:3px">
                   <strong>Total Amount:</strong></td>' +
                  '<td style="text-align:right; background:#e1e1e1; padding:3px"><strong>' + 
                    ToString([quote.totalamount],'0.00') + '</strong></td>' +
                    '</tr>' + 
                   '</table><br/>'
                                 ))

Create the Workflow

Create a new workflow process for the Quote entity to run as an on-demand real-time process.

It will have the following steps:

The individual Process Genie steps will be configured as shown below. The short-code for each formula can be found in the top right hand corner of each formula.

Add a step to create an email record and set the following fields:
- Subject = Quote ID
- Description = the content you would like to appear

Add a step using the custom workflow activity ‘North52 Process Genie’ and insert the formula Short Code from the ‘Quote - Process Genie - Table Header’ formula in as a parameter
Add a step using the custom workflow activity ‘North52 Process Genie’ and insert the formula Short Code from the ‘Quote - Process Genie - Table Body’ formula in as a parameter
Add a step using the custom workflow activity ‘North52 Process Genie’ and insert the formula Short Code from the ‘Quote - Process Genie - Table Footer’ formula in as a parameter
Add an update step and update the Email record (created in the 1st step) subject • Save and Activate your workflow• Test on a new Quote record

Save and Activate your workflow
Test on a new Quote record
Adjust the HTML to suit your needs

Did you know?

North52 helps with Task Management in Dynamics 365

Whatever your process North52 can help with Task management and assignment. Some of the common areas we help streamline processes with business rules are:

Purchase Order Requests and Approvals
Double Blind Data Entry
Data/File Gathering
Customer On-boarding
Call Center Automation
Coordination of Front Office and Service People
Inbound Lead Management
Application Processing
Inventory Management
Supplier Vendor Portal
Purchasing
Invoice Tracking and Approvals

Learn more about the North52 Business Rules Engine

Keywords: Process Genie, CDecimal, ToString, FindValue, FindRecords, ForEachRecord, CurrentRecord, UpdateRecord, SetAttribute, Formula Manager

xRM Formula #015 - Sending HTML formatted Quote emails with a table of products

Scenario Overview

North52 Decision Suite Solution

North52 Decision Suite Steps

Formula

HTML Table Body

Formula

HTML Table Footer

Formula

Create the Workflow

Did you know?

North52 helps with Task Management in Dynamics 365