Views:

Scenario Overview

In this scenario we need to send sales representatives a weekly reminder to update their open Opportunities.  

Below is a screenshot of what the email to a sales representative would look like:

N52 Formula Manager Solution

The Formula Manager solution works like this,

  • Schedule is setup to execute on a weekly basis. This schedule targets all Users with open Opportunities
  • This schedule executes a Workflow for each User
  • The first step of the workflow is to call a Process Genie formula to generate a HTML table which contains the list of Opportunities for that User
  • The second step is a Send Email step which sends the email with the generated HTML table from the previous step

The HTML table can be styled by modifying the default CSS web resource named 'north52_/css/htmltable'

Formula  Steps

The following set of steps outline how to create this Formula

  • Create a new formula of type 'Process Genie'
  • Set the Source Entity to 'User'
  • Copy and paste the formula below into the formula editor canvas 
  • Click Save
  • Create the Formula Detail record (see section below)

Formula

CreateHTMLTable(
  FindRecordsFD('GetOpenOpportunitiesForUser', true), 
  SetColumnOrder('name', 'customerid', 'estimatedvalue', 'statuscode', 'estimatedclosedate'), 
  'https://north52demo.crm4.dynamics.com/', 
  'north52_/css/htmltable'
)

 

Formula Detail Steps

The following set of steps outline how to create the Formula Detail record for this Formula

  • From the Fetch-Xml tab click the + button
  • Set the name to GetOpenOpportunitiesForUser
  • Copy and paste the fetch-xml below into the Query field
  • Click Save

Fetch - Xml

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="opportunity">
    <attribute name="name" />
    <attribute name="customerid" />
    <attribute name="estimatedvalue" />
    <attribute name="statuscode" />
    <attribute name="opportunityid" />
    <attribute name="estimatedclosedate" />
    <order attribute="estimatedclosedate" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="ownerid" operator="eq" value="@systemuserid@" />
    </filter>
  </entity>
</fetch>

Workflow Steps

The following set of steps outline how to create the workflow to create the email and link the Formula

  • Create a new Process of type Workflow called 'Send Open Opportunities Email'
  • Set the Workflow to be As an on-demand Workflow and uncheck the 'Start When: Record is Created'
  • Add a new step to the Workflow to call the North52 Process Genie
  • Open the step properties and set the Formula Shortcode to be the one on the formula you just created, in this case 'bGm'
  • Add a new step to the Workflow to Send an Email
  • Use the String Output from the Process Genie step to place the Generated HTML into the body of the email
  • Click Activate

Workflow Setup



Process Genie Configuration
 

Details of the Send Email Step

 

Schedule Steps

The following set of steps outline how to create this Formula

  • Create a new North52 Schedule
  • Set the Name to Weekly Open Opportunity Reminders for Sales Reps
  • Set the Frequency field to 'Weekly'
  • Set the Schedule Type to 'Workflow'
  • Set the Workflow field to the workflow created in the previous step: Send Open Opportunities Email
  • Set the Start DateTime to the desired date and time
  • Copy and paste the Fetch Xml Query from below into the Query field 
  • Finally to start the Schedule click N52 Commands > Start Schedule
  • To verify that it is up and running check the System Jobs view

Schedule Setup 



This query returns all Users who are owners of open Opportunities:

Schedule Fetch Xml Query

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="systemuser">
    <attribute name="systemuserid" />
    <link-entity name="opportunity" from="owninguser" to="systemuserid" alias="ab">
      <filter type="and">
        <condition attribute="statecode" operator="eq" value="0" />
      </filter>
    </link-entity>
  </entity>
</fetch>

Wizard - FindRecordFD

Please see below the wizard you can use to create the FindRecordFD() function call used in this formula. 

Note you will need to create the Fetch Xml GetCurrentStage beforehand.