Views:

Scenario Overview

In this business scenario you have a requirement that whenever a user opens an Account record or a user changes the category of a customer that you check the value of outstanding Invoices for that customer. And if the value is more than 50k then display a form level notification to the user stating not to create any more Orders for  this customer.


This is a sensitive requirement for the customer & so the query to find the sum of outstanding Invoices needs to be run in real-time, which is the default for North52 formulas..

N52 Formula Manager Solution

The Formula Manager solution works like this,

  • We create a formula of type 'ClientSide - Perform Action' for the Account entity
  • This formula will be executed for the OnLoad event of the form & for the OnChange event of the Category field.
  • The formula itself will run a query against the Invoice entity to Sum all the oustanding Invoices for the current customer.
  • If the value exceeds 50k then a Form Level Notification will be shown to the end user.

N52 Formula Manager Steps

  • Create a new formula of type 'ClientSide - Perform Action'
  • Set the Source Entity to 'Account'
  • Set the Source Property to 'OnLoad & Category' for the desired form and\or forms. (Use the Ctrl key to multi-select)
  • Copy & paste the formula below into the formula description field & click save      
  • Next click N52 Commands on the command bar & choose the 'Publish Formula' menu option. 
  • You are ready to test

Formula

if( ([account.accountcategorycode] = 2 and    FindSum('invoice',
                                                     'customerid',
                                                     [account.accountid],
                                                    'totalamount','0',true)    > 50000),                
     SetFormNotification(This standard customer is over the 50k invoice limit. 
                         DO NOT CREATE ANY ORDERS !!!, WARNING, msg1), 
    ClearFormNotification(msg1)
)

Wizard - FindSum

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