SharePoint and Access

This post will concern SharePoint for once in a while!

As most of you know, it is possible to link SharePoint lists to excel, however, it is also possible to link SharePoint lists as tables in Access. This is very usefull since you can then create more advanced views using complex SQL. Data can also be linked with data from other sources, if needed. There should also be some ODBC driver that could be used to fetch data from a SharePoint list, however, I havn't seen one. If I do, I will let you know.

How to link a SharePoint list into Access as a table:
1. Make a new view in SharePoint with all columns and all rows. Call it something nice like "everything".
2. In access, choose the file menu and then choose "Get external data" (I am using the swedish version of access, so excuse my translation) and then choose "Link tables". In the dialog that opens, you can choose SharePoint list from the filetype dropdown. Then enter the url for the site, not the list. You will then be prompted for which list you want to use and so on and so forth. When done, you will have one maybe more linked tables in Access which will work just as any other tables in access.

Happy SQL:ing!

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Developing with VS 2005

Extending MS CRM 3.0 is normally done using Visual Studio 2003 and this is also the only supported development environment. However, it is possible to develop using VS 2005 as well. Some things have to be considered.

.NET 2.0 will work fine for external web applications using the CRM Webservice (not very strange since the webservice is generic)

.NET 2.0 will probably also work fine when developing workflow dll:s, but it is not supported. At least it worked when I tested Hello World! :)

.NET 2.0 will not work when developing callouts. However, there is a great description of how to develop these in VS 2005 anyway but for .NET 1.1. Beware that you cannot use specific .NET 2.0 functionality.

Here is the site: http://blogs.msdn.com/arash/archive/2006/08/25/719626.aspx

A good thing with this is that the standard TDT can be used when developing customizations. I am no licensing expert, so make sure it's within your licensing agreement.

Gustaf Westerlund CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Odd SQL Reporting Services Error...

I was working with a custom report today and I had a very strange error which I have noticed that several others also seem to have, both with SQL Reporting Services and other .NET applications. The report I was making worked fine, but when I tried to deploy it to the site, I got the following error:

"Length cannot be less than zero."

When I dubbel-clicked it in VS2003 (yes, I am still using it), the sourcefile of my DataSource was shown, and there was nothing wrong there...

Well, I tried lots of different things, restarting, trying to deploy to another server but the error was still the same and I could see the report was working fine i Preview mode.

Well, after a while I got fed up with it, and just tried to install the rdl manually on the reportserver (using the update function, in the properties tab of the report). It worked!

Hence, there seems to be some error in the deployment of reports in SQL Reporting Services. If you get this error, try to bypass it, your report might not be the problem after all.

Anyone reading this that knows why the deployment didn't work? I'd like to know!

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Web and Outlook mixup

As some of you might have noticed, if you have the Outlook desktop client installed and Outlook open, and then open CRM using IE, you will loose some web-specifics like the "Settings" menu and other addons specified in the sitemap or isv.config.xml as "web".

This is due to the fact that these files are downloaded to the client by IE when Outlook is started and these are then cached by IE. When the same URL is opened, IE recognizes these files, and does not download any new files, hence you get the Outlook-client specific.´

How to solve this? IE bases its caching on the url so you just have to install the crm-outlook-client to one url and use the webclient for the other. Note that you can even use the IP adress as one of the url:s, since IE doesn't resolve the name when checking for cache-hits.


Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Downloading the IE fix

I read the CRM Team Blog and they have a link to the IE-fix concerning the bug discussed bellow.

http://www.microsoft.com/downloads/details.aspx?familyid=ff9bc431-01f3-48e8-9a58-d701d2e60c1d&displaylang=en

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Use AllColumns

When retrieving data from the CRM webservice, part of the
RetrieveRequest-object needs to have the required columns specified. In
many of the examples this field is specifically set to the columns
needed, which is the most "correct" way of retrieving the data. However,
if you aren't experiencing performance problems the easiest way is to
use the AllColumns-class.

In short:

RetrieveRequest retrieve = new RetrieveRequest();
retrieve.ColumnSet = new AllColumns();

This is probably old news to all of you who have coded a bit. But I
thought I might write a bit about it anyway, since not all people are
experts at birth.

Gustaf

more on SubscriptionSyncInfo

Well, sometimes it's better to look first and blog second,
I looked around a bit and found that the CRM Team Blog actually have an entry on how this works. Have a look here: https://blogs.msdn.com/crm/archive/2006/08/15/701230.aspx

