Mail Merge Quote - Grouping Products by Type

As mentioned in a previous post, the Sales Proposal/Quote format requested by my users requires that products be separated into multiple tables, grouped by Product Type. Mail Merge is not the friendliest beast - so this one was a bit of a challenge. The solution this this involves a combination of new categorization fields in the Quote Product form, client-side javascript, and some mail merge tricks. 

The Goal:
Use a Mail Merge template to generate a proposal or quote in CRM, where products contained in the quote are grouped into a predefined (or custom - just to make it even for fun!) set of product type tables.

The Hangup:
In Word Mail Merge, special field codes are required to include information before and after the main set of data being merged - and I did not know of a way to repeat the merge several times within the document while filtering said data to only list those of the current table's product type. I was sure there must be a way... so I took to my good old friend Google.


The Workaround:
As it turns out, the solution is not to repeat the merge with filters; but instead use dynamically defined bookmarks to hold the current product's type for comparison to the next. For each merged item, if the product type changes a new table is created for the new product type. This of course, requires that the data set be sorted by product type so only one table for each type is created. 

Step One: CRM Form Customization

The merge data set is a directory of Quote Products associated with the Quote record in CRM. However, I want to group these products by Product Type, which is an attribute of the Product lookup defined in the Quote Product record. So, I needed to add new attributes to the Quote Product entity that will define their grouping inside the quote document, as well as the preferred sort of the groupings. Remember that although there is a predefined set of preferred product groupings, my users also wanted the ability to provide their own group names on a per quote basis for added flexibility. 

To the Quote Product entity, I added:

• Product Category (new_productcategory): a picklist including the predefined product group names and an 'Other' entry
• Group Number (new_groupnumber): an int for the sort ID
• Category (new_productsubject): a varchar for a write-in group name. 

I added these fields to the Quote Product form. We will use client-side scripting to populate these fields.

Step Two: Client-Side Scripting

