This is a follow on scenario to xRM Formula #25 User Actioned Form Notifications.
Its business scenario was 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.
Now we want to enhance this so that there is a validation rule put in place that stops any user trying to break the above business requirement. This means no new orders should be created for Accounts with 50k+ of outstanding invoices & it must be validated in real-time.
N52 Formula Manager Solution
The Formula Manager solution works like this,
- We create a formula of type 'Validation' for the Order entity
- This formula will be executed whenever a new Order is Created
- When a user attempts to create a new Order it find the sum of outstanding invoices for that Account
- Any if it exceeds 50k then it will stop the Order creation & display an error message to the user
N52 Formula Manager Steps
- Create a new formula of type 'Validation'
- Set the Source entity to 'Order'
- Set the Source property entity to 'All Properties '
- Copy & paste the formula below into the formula description field & click save
- You are ready to test
if( (FindValue('account', 'accountid', [salesorder.customerid], 'accountcategorycode','NotFound') = 2 and Sum('Invoices.totalamount') > 50000), 'Unable to create an order for an account if its category is standard and has over 50k of outstanding invoices', 'NoOp')
Wizard - FindValue
Please see below the wizard you can use to create the FindValue() function call used in this formula.
Note 1 : to find the value for Equals Value, you will need to go to the Explore tab > Order > Customer