The SubscriptionSyncInfo table seems to be the place where all syncs are logged to the database. Very usefull for checking if a users sync has been successfull and how often it has been done.

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Subscription Database tables

There are a couple of tables in the database that handle the Outlook-CRM Syncronization and the offline sync. I havn't been able to find any further documentation on exactly how it works, but will publish any more info I find on the subject on this blog. There are mainly three normal tables concerned, namely:

Subscription
SubscriptionClients
SubscriptionSyncInfo

There are also several tables created for each user that contains their specific sync data. These are named:
SyncEntry_987F788E642CDB1184900007E94DD8DB

I would like to point out that these tables both contain info on Outlook-CRM sync and CRM Offline sync (ie Main SQL - MSDE sync).

If anyone has any more info on the subject, please contact me, I would like to discuss the matter.

If I find the time, I would like to research this a bit more. If I do, I'll let you know...

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

JavaScripting and quick create forms
For you who haven't already noticed, if you create an OnLoad JavaScript for Account, Contact or any other entity and it includes some sort of defaulting of field values, if this field is not compulsory, it is not included in the quick create form, hence the onLoad event will send an error when the form is loaded. So, just check that it is there first :)

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Developing Callouts
When developing callouts, many of you have probably gone mad by all the waiting due to the fact that the IIS has to be stoped and started between every compilation, and then most of the CRM application has to be recompiled again when you try to access the page. I have tried to find a good solution on how to bypass this, some including trying to shut down the AppPool instead of the IIS, but I couldn't get it to work.

This evening, I read a blog about how you can do it! This great guy has developed a small program that just calls your callout like the CRM normally does, only you can do it in your local computer instead! Great for debugging! A simple a ingeniuos solution, why didn't I think of that?

You can find it at: http://www.stunnware.com/crm2/topic.aspx?id=LocalCalloutDebugging

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

On taking backup
When taking backup of your CRM system, to avoid any later problems consider the following:
- Take a complete backup of the xxxx_MSCRM database.
- Take a complete backup of the xxxx_METABASE.
- And just to be sure, export all customizations to an xml-file.
- Make a backup of the Microsoft CRM\Server\bin\assembly (normally found in Program Files)
- Make a backup of all custom aspx and html pages and code from the external website.

If you have any customizations what so ever, make sure you take a backup of the METABASE att same time as the MSCRM-database. If you do not, you might encounter inconsistencies at a later date which might be hard to fix. I'm planning on writing about what you do if this happens anyway.

If you have made any changes to the reports you should also backup the main SQL Reporting Services database, but hopefully have the rdl:s somewhere else aswell.

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

MS Hotfixes

Maybe you've already been there, if not, you should, The Microsoft hotfixes for MS CRM 3.0 can be found here. Note that it is provided as-is, so read the text above the files before downloading anything!

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Limitations on Client side dublett handling

In the CRM SDK there is an example of how to create a dublett checker. It works fine but I would like to point out a few things.

1. Note that it is based on JavaScript, hence it will only be used when the GUI is run. When running an import, for instance, the GUI is not used and the javascript will not be run.
2. Perhaps a lead dubletthandler should also check for the existance of an account with the same name. It is only natural to check if the new lead might already be a customer.
3. It cannot be used offline. Users should be notfiied of this or the function to create the entity should be disabled in offline mode.

To solve the first problem, the best cause of action is to make a callout-based dublett handler in addition to the javascript based. You don't want to throw that away since its user interface is a lot better than the callout-based checker.

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

What is the REAL error?

Finding what the real error is can sometimes be a bit tricky since the real error actually is embedded a bit down the exception hierachy. For instance, I recieved the following error when trying to send an email with the normal .NET mail classes using a workflow-dll.

Error code=8004280b. Unable to access the object CDO.Message.

I started thinking that there might be some different version between my local .NET and the server on where the program was run. I also thought there might be some problem with finding/accessing the dll. A bit weird but it had to be something.

However, after searching the internet I found that this error might not actually be the real error. Try recursing down into the exceptions to find the real error, and I did. With the following code:

try {
SmtpMail.Send(mail);
}
catch(Exception ex )
{
OutText("The following exception occurred: " + ex.ToString() ); //check the InnerException while( ex.InnerException != null )
{
OutText("--------------------------------");
OutText("The following InnerException reported: " + ex.InnerException.ToString() ); ex = ex.InnerException;
}
}

The output made me solve the problem in 5 sec, I had forgot to assign a to-adress in the mail. A weird error message for a simple and stupid error. So, from now on, I am always a bit sceptical on what error is shown and if it is the real error or not.

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Off topic - ease your work with Remote Desktop

