Views:

Overview

This example shows how you can calculate and assign discounts for your Quote Products

We will be basing the discount off the Customers 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. 

North52 BPA Solution

The North52 BPA 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 FetchXML
    • In another sheet the Decision Table will then use that calculation to work out how much discount should be applied. It will set the Discount field to the calculated amount. 

Formula

  • Navigate to Settings > N52 Formula
  • Click New
  • Set Source Entity to Quote Product
  • Set Formula Type to Save - Perform Action
  • Select Decision Table Editor
  • 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

FetchXML 

  • Select the Fetch-Xml tab
  • Click the + button
    • For Name enter Total Amount paid by invoice in last 3 months
    • For Query copy and paste the following FetchXML:
      <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 Save & Close

Sheet 1: Paid Invoices

Now that we have the FetchXML defined, we can calculate the total amount:
  • Select the Paid Invoices sheet
  • 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
  • Select cell A4
  • Copy and paste the following formula:
    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 the 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 

To test this in action:
  • 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: