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:

Did you know?

North52's Data Packager helps move Portal configuration data between instances

North52 Data Packager can be used to easily manage the transport of portal data from one instance to another. Specifically for Dynamics/PowerApps Portals, we provide templates that let you quickly move all portal entity data with ease. You can move whole sites or only the records you specify, either manually or via FetchXML queries.

And, because we use the Solution framework for the export and import of Data Packager data, you can easily version each data package. This is especially useful if you need to build up a new dev/test portal to a specific version – just apply and execute the Data Packages you need.

Learn more about the North52 Data Packager