Many of you probably use Remote Desktop to connect to your clients computers. Sometimes you manage to lock the two sessions you are allowed and you can't reach the computer. What you need to do then is to connect using the console command. The easiest way to do this is to create a bat-file with the following content:
console.bat
========
mstsc %1 /console



Now, you just have to drag-n-drop the rdp-files on the batfile and the connection will be made using the /console and you can access the computer. No cmd-hacking, no remembering the syntax... just drag-n-drop! You have to be a local administrator to connect using /console, but arn't you always?

To log out the old connections, open the task manager and select the tab users. Mark the users that do not have id=0 (thats the console connection) and select logoff.

Hope it is usefull!

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Migration and the Migration Framework

The migration framework is based on a very good intention from MS since it can usually be a hassle. It is quite competent but has one great lack being that it only handled system entities and not cusomtized entities.

The feature of customizing entities is one of the greatest USP (unique selling points) for MS CRM, and being a system in directed at mid-market it is more than likely that a customer has to migrate from previous data, if only being excel, access or Outlook. To not support customized entities is a great flaw that severly limps the otherwise fine product.

If you need to migrate to MS CRM 3.0 and you are using customized entities there are two choices:
1. Make your own speciall program that communicated directly with the webservice.
2. Buy a third party product.

In most cases I think the later choice is best, there is a very good product called ImportManager Studio 2006 Professional made by the Danish company CRM Extensions. link.

Not only is this product good for migrations it is also replaces the very rudementary import function found in CRM. Other than being able to import data to custom entities, it can also store an import set-up to be used at a later date and link imported entities to eachother (like linking a contact with an account).

It is only €1700 which is quite cheap when you compare it to the costs of writing your own program.

It will be interesting to see if MS updates the migration framework to handle custom entities. Until then, count on paying €1700 for a product that can.

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

On the IE bug again...

The bugg in IE described bellow will according to PC-World be corrected in an upcomming update of IE. Please see the article.

I would also lite to recommed the following Blog, where I noticed this.
Sonoma Partner Blog

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

Tectura - CRM and Navision integrator

As many of you might know there is an integration product by the company Tectura to use for integration between MS CRM and Navision. I have been working with this and would like to share a few thoughts on this.

In general it works well when you get it installed properly, something you have to allocate quite a lot of time to. At least a couple of weeks.

It is, out-of-the-box, a technical integration of the products, i.e. it transfers information but implements very little business logic, something you have to add later. For instance if you block a product in Navision, this information is propagated to CRM to a field but it does not stop users from placing orders with the product.

The integration does not concern all entities. The integration mainly concerns account, orders, quotes. Products, Invoices and Units of Measure (and schedules) are sent from Nav to CRM but not back. Note that the integration does not treat contacts, pricelists, oppurtunities. Many salespeople also like to get information on the payment status of invoices. This is not included by default.

Summary being that Tectura is a good foundation for integration between CRM and Nav but be sure to note its limitations and make sure you have skilled CRM and Nav programmers to make the integration as tight as the customer most likely would like it to be.

I will probably blog some more on this subject later.

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se

DevToolBar and style.display=none

There is a great tool from MS called the Developer Toolbar. It is great for finding parts of the GUI in CRM that you want to hide or change in some other way. To the download page

For instance, I wanted to block the use of write-in products. There is no security setting to use for this, so what you can do is write protect or hide the write-in product switch in the order view (and in quote and opportunity).

However, there is also a menu item for write-in products and it can't be disabled (as far as I know). So, by first, disabling appmode in web.config (should always be used when developing/customizing) and then accessing the orderform with the direct url: /SFA/salesorder/edit.aspx">http:///SFA/salesorder/edit.aspx you can browse the DOM-tree and will find that the menu item for Write-in products has the id: navWriteInProducts

Then in the onLoad event of Order, you just add the following:
navWriteInProducts.style.display = "none";
which simply removes the item from the menu when loading an order.

This is a simple way to modify items in CRM as long as they have an ID. It is quite common that customers want things to be hidden and defaulted, and this can't be done for some parts of CRM using normal customization. This method doesn't require any coding and will work.

Gustaf

IE problem

There is a known error in the latest update of IE and MS CRM. IE crashes when running CRM, I havn't been able to hone in to exactly when it crashes but it usually happens when you are using the drop-down-menus.

I will update this blog when I hear something new.

Gustaf