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.

Our development team utilizes several tools to accelerate and enhance the development of customizations. Whether we use Firebug for Firefox to troubleshoot JavaScript errors/oddities or PSPad to quickly mockup code, each tool fills a nitch, and is essential to delivering solid customizations. One tool we use for “super fancy” customizations involving asynchronous callbacks is Fiddler2.

What is Fiddler2?

Fiddler2 is a web debugging proxy. Simply, Fiddler2 allows web based traffic (HTTP and HTTPS) to be monitored, debugged, replayed, and mangled for your pleasure! 

Fiddler is a Web Debugging Proxy which logs all HTTP(S) traffic between your computer and the Internet. Fiddler allows you to inspect all HTTP(S) traffic, set breakpoints, and “fiddle” with incoming or outgoing data. Fiddler includes a powerful event-based scripting subsystem, and can be extended using any .NET language.

Fiddler is freeware and can debug traffic from virtually any application, including Internet Explorer, Mozilla Firefox, Opera, and thousands more.

In addition to the core features, Fiddler2, also has a 3rd party plug-in model, allowing users to extend the functionality of Fiddler2. All for FREE!

Why would I use Fiddler2?

A valid question. There are several tasks we perform with Fiddler2; here are a couple:

• Find Hidden and Unhidden Form Values – Form values can be hard to find. Clicking on the “WebForms” tab reveals hidden and unhidden Form values.
• Troubleshooting Asynchronous Calls – Asynchronous calls can’t be seen from the web browser. However, using Fiddler2, we are able to see the actual call to the backend page. We do this to a.) Ensure the call is being made and b.) Validate that the correct QueryString parameters are being appended.
• Accelerate Development – When testing, the time spent clicking through 2-3 screens, to reach a button or hyperlink you want to test can, can add up. The “Replay” feature in Fiddler2 speeds this process up. Allowing you to resend a previous request and view the results right inside of Fiddler2!

Use the right tool?

As with anything, using the right tool is essential. Although Fiddler2 may not be for everyone, we love it. In fact, in a post to come, we hope to show you how we used it to “work around” a bug in SageCRM.

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.

We love SQL. We love reading it, writing it, proofing it, improving it and sometimes dropping it (always sad). We also like to see our SQL dressed to impress- not looking flabby but lean and mean. One tool we use to buff and shine our SQL is "Instant SQL Formatter" (ISF). ISF, a web site created by Gudu Software, takes ugly SQL and makes it pretty. Why is this important? Frankly, it makes debugging and modifying existing SQL statements easier – and we like easy. Let’s see an example.

"It’s not that it’s ugly… it’s just not pretty"

Below we have "vSearchListCompany". This is a popular SQL view – used in a variety of Sage CRM functions. Consequently, this would be the same view that seemly breaks when applying patches – forcing one to limit the amount of fields returned in order to fix the error. But we can save that post for another day – back to "vSearchListCompany". As we see (screen shot) not all SQL statements are easy on the eyes. Attempting to add a column, modify the first name and last name concatenation, or removing a column can get messy. To make this a little easier, let’s use ISF.

"We are going to PUMP…. You Up!"

Now, at first visit, ISF can seem a little disorganized – offering a plethora of options. Side note: We use ISF’s impressive array of options for more than beautifying SQL. ISF can be a huge time saver for creating JScript or C# string variables based on your SQL statements -  I implore you to explore the settings. For this example, we are only concerned with two settings: Database and Output. These two settings inform ISF what database platform the SQL statement is coming from and what format to output the results. For this example, our database format is "SQL" and our output format is "SQL (Text)". Selecting "SQL (Text)" as the output format will deliver a SQL statement – executable in SQL Query Analyzer. After selecting the appropriate settings, take your SQL statement, copy and paste it into the provided text box and click "Format SQL".

"Looking SWEEEEET!"

After a few milliseconds, our new and improved SQL is unveiled. We can see (below) the resulting SQL is much easier to work with. Adding or modifying existing columns becomes a cinch. Additionally, the SELECTION fields along with the JOIN and WHERE clauses are easier to disseminate. From here, we typically copy the resulting SQL statement into SQL Query Analyzer and work with it from there. Also, it is not uncommon for us to keep the formatting provided by ISF when pasting BACK into Sage CRM. This way, if we need to make changes in the future, we are ready to go!

/* Powered by General SQL Parser (www.sqlparser.com) */

