SageCRMFun.com

Table level scripts are the perfect compliment to client side actions such as create scripts and on change script because it is server side and allows you to run code based on inserting, updating, or deleting a record.  My goal was to update “Average Certainty”, “Weighted Average”, and “Total Forecast” on the ‘Company Summary’ screen every time an opportunity is created, updated, or deleted.  Once the “Show pipelines for Companies/People” is set to ‘Yes’ the Opportunity tab under company will display the pipeline and calculated fields for all opportunities.  These calculations are done by the Dll so performing the calculations and updating the fields in real time required a table level script under opportunity.

The fields above were created on the company summary screen and will hold our calculations that match the values on the opportunity tab.  I tried using the detached table level but I faced the same problem that I have faced in the past.  The values insert into the DelayedScriptTable, delete after the allotted time but the values on the company summary screen did not seem to update at all.  I have also seen bugs where the entries stay in that DelayedScriptTable and never get executed.  Sage support has informed me to stay away from Detached Table Level Scripts so I used a regular table level script here. 

PostInsertRecord()

function PostInsertRecord()

{

ccRec = eWare.FindRecord("opportunity",WhereClause);

var oppoid = ccRec.oppo_opportunityid;

var compid = ccRec.oppo_primarycompanyid;

var oppF = Values('oppo_forecast');

var oppAC = Values('oppo_certainty');

//SQL BELOW SETS THESE FIELDS WITH THE RECENT VALUE SO THE SELECT STATEMENT IS USING THE MOST RECENT AND REAL TIME DATA

var oppFSQL = "update opportunity set oppo_forecast = '"+oppF+"', oppo_certainty = '"+oppAC+"' where oppo_opportunityid='"+oppoid+"'";

eWare.ExecSql(oppFSQL);

var sql = "SELECT TOP 20 SUM((Oppo_Forecast/Curr_Rate) * 1.000000)  AS Total_Forecast, cast(SUM(Oppo_Certainty) as numeric)/count(oppo_primarycompanyid) as Average_Certainty, (SUM((Oppo_Forecast/Curr_Rate) * 1.000000)/count(oppo_primarycompanyid))*(cast(SUM(Oppo_Certainty)as numeric)/count(oppo_primarycompanyid))/100 as Weighted_Average  FROM vListOpportunity (nolock) LEFT JOIN Currency ON Oppo_Forecast_CID = Curr_CurrencyID LEFT JOIN Custom_Captions ON Oppo_Stage = Capt_Code WHERE Oppo_PrimaryCompanyId='"+compid+"' and ( Capt_Family = \'Oppo_Stage\' OR Capt_Family IS NULL) GROUP BY Oppo_PrimaryCompanyId ORDER BY 2 DESC";

var QueryObj = eWare.CreateQueryObj(sql);

QueryObj.SelectSql();

var WA = QueryObj.FieldValue('Weighted_Average');

var TF = QueryObj.FieldValue('Total_Forecast');

var AC = QueryObj.FieldValue('Average_Certainty');

var update = "update company set comp_totalforecast = '"+TF+"',  comp_weightedaverage = '"+WA+"', comp_averagecertainty = '"+AC+"' from company  where comp_companyid ='"+compid+"'";

eWare.ExecSql(update);

}

I use the “WhereClause” to get the opportunity record id  just created.  I used Values() in order to get the certainty and forecast values I just entered for that opportunity.  I have to set the forecast and certainty values ourselves because the Dll does not do the insert command until after the post insert table level script code is run.  Since I do a select statement that performs our calculations on all of the opportunities these forecast and certainty values need to be saved into the database before it is run.  This is one of the “bad” things about the PostInsertRecord because it requires us to manually update the opportunity table before CRM does it by design.  The “ugly” part about the InsertRecord is it runs code from the UpdateRecord function causing undefined for the Values() method.  Once the code does the calculation it updates the company table by inserting those calculations into the fields displayed on the company summary page.

UpdateRecord()

function UpdateRecord()

