Views:

Scenario Overview

In this scenario we have a fictitious Insurance company that rents out vehicles. The rental agreement between the Insurance company & the customer is represented by a Contract. And the Contract is made up of many (1:N) Rental Payments which are paid on a monthly basis. 

The business need here is to calculate the settlement amount when a contract is terminated early via an Early Termination Quote. The screenshot below shows where we have added two new sub-grids to the out of the box contract entity which represent, 
 

  • The Rental Payments associated to this contract
  • Any Early Termination Quotes associated to this contract

Business Rules

On the contract record an Early Termination Quote record can be added via the sub-grid.  The user will enter in a 'Last Rental Payment Date' on this record & click save. During the save process the settlement amount must be calculated based on certain rules, 

IF 4 or more Rental Payments are due after the Last Rental Payment Date THEN
      Find Max of either, 
                (-) 40% of Sum of Rental Payment Amounts after the Last Rental Payment Date + 
                    Early Termination Admin Fee
                (-) 4 times the Rental Payment Amount + Early Termination Fee
ELSE
     Sum of Rental Payment Amounts after the Last Rental Payment Date + Early Termination Admin Fee


* We assume all rental payment for a contract are the same value per month

Data Model

North52 Decision Suite Solution

The North52 Decision Suite solution works like this,

  • A formula of type 'Save To Current Record' is created that will execute whenever a new Early Termination Quote is created
  • The SmartFlow result will be placed into the Settlement Amount field on the Early Termination Quote record
  •  Next Calculate the Count of Remaining Months
  •  Next Calculate the Sum of the Remaining Amount
  •  Next Calculate four times the standard payment
  •  Next Calculate the Admin Fee
  • It then checks if the remaining months are greater than 4
  • If yes then it calculates the greater value of
    • 40% of the RemainingAmount + Admin Fee OR Four Times the standard payment + Admin Fee
  • Otherwise it rteurns the Remaining Amount + the Admin Fee

North52 Decision Suite Steps

The following set of steps outline how to create this Formula

  • Create a new formula of type 'Save To Current Record'
  • Set the Source Entity to 'Early Termination Quote'
  • Set the Event field to 'Create'
  • Copy & paste the formula below into the formula 
  • Click save & test

Formula

SmartFlow(

   SetVar('CountRemainingMonths', FindCountFD('RemainingRentalMonths', 'new_rentalpaymentid', '0' )), 

   SetVar('SumRemainingAmount', FindSumFD('RemainingRentalMonths', 'new_amount', '0' )), 

   SetVar('FourTimesPayment', 4 * FindValue('new_rentalpayment', 'new_contractid',
            [new_earlyterminationquote.new_contractid],'new_amount','0') ), 

   SetVar('AdminFee',   FindValueFD('GetAdminFee', 'new_earlyterminationfee', '0' )), 

   SmartFlowReturn(  if ( GetVar('CountRemainingMonths') >= 4 ,

                                  MaxOf( GetVar('AdminFee') + (GetVar('SumRemainingAmount')*.4)     ,
                                  GetVar('AdminFee') + GetVar('FourTimesPayment') ), 

                                  GetVar('SumRemainingAmount') + GetVar('AdminFee')
                         )
                 )
    ) 

Formula Detail - Remaining Rental Months 

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

  • Create a new formula detail record for the formula
  • Set the name to be 'RemainingRentalMonths'
  • Copy & paste the fetch-xml from below in the query field
  • Click save


RemainingRentalMonths - Fetch-XML

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="new_rentalpayment">
    <attribute name="new_rentalpaymentid" />
    <attribute name="new_amount" />
     <filter type="and">
      <condition attribute="new_contractid" operator="eq"  value="@new_contractid@" />
      <condition attribute="new_duedate" operator="on-or-after" value="@new_lastrentalpaymentdate@" />
    </filter>
  </entity>
</fetch>

Formula Detail - Get Admin Fee

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

  • Create a new formula detail record for the formula
  • Set the name to be 'GetAdminFee'
  • Copy & paste the fetch-xml from below in the query field
  • Click save

GetAdminFee - Fetch-XML

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="account">
    <attribute name="name" />
    <attribute name="accountid" />
    <attribute name="new_earlyterminationfee" />
    <link-entity name="contract" from="customerid" to="accountid" alias="aa">
      <filter type="and">
        <condition attribute="contractid" operator="eq"  value="@new_contractid@" />
      </filter>
    </link-entity>
  </entity>
</fetch>

Wizard - FindCountFD

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

Note that for Formula Detail (Query ) , you will need to have created the Fetch Xml beforehand. 

Did you know?

You can easily schedule recurring processes or CRM record updates

Every business has recurring processes and tasks that need to be done on repeatable and scheduled basis. Microsoft Dynamics 365 users are often surprised to learn that it is difficult to achieve using standard functionality. The North52 Scheduler makes it easy to trigger workflows and/or advanced business logic for specific entity records on a scheduled basis. 

Key features include:    

  • Create Recurring Schedules within Dynamics 365
  • Powerful Record Targeting - easily create queries to target a specific set of records
  • High Performance - can process 100,000+ records on a scheduled basis
  • Execute Workflow/Action or Update Entities
  • Native to Dynamics 365

Learn more about the Scheduler