CREATE VIEW VSEARCHLISTCOMPANY
AS
  SELECT RTRIM(ISNULL(PERS_FIRSTNAME,'')) + ' ' + RTRIM(ISNULL(PERS_LASTNAME,''))                                                           AS PERS_FULLNAME,
         RTRIM(ISNULL(PERS_PHONECOUNTRYCODE,'')) + ' ' + RTRIM(ISNULL(PERS_PHONEAREACODE,'')) + ' ' + RTRIM(ISNULL(PERS_PHONENUMBER,''))    AS PERS_PHONEFULLNUMBER,
         RTRIM(ISNULL(PERS_FAXCOUNTRYCODE,'')) + ' ' + RTRIM(ISNULL(PERS_FAXAREACODE,'')) + ' ' + RTRIM(ISNULL(PERS_FAXNUMBER,''))          AS PERS_FAXFULLNUMBER,
         RTRIM(ISNULL(COMP_PHONECOUNTRYCODE,'')) + ' ' + RTRIM(ISNULL(COMP_PHONEAREACODE,'')) + ' ' + RTRIM(ISNULL(COMP_PHONENUMBER,''))    AS COMP_PHONEFULLNUMBER,
         COMPANY.*,
         PERSON.*,
         ADDRESS.*
  FROM   COMPANY
         LEFT JOIN PERSON
           ON COMP_PRIMARYPERSONID = PERS_PERSONID
         LEFT JOIN ADDRESS
           ON COMP_PRIMARYADDRESSID = ADDR_ADDRESSID
  WHERE  COMP_DELETED IS NULL

The tab for a custom ASP page will not stay highlighted when you jump to another asp page within. For Example: The screen shot below shows a custom asp page called "Logins". The tab is selected within that custom asp page but when you hit new it calls another asp page.

You will notice that the tab is not selected when the other asp page is called within the first asp page by pressing the new button.

There are a few ways to handle this problem.

1. You can just create a tab group and just call it within get page so it goes to it’s own tab group like Marketing.

   Response.Write(eWare.GetPage("YourTabGroup");

   

2. You can handle multiple views within 1 asp page so the tab is selected amongst the other tabs. The code below is an example of using if statements to handle different views and actions.

   <!-- #Include file = "accpaccrm.js" -->
   <%
   if(Request.QueryString("mode") == 'new')
   {
       varCancelButton = eWare.Button("Cancel","cancel.gif",eWare.Url("login.asp"));
       if(eWare.Mode == View)
       {
           eWare.Mode = Edit;
       }
       varLoginNew=eWare.GetBlock("LoginEntry");
       varThisCompany = eWare.GetContextInfo("Company","comp_companyid");
       LoginNew.Title = eWare.GetTrans("tabnames","logins");
       LoginNew.AddButton(CancelButton);
       varmyRecord = eWare.CreateRecord("logins");
       myRecord.log_companyid=ThisCompany;
       eWare.AddContent(LoginNew.Execute(myRecord));
       if(eWare.Mode == Save)
       {
           Response.Redirect(eWare.URL("login.asp"));
       }
   }
   else if(parseInt(Request.QueryString("log_loginid")) > 0)
   {
       varCancelButton = eWare.Button("Cancel","cancel.gif",eWare.Url("login.asp"));
       LoginNew=eWare.GetBlock("LoginEntry");
       LoginNew.AddButton(CancelButton);
       LoginNew.Title = eWare.GetTrans("tabnames","logins");
       LoginNew.DisplayButton(Button_Default) = true;
       LoginNew.DisplayButton(Button_Delete) = true;
       varintRecordId = eWare.GetContextInfo("company","comp_companyid");
       ThisLoginId=Request.QueryString("log_loginid");
       varmyRecord = eWare.FindRecord("logins","log_loginid="+ThisLoginId);
       eWare.AddContent(LoginNew.Execute(myRecord));
       if(eWare.Mode == PostDelete)
       {
           Response.Redirect(eWare.URL("login.asp"))
       }
       if(eWare.Mode == Save)
       {
           Response.Redirect(eWare.URL("login.asp")+"&log_loginid="+myRecord.intRecordId);
       }
   }
   else
   {
       varThisCompany = eWare.GetContextInfo("Company","comp_companyid");
       varurl = eWare.Url("login.asp")+ "&mode=new";
       varnewButton = eWare.Button("New","new.gif", url);
       varLogins = eWare.GetBlock("LoginList");
       Logins.AddButton(newButton);
       varArg = "log_companyid="+ThisCompany;
       eWare.AddContent(Logins.Execute(Arg));
   }
   Response.Write(eWare.GetPage());
   %>