Wednesday, September 26, 2007

Creating custom views for MS CRM 3

Any modification made to the main or META-database is considered unsupported customization. Not very strange, since you never know what new fields or tables Microsoft might add to the database.

Sometimes, however, especially when working with reports, it can be useful to create your own views that aggregate data to your needs. Adding a view to the CRM database might be the easiest and you will probably be ok as long as you are a bit careful when naming the view. However, you don't have to do it this way. You can also create a new database on the same server and create your view in this database. To be able to access the views (and if you still want to play unsupported, tables), just add the database name in front of the viewname. For instance:

SELECT * FROM MyCompany_MSCRM.dbo.SystemUser;

The "dbo" part doesn't have to be there but is added by the view creator GUI, so I left it there to avoid any misunderstandings.

Unfortunately, the "Add Table" function in the GUI doesn't support selecting views from other tables even though it is supported in the database.

As far as I know, it is also possible to connect to a view in a database on another server. I don't know how this is done and don't really see the relevance from a MS CRM 3 perspective, but if I find out how, I will let you know.

Gustaf

Friday, September 21, 2007

Silverlight and Dynamics CRM

Silverlight is a new product from Microsoft that can be viewed as the same sort of plug-in as for instance Flash, Java Applets or ActiveX. There are two feautres that make it stand out from the rest, namely:
- Vector based graphics built on the new Windows Presentation Foundation (a part of .NET 3.0). The definition of the GUI is definied in a langauge called XAML.
- From Silverlight version 1.1, you can write managed code. In other word you can finally start working with C# or VB.NET on the client side instead of the irritating language javascript.

These two features make the foundation for a more user friendly and more rich experience of using the web, something at least I have been longing for. Being able to use managed code/CLR-code, will make web development a lot easier and speed up the process of making more robust and reusable programs.

On the CRM Team Blog you can download an example of how silverlight can be used in conjunction with Dynamics CRM 3. I saw that some people had had some problems with it, and I havn't had time to test it myself yet, so be advised.

Here is the link: http://blogs.msdn.com/crm/archive/2007/06/19/crm-and-silverlight-integration.aspx


Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Sunday, September 16, 2007

Office 2007 + CRM = true

Michael Höhne is one of the most generous and knowledgable people in the Microsoft CRM 3 community. His webpage http://www.stunnware.com/crm2/ hosts lots of very interesting articles, both for advanced Microsoft CRM developers and more novice Microsoft CRM application professionals. For instance, he has a very interesting posting showing an example of how to use the new file format OpenXML that comes with Office 2007 in conjuction with Microsoft CRM 3 to make very powerful software with almost no effort (ok, a little effort :)

Have a look: http://www.stunnware.com/crm2/topic.aspx?id=OpenXml

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Request hotfixes online

The most common way of requesting a specific hotfix for Microsoft Dynamics CRM or SharePoint is to call Microsoft Support (they cannot be downloaded directly). However, there is also a webpage where you can request a hotfix online. Have a look an add it to you favorites, you will probably need it.

https://support.microsoft.com/contactus2/emailcontact.aspx?scid=sw;en;1410&WS=hotfix

Gustaf Westerlund
Microsof CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Fake lookups

As I mentioned before, when I was working with the company Cybernetics, there is a way of creating false lookups when it might not be possible to do so normally (for instance system-system relationsships).

The blog http://andrewn23.blogspot.com/ describes in detail how this is done, so, if you are interested, please have a look. This technique can of course be enhanced in many ways.

Also, I am not certain if this technique is supported since Microsoft might decide to change the way some of these more internal pages (lookupsingle.aspx) work, hence breaking the customization.

The blog also mentions some hazards of using allcolumns that are very worthwhile reading (I like to use them to increase development time and change to specific attributes when I get performance issues).

So, take some time and have a look at it, I am however sad to see that the last entry was in october 2006.

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Thursday, September 13, 2007

Selecting multiple records from a grid in MS CRM 3

Using isv.config customizations you can add buttons to the menybar above a grid (next to "More Actions"). A special technique has to be used to fetch the selected records and Ronald Lemmen wrote some about it this summer, here: http://ronaldlemmen.blogspot.com/2007/06/fetching-selected-records-in-grid.html . I had reason to try it and ran into problems when the url of the file containing the javascript was on a different server (different tcp-port has the same characteristics as a different server from an IE viewpoint). The window.dialogArguments was null. I tried to modify the settings in IE but found no way of getting around the problem except for setting up a virtual directory bellow the CRM-site containing the html-file.

So, if you run into similar problems, try moving the html-file to a directory on the same server (from an IE standpoint).

Gustaf

User based Timezones in SharePoint 3

I have been on the look for this feature for some time. I've heard it's there but never found it. Now my colleague Sebastian Tegel found it. Have a look if you're interested!

http://sebastiant.blog.com/2079971/

Gustaf

Friday, September 07, 2007

Environment independent solutions

Developing CRM solutions that are environment independent is not trivial. By just adding a webservice you are making your code dependant on the CRM customization of that system. If you move the solution/dll:s to another system you will be running a risk of your code not working. This is especially problematic for ISVs since they never know what system their product will be installed on. However, there is a good way to handle it. The best way is to first use a plain out-of-the-box CRM system and use the program

C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\Bin\wsdl /out:c:\temp\myCRMProxyClass.cs http://localhost:5555/mscrmservices/2006/crmservice.asmx?wsdl

This will create a file called myCRMProxyClass.cs is c:\temp.

Add this to your project. This is in essence the same this as adding a web reference and adding "using CRMSDK" or whatever you named it. Hence you can access all the CRM webservice classes.

Then create the service-instance and set the url by using the registry-key added during the installation of Microsoft CRM. Have a look here if you want to know how. http://ronaldlemmen.blogspot.com/2007/08/creating-environment-independent.html

If you have other environment specific data, it should be placed in the web.config / appsettings (if using a normal web-site/virtual directory) or you can use the AssemblySettings.dll (has to be downloaded, not from Microsoft) to create a assembly specific config-file, alternatively, add you own keys to the registry.

I hope I have given you a few hints on how this is done. Happy coding!

Gustaf

Activate a Workflow using Javascript

Workflows are a great tool. They are quite simple to create, even non-programmers can actually create them and they are flexible. However, there are some problems with workflows in CRM 3 (how workflows are handled in Titan/CRM 4 will be interesting to see) for instance, a workflow cannot be activated when an entity is updated, only when it is created, when it changes status, when it changes owner and of course, manually.

Mitch Milam has written an interesting post on his blog concerning how to activate a workflow using javascript. You can read about it here: http://blogs.infinite-x.net/2007/06/15/launching-a-workflow-rule-from-javascript/.

A bit of caution is however advised. I would actually prefer to have the workflow being activated by a PostUpdate-callout instead, to make sure that it is only run once. Activating a workflow on the on-change event can be a bit hazardous since it is very possible that the event is triggered several times on the same form before it has been saved.

Never the less, it is a good posting and can probably give you an idea or two about how to programmatically activate CRM workflows. If I wasn't block from using my own virtual directory or website with my own aspx-files, I would also prefer to trigger the workflow from an aspx-page and call the aspx-page using AJAX. Have a look at the class ExecuteWFProcessRequest in the CRMSDK as it enables you to execute a workflow using the web service.

Gustaf

Wednesday, September 05, 2007

SQL COALESCE

SQL Reporting Services is a great tool. It allows you to create flexible and exportable (to pdf etc) reports. However, things sometimes get a bit complicated and a normal problem that you face is the problem of columns containing null-values that you want to use for calculations. For this there is a nice function called COALESCE which replaces null with the value directed.

For example:

SELECT firstname, lastname, age FROM contacts;

Might return the list:

firstname        lastname        age

John            Smith            5

Peter            Jacks            null

If you want to use the age-column to do some calculations or you just don't want it to be null, just change your SELECT-statement to:

SELECT firstname, lastname, COALESCE(age, 0) As NewAge, COALESCE(age, 0) * 2 As DoubleAge FROM contacts;

This will give you the result:

firstname        lastname        NewAge    DoubleAge

John            Smith            5        10    

Peter            Jacks            0        0


 

A useful and simple functions, to get you out of some nasty trouble when doing for instance LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN when the risk of returning null is great.

Gustaf

Humandata AB

Saturday, September 01, 2007

Javascript – define functions and programmatically attach events

Since we're on the subject of javascript I might as well add some more stuff. First of all, how to define you own functions, then how to programmatically bind a function to an event, for instance, onchange of a field.

This mainly concerns Microsoft CRM but the technique can also be used in SharePoint, although javascripts aren't as common.

First off, how to define a function.

There are actually several different ways, for instance the normal:

function myfunc1(param1, param2)

{

alert("myfunc1");

}


 

And the one with a weird sort of syntax:

var myfunc2 = function(param1, param2)

{

alert("myfunc2");

}


 

In CRM 3, these can actually be defined within the onLoad, hence creating some ways of code reuse. These functions will be available for all event in the page since they have been declared in the onload.

This leads me to another point, that a function has to be defined before it can be used. Hence, in the example above, myfunc2 can call myfunc1, but not the other way around. If you try, you will get strange error messages saying stuff like "null is not 'null'".

Secondly, you can bind events to functions using code. There are several ways of doing this as well (what did you think!?) and they mainly differ based on which browser you are using. If you want to read some more on the subject, if you want some more info on event binding in javascript, please have a look at this very instructive page: http://developer.apple.com/internet/webcontent/eventmodels.html

First the simple method by just setting the onchange attribute.

crmForm.all.ext_myfield.onchange = myfunc2;


 

or

document.getElementById("objectid").onchange = myfunc2;


 

This has the advantage of a quite simple syntax and, if you want it, the override of previous events.

The second way of doing this is using the attachEvent method. As far as I've read, there are some differences with syntax between browsers, but since neither MS CRM nor SharePoint work in any acceptable way in anything but IE, this is not a big issue. Here is some example syntax:

crmForm.all.ext_myfield.attachEvent("onchange", myfunc2);

or

document.getElementById("objectid").attachEvent("onchange", myfunc2);


 

The main advantage of this method is that you can attach several functions to one event, hence, not overriding other handling.

Bellow is an example html-file which demonstrates this:

<html
xmlns="http://www.w3.org/1999/xhtml">

<head>

<script
type="text/javascript">

var myfunc1 = function()

{

alert("myfunc1");

}

var myfunc2 = function()

{

alert("myfunc2");

}

var loading = function()

{

alert("running");

document.getElementById("btTest").attachEvent("onclick", myfunc1);

document.getElementById("btTest").attachEvent("onclick", myfunc2);

}

</script>

</head>

<body
onload="loading();">


<button
id="btTest">Click me</button>

</body>

</html>

If you copy this to a file called test.htm and test it, you will see that when clicking the button, myfunc2 will be executed first and then myfunc1.

So, all put together, this can be used to a great extent in MS CRM 3, where you can now put all code in one place, the onLoad script, define some functions and then bind the onchange event of all the attributes you like in the same code. Remember to define the functions before you attach the events.

I havn't seen CRM 4 / Titan yet, and I don't know what features it will have in this aspect, but if it includes the feature to be able to add a global javascript file that will be included on all pages, this would be very very useful when using techniques like this.

And remember, javascript is an ugly beast, but it is very useful! J

Gustaf Westerlund

Humandata AB

Window.close() in javascript acts a bit strange…

Javascript is a strange language, it is often confused to be Java, which is very far from right, and the syntax can often be thought to be as consistent as C# or Java. Not the case. Javascript is a strange and weird beast, that when tamed can be a very strong ally. Taming it can however really get on your nerves!

There is one method working on the object window, called close(). Hence it is called using window.close(). It does the simple thing of closing the current browser window. No more, no less.

Well, actually, it does it in a strange way. Have a look at the following code that is executed in body onload. What is going to happen?

Alert("Start");

Windows.close();

Alert("End");


 

I will give you two options:

Option 1:

  1. A dialog window showing "Start" will show.
  2. The current browser window will close.

Option 2:

  1. A dialog window showing "Start" will show.
  2. A dialog window showing "End" will show.
  3. The current browser window will close.

Well?

You would think that option 1 is correct, well, it isn't, the script is executed according to option 2. For some reason, window.close() doesn't actually close the window, it signals it to close and when the onload has reached its end, it is closed. I havn't checked to see if this behavior only happens when executed in onload or if it is the same all over. Be aware though, it might not work as you'd expect!

Gustaf

How to add a virtual directory below your SharePoint or CRM website

Sometimes you would like to create a virtual directory bellow for instance the SharePoint main site. Lets say you MOSS is installed at http://intranet and you would like to create your own virtual directory with your custom made aspx-pages at for instance http://intranet/custom. Well, if you use the IIS Management Console, add a virtual directory to the website, and add an aspx-page to that virtual directory, you will get errors saying that the application cannot find certain assemblies. This is due to the fact that these assemblies have been referenced in web.config using <add assembly="name-of-assembly" / > tags. Due to the way IIS works, these references are automatically inherited to all sub-directories, in this case, you virtual directory.
So, what to do? The simplest way to handle this is to add <remove assembly="name-of-assembly" / > tags to the web.config that resides in your virtual directory. This will explicitly remove the reference created by the parent website. Remove them one by one and soon you will be able to run your code just as you would like.
One good advantage of using adding your code to a virtual directory bellow the main site instead of using a new website at for instance another host header or TCP port, is that you can address your code using server relative urls. In essence, if you created an aspx-page at http://intranet/custom/mycustompage.aspx. If you address this page from the SharePoint, you can use the url: /custom/mycustompage.aspx without http:// or the servername. This also gives you the benefit that the page is on the same server, hence no security problems with IE.
In SharePoint, make sure that the address of your virtual directory is NOT bellow any wildcard included och explicitly included sites (in the setup of the webapplication in central admin).
Please note that adding virtual directories bellow the CRM root is an unsupported customization. This is due to the fact that Microsoft might release an update that adds a directory with the exact name of you virtual directory. This would give rise to either the fact that your code stops working or more likely that the content of the directory added by Microsoft cannot be accessed.
Gustaf Westerlund
Humandata AB