Scenario Overview

In this scenario we have an Advertising company selling TV ads for a period of time with the need to calculate monthly pro-rata dates.

These TV ads were represented as Opportunities in the CRM system where you had a campaign start & end date.


N52 Formula Manager Solution



Formula Header

The Formula Manager solution works like this,

  • A formula is created which executes whenever an Opportunity is created or updated.
  • It checks to see if either of the following fields are changing before it will continue its execution, 'Campaign Start Date' and 'Campaign End Date'

Formula Body

This part of the Formula Manager solution describes the individual steps that make up the formula. When dealing with these complex formulas it is usually best to think of them like a CRM workflow where each line in the SmartFlow statement is like a step in a CRM workflow.

  • Find any existing records & delete them. Executes a fetch-xml statement to find the records.
  • Calculates the overall Number of Months involved
  • Loops over the number of months where it will create 1 Opportunity ProRata record per month


  • Add 2 new datetime fields to the Opportunity entity called 'Campaign Start Date' and 'Campaign End Date'
  • Create a new entity called Opportunity ProRata using the screenshot below as a reference

N52 Formula Manager Steps

  • Create a new formula of type 'Save - Perform Action'
  • Set the Event to 'Create & Update'
  • Set the Source Entity to 'Opportunity'
  • Set the Source Property to 'Campaign Start Date' and 'Campaign End Date'
  • Copy & paste the formula below into the formula description field & click save
  • Next scroll down to the formula detail grid & add a new record
  • Give it the name 'RemoveOldRecords'
  • Copy & paste the fetch-xml below into the query field & click save
  • You are now ready to test





     SetVar('NoOfMonths', DateDiff([opportunity.new_campaignstartdate], [opportunity.new_campaignenddate], 'pm') ), 
     DoLoop( GetVar('NoOfMonths') , 
                              SetAttribute('new_name', [] + ' - ' +  DoLoopIndex()),
                              SetAttributeLookup('new_opportunityid', 'opportunity', [opportunity.opportunityid]),
                                           if( DoLoopIndex() = 0 ,
                                               GetFirstDayOfMonth( [opportunity.new_campaignstartdate], DoLoopIndex()) ) ),
                                             if( DoLoopIndex() = GetVar('NoOfMonths')-1,
                                                  GetLastDayOfMonth( [opportunity.new_campaignstartdate], DoLoopIndex())))           


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="new_opportunityprorata">
    <attribute name="new_opportunityprorataid" />
    <attribute name="new_name" />
    <filter type="and">
      <condition attribute="new_opportunityid" operator="eq" value="@opportunityid@" />

Wizard - GetFirstDayOfMonth


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


Did you know?

North52 Quick Buttons can be used on Dynamics Portals Entity Forms

That's right, you can trigger complex business rules via a button on an Entity Form. You could:

  • Retrieve information from another entity or even another system and return it to the form
  • Create or Update records on the button click
  • Process advanced decision logic from information that has been completed on the form but not submitted
  • Have dynamic buttons showing different options dependent on your business rules
  • And much more!

Learn more about North52 Quick Buttons for Dynamics Portals