SageCRMFun.com

CRM has the ability to carry out a mail merge document for the following entities.

• Company
• Person
• Opportunity
• Quotes
• Orders
• Case
• Lead
• Custom Entities

Additionally, CRM comes with a template that can be used with “Quotes” and “Orders” to fill multiple line items into the merge document – but there are limitations.

What if your custom entity has multiple records that need to merge into line items?

In this example we have a custom entity called "Parts Used".   This entity appears underneath a tab, under Cases, called “Parts Used”.  Upon clicking the Tab a list of parts are displayed for the given Case.  Unfortunately we are unable to tweak the PanoplySalesQuote.doc to support multiple line items for our custom entity due to the design capabilities of CRM.  Although, we can create a merge document for the custom entity by tweaking the vMailMergeCase view to support our custom entity fields we will be unable to produce a document that merges multiple line items.

The ASP page that handles the SQL and HTML

The solution is an ASP page which can be printed just like a Word document.  The ASP page will perform a SQL statement that will display each record on a new line in our HTML form.  For example, if there are five parts used then it will write the five records on different lines in the HTML form.  If there is only one part used it will only write one line on the html form.  New lines are created based on the number of records.  We use a while loop to populate a new line for each record if it exists.

var  caseid = eWare.GetContextInfo("Cases", "case_caseid");

var qryobj = eWare.CreateQueryObj("SELECT * from caseproducts inner join newproduct on cp_productid = prod_productid where cp_deleted is null and cp_caseid="+caseid);
qryobj.SelectSQL();

while(!qryobj.eof)
{
Response.Write("<tr>");
Response.Write("<td class='style20'>"+ qryobj.FieldValue("cp_qtyint")+"</td>");
Response.Write("<td class='style22'>"+qryobj.FieldValue("prod_code")+"</td>");
Response.Write("<td class='style18'>"+qryobj.FieldValue("prod_name")+"</td>");
Response.Write("<td class='style25'> "+qryobj.FieldValue("cp_status")+"</td>");

Response.Write("</tr>");
 qryobj.NextRecord();
}

The line items that merge into the document

There were two items entered so when service form is selected both parts used on this case will populate in “Parts Used In This Service”. 

The sql code will run every time the form is accessed so if another part is added it will show up on the form.

Drawbacks

Although you are able to display this merged document on a tab and print it, this customization would not allow you to save it into a word document like the built in CRM functionality.