Overview

The North52 business rules engine for Microsoft Dynamics 365 is perfect for applying rules to quotes. This example shows how you can calculate and assign discounts for Quote Products. 

We will be basing the discount on the Customer payment history for the last 3 months. 

The percentage of a discount will increase as the amount paid increases. This could easily be substituted with your own rules. 

 

Discount Rules

The following discount rules need to be applied to quote products:

Rule #	Paid invoices in last 3 Months	Discount Percentage to Apply
1	Less than $1,000	5%
2	Between $1,000 and $5,000	7%
3	Between $5,000 and $10,000	10%
4	Between $10,000 and $15,000	12%
5	Between $15,000 and $20,000	15%
6	Greater than $20,000	20%

North52 Decision Suite Solution

The North52 Decision Suite solution works like this:

• A Decision Table with 2 sheets is created that will trigger whenever the fields Quantity or Price per Unit on the Quote Products are changed
  • In one sheet the Decision Table will calculate how much the customer has paid in the last 3 months
    • It will do this using a FetchXML query to find the customer's paid invoices
  • Another Decision Table sheet will then use that calculation to work out how much discount should be applied. It will set the Discount field to the calculated amount

Set up Formulas

Create Formula 

• Create a new Decision Table of type Save - Perform Action
• Set Source Entity to Quote Line
• Click Create
• Change the name to Calculate Customer Discount
• Expand Source & Target
  • Ctrl-click Price per Unit and Quantity
• Double-click the Decision Table sheet name and rename it to Paid Invoices 
• Right-click on the Decision Table and select Insert > Insert Decision Table
• Rename this new sheet to Calculate and Set Discount
• Click Save

Sheet 1: Paid Invoices

We can calculate the total amount using a FetchXML query:

• Select the Paid Invoices sheet
• Select the Fetch-Xml tab
• Click the + button
  • For Name enter Total Amount paid by invoice in last 3 months
  • For Fetch Xml Query copy and paste the following FetchXML - note the {0} parameter for a dynamic query:

      <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="invoice">
        <attribute name="name" />
        <attribute name="customerid" />
        <attribute name="statuscode" />
        <attribute name="totalamount" />
        <attribute name="invoiceid" />
        <attribute name="north52_invoicedate" />
        <attribute name="north52_billtocontact" />
        <order attribute="name" descending="false" />
        <filter type="and">
          <condition attribute="statuscode" operator="eq" value="217890001" />
          <condition attribute="customerid" operator="eq" uiname="Test" uitype="account" value="{0}" />
          <condition attribute="north52_paymentdate" operator="last-x-months" value="3" />
        </filter>
      </entity>
    </fetch>
    

• Click Generate

• Select column A & B
• Right-click and select Delete > Delete Column
• Right-click and select Insert > Insert inline Calculation
• Select cell A2
• Enter Find amount paid by Customer in last 3 months
• Copy and paste the following function in cell A4:

  FindSumFD('Total Amount paid by invoice in last 3 months','totalamount','0','true',SetParams([quotedetail.quoteid.customerid.?]))

• Click Save

Sheet 2: Calculate and Set Discount 

• Select cell A2
• Go to the Source tab
  • Click the refresh icon to the right of the search bar
  • Expand Decision Table Calculations > Paid Invoices
  • Select Find amount paid by Customer in last 3 months
• In cell A4 enter < 1000
• In cell A5 enter ((1000, 5000))
• In cell A6 enter ((5000, 10000))
• In cell A7 enter  ((10000, 15000))
• In cell A8 enter ((15000, 20000))
• In cell A9 enter > 20000
• Select column B
  • Right-click Insert > Insert Inline Calculation
  • Set the name to be Discount Percentage
• In cell B4 enter 5
• In cell B5 enter 7
• In cell B6 enter 10
• In cell B7 enter  12
• In cell B8 enter 15
• In cell B9 enter 20
• Select Save
• Select cell C2
  • Go to the Source tab and expand Source
  • Search for Manual Discount and select it
• Select cell C4 
  • Go to the Source tab
    • Expand Decision Table Calculations > Calculate Discount 
    • Click on Discount Percentage
  • On the Decision Table editor, click into cell C4
    • Enter * 
    • Go to the Source tab again. 
      • Expand Source
      • Find and select Amount
  • On the Decision Table editor, click into the cell C4
    • Enter / 100
• Drag this cell down to copy it into cell 5 - 9 
• Select Save

Testing 

• Open a Quote related to a company that has a number of paid Invoices in the last 3 months
• Then add a Quote Product to that Quote and check does the discount reflect the expected result: