Actionscript, it’s the new Javascript!

Some time last year, I got my hands on a draft of the Ecmascript 4 (Javascript to you and me) specification, thinking that it would be interesting to implement a reference-implementation for the evolving language (or rather, a non-boring way to get credit towards my degree). I remember being puzzled by the fact that, aside from a few Microsoftees and Mozilla-people, almost all the committee participants are from Adobe. The reason, it turns out, is that Actionscript — the scripting-language used in Flash — is based on Ecmascript, and Adobe apparently wants to drive innovation for the language.

Why do I care? Earlier this month, I wrote a non-trivial Flash application (details of the project to follow in another post), and I rather liked it. The typical place to start is the Flash authoring tool, which — compared to regular IDEs like Visual Studio (VS) — seems turned on it’s head: Whereas VS is a code editor with some incidental design-modes, Flash is a graphical design-tool with an incidental code-editing feature. Trying to figure whether an object dragged on to the stage should be declared, merely initialised or just used from your code gets old pretty quickly, Javascript has enough scoping ambiguities on it’s own.

Instead, go get Flex Builder 3. It’s still in beta, but freely available and built on Eclipse with all the IDE-niceness that comes with that platform. Refactoring is a bit sketchy, and probably difficult to get right in a language like Actionscript, but otherwise it’s very well rounded. The best part is that Actionscript 3 comes with most of the good stuff from Ecmascript 4 (could someone sync the versions please?), features like packages, proper classes (as opposed to the prototype-voodoo) and optional, strong typing.

What I’m getting is that if you wanna see what’s new in Javascript, go check out Actionscript! Through Ecmascript 4 it’s becomming an interesting language, there’s a nice IDE, rich API’s, interesting code (check this out) and a community that’s more focused on building compelling apps than on perfecting their Tower of Hanoi implementations.

Resources:

Using templates to do auto-signatures in client side javascript

WARNING — unsupported CRM hacking below!

A client wanted signatures to be inserted automatically in emails sent from CRM. I wanted to use the built-in template-feature, but the button on the email form launches a modal dialog which makes it hard to script. I was just about to give up and do a callout, when my manager suggested eavesdropping on the dialog to see what it did to merge the template. Wireshark showing web service call

Wireshark revealed that no postback is involved, the dialog merely calls an undocumented web service called /AppWebServices/EmailTemplateService.asmx. The service has an operation called GetInstantiatedEmailTemplate (see pic) which returns a bunch of XML. Not wanting to deal with this myself, I poked around in the email-form source and found that Microsoft has been kind enough to provide a function that’ll insert the result of the template instantiation for you. It’s called InsertValue() and resides on the crmForm.all.description element.

GetInstantiatedEmailTemplate web service

While this method is obviously unsupported, I’m reliably informed that the web services in /AppWebServices are also to be found in Titan.

Here’s the code in all it’s glory, note that JavaScript SOAP Client is used for the web service call.

var template_url = 'http://foo/AppWebServices/EmailTemplateService.asmx';
var soap_js = IncludeJsByDom('http://foo/js/soapclient.js');
soap_js.attachEvent("onreadystatechange", check_load);
function Get_Text()
{
    var params = new SOAPClientParameters();
    params.add("templateId", '{5623E3DE-1175-DC11-A465-001B78E16CCE}');
    params.add("objectId", crmForm.all.to.DataValue[0].id);
    params.add("objectTypeCode", 2);
    SOAPClient.invoke(
        template_url,
        "GetInstantiatedEmailTemplate",
        params,
        true,
        Template_Callback);
}
function Template_Callback(vol){
xmlDoc=new ActiveXObject("Microsoft.XMLDOM");
xmlDoc.async=false;
xmlDoc.loadXML(vol);
var body = xmlDoc.getElementsByTagName("body");
crmForm.all.description.InsertValue(body.item(0).text);
}
var check_load = function()
{
    if (event.srcElement.readyState == 'loaded'
    || event.srcElement.readyState == 'complete')
    {
        Get_Text();
    }
}

Attribute summing in CRM entity hierarchies

I’ve implemented some rather hefty Reporting Services (RS) reports lately, with some of the requirements including recursive summing af attribute values through hierarchies of CRM accounts. This sort of aggregation may be possible in RS, but I dislike that tool with a vengeance and prefer doing math in the database.

My first attempt (and the one that is now running at the client) used recursive SQL-functions. The scoping rules for recursive functions are highly non-intuitive however and when I look at the functions now, I have only a vague idea of why — let alone how – they work. Further, functions have to be created in the database independently of uploading the reports that use them, a minor deployment headache. Clearly there must be a better way…

Enter Common Table Expressions, a new feature introduced in SQL Server 2005. The syntax and exactly how the recursion works is somewhat opaque, the best explanation I’ve found is this article. You’ll probably need to read it if you want to grok this bit of SQL that computes the total number of employees in the parent/child hierarchy of each account:

with num_employees as
(
	select fa.accountid,
		accountid as original_id,
		fa.[name] as original_name,
		isnull(fa.numberofemployees,0) as numberofemployees
	from filteredaccount fa

	union all

	select fa.accountid,
		ne.original_id,
		ne.original_name,
		isnull(fa.numberofemployees,0) as numberofemployees
	from filteredaccount fa
		inner join num_employees ne on fa.parentaccountid = ne.accountid
)

select sum(numberofemployees) as numberofemployees, original_name
from num_employees
group by original_id,original_name

Here’s similar code for a prefilterable report dataset:

with num_employees as
(
	select accountid,
		accountid as original_id,
		name as original_name,
		isnull(numberofemployees, 0) as numberofemployees
	from filteredAccount as CRMAF_fa

	union all

	select fa.accountid,
		ne.original_id,
		ne.original_name,
		isnull(fa.numberofemployees, 0) as numberofemployees
        from filteredAccount as fa
		inner join num_employees as ne on fa.parentaccountid = ne.accountid
)
select sum(numberofemployees) as numberofemployees, original_name
from num_employees as num_empl_1
group by original_id, original_name

The most elegant formulation I’ve come up with is based on this example however:

with num_employees as
(
        select          accountid,
                        sum(numberofemployees) as numberofemployees
        from            filteredaccount
        group by        accountid

        union all

        select          fa.parentaccountid,
                        y.numberofemployees
        from            filteredaccount as fa
			inner join num_employees as y on y.accountid = fa.accountid
)
select sum(numberofemployees),accountid
from num_employees
group by accountid

I challenge you, the reader, to come up with a more concise example :-).

More resources: