No-code Customizations with North52 Formula Manager, Part 2: Line Items

In a previous post I wrote about how the North52 Formula Manager solution can help you build some common customizations that would otherwise require writing Javascript for Dynamics CRM entity forms. The scenarios included:

• Setting default values for lookup fields
• Dynamically changing the field requirement level
• Displaying popup warnings
• Launching dialog process windows

This time I will show a few examples of the Formula Manager capabilities that would typically fall into the domain of writing C# plugin code to enhance the business logic of Dynamics CRM. Being a functional CRM consultant that doesn’t normally deliver a single line of code (at least for production environments), these scenarios would traditionally require me to hand over the task to a CRM developer. What we’re about to see is an alternative method of creating just formulas with the graphical tools available in the solution package to get the same job done.

Updating Opportunity, Quote, Order or Invoice Products

In Microsoft Dynamics CRM 2011 you can trigger workflow processes on the line items of Opportunities, Quotes, Orders or Invoices. This allows you to execute custom logic when, for example, new Quote Product rows are added for a Quote record. Unfortunately what you cannot do with standard workflows is to actually update the line item record itself.

Well, this surely looks like a dead-end for most of the use cases you could think of for triggering the workflow process in the first place. If I wanted to, for example, pull information from the selected Product record and save it on the Quote Product’s fields, such as product description, vendor details or a line item number to sort the quote rows, this apparently cannot be achieved with CRM’s workflow functionality.

Let’s look at how we can solve the problem with Formula Manager instead. In our scenario we would like the description field of the Quote Product record to be updated with the description field contents of the selected Product record. What we therefore need is a “Save – To Current Record” type of formula that is triggered on the Create & Update events of the Quote Product entity. We can limit the update events only to the Existing Product field by setting the Source Property value. The Target Property of our formula should be the Description field.

Finally, we need the actual formula that will feed the values from the related Product record into the Quote Product’s description field. To retrieve the description field contents we’ll use the FindValue function and tell it to search for a Product record that has the same productid as the existing product selected on the Quote Product record. While we’re at it, let’s also ensure that the Quote Product actually has an existing product instead of a write-in product by verifying the productid field contents with the ContainsData function. The end result will look like this:

if(ContainsData([quotedetail.productid]), FindValue(‘product’, ‘productid’, [quotedetail.productid], ‘description’),’NoOp’)

Now when we navigate onto a Quote and add a new Quote Product record underneath it, the description field will inherit the value from the related Product and display it directly on the Quote Product form (assuming you’ve made this default field visible on the form in the first place).

Not that much more complicated than building a workflow rule, just as long as you familiarize yourself with how the formulas work and how to format the parameters required by the functions.

Creating Line Items

Let’s proceed with exploring the world of line item records in the sales process. Updating existing records automatically is a nice capability to have for sure, but what about automating the complete process of creating the line items for an Opportunity, Quote, Order or Invoice? For example, if we wanted to ensure that there is always a standard Delivery Fee or other persistent item included whenever we’re creating an Order of a specific type (or for a specific customer group), then this is another area where the out-of-the-box functionality of Dynamics CRM workflows can’t be used, since new Order Product records cannot be created through a workflow rule.

With Formula Manager we have no such limitations in place, rather we are free to create any type of records we want with the CreateRecord function. The example formula below was actually provided to me by North52’s John Grace to demonstrate the solution’s functionality. The Save – Perform Action formula is attached to the Create event of an Order record, which in turn triggers the creation of a new Order Product record. The contents of this auto-created line item can be defined either directly in the formula or alternatively queried with the FindValue function familiar to us from the previous example.

CreateRecord(‘salesorderdetail’,
1,
‘salesorderid.salesorder.’ + [salesorder.salesorderid],
‘productid.product.’ + FindValue(‘product’, ‘name’, ‘Bike’, ‘productid’),
‘uomid.uom.’ + FindValue(‘uom’, ‘name’, ‘Primary Unit’, ‘uomid’),
‘quantity.10’
)

Now, as a limited time special offer, any new Order that we create (or a Quote that we convert) will get 10 Bike products added onto it with their unit price dynamically retrieved from the Price List selected on the Order. What a sweet deal!

Cloning Records

For a Dynamics CRM administrator these type of automated steps can be really handy in enforcing business rules and ensuring correct entry of data onto sales records. If we’d show them to your typical salesman, though, he might not be so impressed with this type of detailed process automation functionality.

“Sure, it looks like we could save a few clicks with these formulas. But here’s the deal: many of the quotations I make are practically identical. They contain the same line items every time, with only some variations in quantity and discounts given. What I’d really want to do is simply select a quote I’ve created for another customer and create a copy of it. You know, the “Save As” button that’s found in all the other Office applications. Why couldn’t CRM also have that? It would be a huge time saver for me.”

Ever had this kind of a discussion with your CRM users? If you have, then you’ll surely appreciate the fact that Formula Manager provides a Clone function that allows you to create a new copy of any existing record. Not only that, but the function also clones all the related 1:N & N:N records, which means you can create a copy of both the Quote and Quote Product line items with a single function!

To get an understanding of how the function could be leveraged in delivering the “Save As” functionality your sales users have been asking for so long, watch this video that demonstrates not only the formula in action but also how you can use the Ribbon Workbench to build a custom Clone button for the required entities.

Think these type of features would be useful to have in your Dynamics CRM organization? Then go ahead and grab the fully functional Standard Edition of Formula Manager that allows you to have 10 active formulas at a time, for test or production use.