I needed to populate my new fields automatically, based on the product associated with the Quote Product record (and it's Product Type). The Group Number is to be assigned based on the Product Category used, and Group Number and Category are disabled by default. If the user changes the Product Category to 'Other', then the Category write-in field is enabled and required. For simplicity within the mail merge template, I wanted the write-in Category field to always contain the Category or group name even when chosen in the picklist so I could avoid an extra IF clause in my merge field codes, so my code prefills the Category varchar attribute with the value chosen in the Product Category picklist.

Quote Product OnLoad

1:  function OnCrmPageLoad() {  
2:    
3:    crmForm.all.productid.attachEvent("onchange", OnProductChange);  
4:    crmForm.all.new_productcategory.attachEvent("onchange", OnCategoryChange);  
5:  }  
6:    
7:  function OnProductChange() {  
8:    
9:    // get current chosen product  
10:    var product = crmForm.all.productid;  
11:    if (product.DataValue != null) {  
12:      // if lookup form was used, get Product Type from lookup columns  
13:      var ptype = product.items[0].keyValues.producttypecode;  
14:      if (ptype != undefined) {  
15:        //Assign category based on Product Type  
16:        switch (ptype.value) {  
17:          case "Controllers & Accessories":  
18:            category = 1;  
19:            break;  
20:          case "Middleware":  
21:            category = 2;  
22:            break;  
23:          case "Enterprise Software":  
24:            category = 2;  
25:            break;  
26:          case "Custom Graphics":  
27:            category = 3;  
28:            break;  
29:          case "Engineering Services":  
30:            category = 4;  
31:            break;  
32:          case "Hardware":  
33:            category = 1;  
34:            break;  
35:          case "Warranty & Services":  
36:            category = 4;  
37:            break;  
38:        }  
39:        crmForm.all.new_productcategory.DataValue = category;  
40:      }  
41:      OnCategoryChange();  
42:    }  
43:  }  
44:    
45:  function OnCategoryChange() {  
46:    
47:    // Initialize category and group number text fields  
48:    var catPL = crmForm.all.new_productcategory;  
49:    var group = crmForm.all.new_groupnumber;  
50:    var catWriteIn = crmForm.all.new_productsubject;  
51:    
52:    // Set group number based on selected category  
53:    var catPLvalue = parseFloat(catPL.DataValue);  
54:    if (catPLvalue < 5) {  
55:      group.DataValue = catPLvalue;  
56:      catWriteIn.Disabled = true;  
57:      group.Disabled = true;  
58:      crmForm.SetFieldReqLevel(group.id, 0);  
59:      crmForm.SetFieldReqLevel(catWriteIn.id, 0);  
60:    } else {  
61:      // if category is Other, enable write ins and make required  
62:      group.DataValue = null;  
63:      catWriteIn.Disabled = false;  
64:      group.Disabled = false;  
65:      crmForm.SetFieldReqLevel(group.id, 1);  
66:      crmForm.SetFieldReqLevel(catWriteIn.id, 1);  
67:    }  
68:    // Set category text field based on selected category  
69:    switch (catPL.DataValue) {  
70:      case "1":  
71:        catWriteIn.DataValue = "Hardware";  
72:        break;  
73:      case "2":  
74:        catWriteIn.DataValue = "Software";  
75:        break;  
76:      case "3":  
77:        catWriteIn.DataValue = "Graphics";  
78:        break;  
79:      case "4":  
80:        catWriteIn.DataValue = "Services";  
81:        break;  
82:      case "5":  
83:        catWriteIn.DataValue = null;  
84:        break;  
85:    }  
86:  }  
87:    
88:  OnCrmPageLoad();  

Quote Product OnSave

Because we are working with fields that are disabled (read-only) on the form, we must force CRM to save their values.

1:    crmForm.all.new_productsubject.ForceSubmit = true;  
2:    crmForm.all.new_groupnumber.ForceSubmit = true;  

Step Three: Mail Merge Template Design
In simple form, the field codes for the mail merge template look as follows:

{ IF { MREGESEQ } = "1" "
{ MERGEFIELD Quote_Product_Category }

" }{ IF {MERGEFIELD Quote_Product_Quantity } > 0 "{ SET Place1 {MERGEFIELD Quote_Product_Category } }{ IF { Place2 } <> { Place1 } "
Subtotal: $

{ MERGEFIELD Quote_Product_Category }

({ MERGEFIELD Quote_Product_Quantity }) { MERGEFIELD Product_ID }: { MERGEFIELD Quote_Product_Amount }
" "
({ MERGEFIELD Quote_Product_Quantity }) { MERGEFIELD Product_ID }: { MERGEFIELD Quote_Product_Amount }
" }{ SET Place2 { MERGEFIELD Quote_Product_Category } }{IF { MERGEFIELD LastItem } = 1 "
Subtotal: $

Product Total: ${ MERGEFIELD Total_Detail_Amount }
" }

I've colored the sections for easier reading and understanding. Consider a quote with two groups of products: Hardware, and Software. In the above code, the blue fields indicate data displayed for the first group, and the green for and after the second group. If the data was sorted prior to merge so that all hardware products were first, and then all software products after, the output would look like the following:

Hardware

(1) PART_NUM_X: $300.00
(3) PART_NUM_Y: $2,000.00
Subtotal: $

Software

(2) PART_NUM_Z: $500.00
(1) PART_NUM_A: $1,000.00
Subtotal: $

Product Total: $3,800.00

This is obviously a very crude example, and can be made much more professional and filled out by adding tables, formatting and additional text in between the field codes appropriately. See http://support.microsoft.com/kb/294686 for more information.

Step Four: Sorting Records During Mail Merge
Finally, when a mail merge is run based on this template, the data needs to be sorted to make sure the product groups are listed together, with the groups in the desired order. This is where the custom Group Number field comes in. When the mail merge is initiated, the user is shown a list of all data to be merged. The user needs to click the Sort link - sorting first by Last Item (to make sure that the last item is always truly last) and then by Group Number.

Caveats:

There is still one hangup that remains with this solution, so it's not as elegant as I had hoped - but is workable. In CRM, the item tagged with the Last Item flag is always the last Quote Product record added to the Quote. I have not found a way of changing this to date. This means, that in order for your sort to work properly, users must make sure that the last product item they add to their quote be of the last sorted product group. So in the above example it would need to be a software product. If this is not done, say the last product added to the quote were a hardware product, you would end up with an additional Hardware list at the end of your quote containing that one last product (not ideal!).

In addition, you may have noticed that the users will need to fill in subtotal numbers prior to delivering the Quote, since there are no subtotal records for these groups to pull from CRM. With the proper Quote Reports made available to the user for reference (I built one that breaks the products out in the same way with subtotals calculated) this one is only minor.