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
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) )