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.

N52 Formula Manager 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,

N52 Formula Manager 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