{

var compid =  eWare.GetContextInfo('opportunity', 'oppo_primarycompanyid');

var oppoid =  eWare.GetContextInfo('opportunity', 'oppo_opportunityid');

var oppF = new String(Values('oppo_forecast'));

var oppAC = new String(Values('oppo_certainty'));

if(oppF!='undefined')

{

//SQL BELOW SETS THESE FIELDS WITH THE RECENT VALUE SO THE SELECT STATEMENT IS USING THE MOST RECENT AND REAL TIME DATA

var oppFSQL = "update opportunity set oppo_forecast = '"+oppF+"', oppo_certainty = '"+oppAC+"' where oppo_opportunityid='"+oppoid+"'";

eWare.ExecSql(oppFSQL);

var sql = "SELECT TOP 20 SUM((Oppo_Forecast/Curr_Rate) * 1.000000)  AS Total_Forecast, cast(SUM(Oppo_Certainty) as numeric)/count(oppo_primarycompanyid) as Average_Certainty, (SUM((Oppo_Forecast/Curr_Rate) * 1.000000)/count(oppo_primarycompanyid))*(cast(SUM(Oppo_Certainty)as numeric)/count(oppo_primarycompanyid))/100 as Weighted_Average  FROM vListOpportunity (nolock) LEFT JOIN Currency ON Oppo_Forecast_CID = Curr_CurrencyID LEFT JOIN Custom_Captions ON Oppo_Stage = Capt_Code WHERE Oppo_PrimaryCompanyId='"+compid+"' and ( Capt_Family = \'Oppo_Stage\' OR Capt_Family IS NULL) GROUP BY Oppo_PrimaryCompanyId ORDER BY 2 DESC";

var QueryObj = eWare.CreateQueryObj(sql);

QueryObj.SelectSql();

var WA = QueryObj.FieldValue('Weighted_Average');

var TF = QueryObj.FieldValue('Total_Forecast');

var AC = QueryObj.FieldValue('Average_Certainty');

var update = "update company set comp_totalforecast = '"+TF+"',  comp_weightedaverage = '"+WA+"', comp_averagecertainty = '"+AC+"' from company  where comp_companyid ='"+compid+"'";

eWare.ExecSql(update);

}

}

I need to use an “If Statement” on the forecast or certainty value because of the “ugly” part of table level scripts mentioned above.  It will only run the code below the “if statement” if it is a true update on the record.  Essentially the same code in the InsertRecord is run in the UpdateRecord but I use GetContextInfo in order to get the opportunity and company id values.  It would be nice if they had a post update function that ran the code after the update statement because when looking at the SQL logs I discovered that the code runs before the values get updated in the opportunity table. 

DeleteRecord()

function DeleteRecord()

{

//WE HAVE TO ZERO OUT THE VALUES AND MINUS FROM THE COUNT IN THE SQL STATEMENT BECAUSE IT NEEDS TO DO THE UPDATES ON THE CALCULATION BEFORE THIS RECORD GETS DELETED

var compid =  eWare.GetContextInfo('opportunity', 'oppo_primarycompanyid');

var oppoid =  eWare.GetContextInfo('opportunity', 'oppo_opportunityid');

var oppF = 0;

var oppAC = 0;

//SQL BELOW SETS THESE FIELDS WITH THE RECENT VALUE SO THE SELECT STATEMENT IS USING THE MOST RECENT AND REAL TIME DATA

var oppFSQL = "update opportunity set oppo_forecast = '"+oppF+"', oppo_certainty = '"+oppAC+"' where oppo_opportunityid='"+oppoid+"'";

eWare.ExecSql(oppFSQL);

var sql = "SELECT TOP 20 SUM((Oppo_Forecast/Curr_Rate) * 1.000000)  AS Total_Forecast, cast(SUM(Oppo_Certainty) as numeric)/(count(oppo_primarycompanyid)-1) as Average_Certainty, (SUM((Oppo_Forecast/Curr_Rate) * 1.000000)/(count(oppo_primarycompanyid)-1))*(cast(SUM(Oppo_Certainty)as numeric)/(count(oppo_primarycompanyid)-1))/100 as Weighted_Average  FROM vListOpportunity (nolock) LEFT JOIN Currency ON Oppo_Forecast_CID = Curr_CurrencyID LEFT JOIN Custom_Captions ON Oppo_Stage = Capt_Code WHERE Oppo_PrimaryCompanyId='"+compid+"' and ( Capt_Family = \'Oppo_Stage\' OR Capt_Family IS NULL) GROUP BY Oppo_PrimaryCompanyId ORDER BY 2 DESC";

var QueryObj = eWare.CreateQueryObj(sql);

QueryObj.SelectSql();

var WA = QueryObj.FieldValue('Weighted_Average');

var TF = QueryObj.FieldValue('Total_Forecast');

var AC = QueryObj.FieldValue('Average_Certainty');

var update = "update company set comp_totalforecast = '"+TF+"',  comp_weightedaverage = '"+WA+"', comp_averagecertainty = '"+AC+"' from company  where comp_companyid ='"+compid+"'";

eWare.ExecSql(update);

}

The code varies the most in the DeleteRecord function because the code runs before the record is deleted so the calculation that is running and updating the fields on the company summary screen are using the same values that I had before.  I need the calculations to take place after the record is gone so I can get accurate numbers.  Unfortunately, there isn’t a post delete function either so I had to 0 out the forecast and certainty values and then do the calculations based on the total number of opportunities minus 1 accounting for this record I am about to delete.  Then I do the update command and the record gets deleted.

In conclusion, the table level script feature is “good” for running JSCRIPT/SQL commands to update data when you are on a different entities but the detached table level script has been proven to be “ugly” and the functionality in the table level script can be “bad”.  These are the work arounds I had to use but I am hopeful that Sage will improve the table level script feature in upcoming versions.

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.