Not all emails are sent from MailMerge

Yesterday I looked at an issue with a colleague of mine (Nico Verhagen). The problem was that a mailmerge quick campaign has been created which should send out approximately 1500 emails. When the quickcampaign had finished, there were only 43 emails sent and nothing failed. How could that be true?

Apparently only 800 contacts had an email address specified, so that already is causing half of the issue. The second half was harder to find, especially because there was no error message anywhere.

The root cause of this issue appeared to be that the bit field for 'donotsendmm' has been set to NULL. Although the default is 'yes', the field had no value because the records were created in an import program instead of the UI. In this import program the default values were not set for the 'allow marketing' attribute. After setting the allow marketing to true, the emails were sent.

Hint: To update all the records in bulk on a supported way, Nico created a workflow rule which updates that field to set it to true. Then perform an advanced find to select the records for which the field has not been set to false and run the workflow on all the records on the page. You will then update 250 records in each run. You can set the record amount visible in your personal options in CRM.

Creating an activity report which includes the related people

In an activity CRM grid, it is not possible to add attributes from the activity type (letter, phonecall etc) itself. The fields to and from on the entities phonecall, letter, fax are therefore not eligable for addition on the CRM grid. It would be very useful to see those though. The same is valid for the to, cc and bcc in email and required and optional attendees in appointments. In this post I won't be giving a solution to show the attributes in the grid, instead I will give a workaround by using reports.

The only attributes which you can select in the grid are the attributes which are belonging to the entity activitypointer. These include the activityid, startdate, statecode, but also the regardingobjectid. So the question is, how to get the to, from, cc etc. For this you can use the function which I have posted in my previous post. This function accepts an ActivityID and an ActivityPartyType. So what is this type? Look at this page: ActivityPartyType. You will find a list of values mapped to what kind of field you want to add to your report.

By using that function you can create your query for the report. An example would be:

SELECT
activityid, activitytypecode, scheduledstart, subject, owneridname, statecodename,
regardingobjectidname,
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 1)) [to],
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 2)) [from],
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 5)) [required]
FROM
filteredactivitypointer

This query does select some default attributes and it adds the regarding, to, from and required fields. Add this query to the generation of a report and you'll be set to go.

Note: make sure that the function gets added to your database and assign the correct rights. See the post around the function for details.

Happy reporting!

Transform a table column into a CSV field in SQL Server

Imagine that you want to use a related table in your SQL query. You then must return only a single column or otherwise you'll get this SQL error:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What I like to do, is to transform the result table column into a single field separated by a ; sign. Earlier on I used to create a huge function which used cursors etc. Then a colleague of mine told me how to use C# code in SQL server which seemed to be a neater solution.

Today I have found a new approach. This uses a function again, but it is very simple. Here's the function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ronald Lemmen
-- Create date: 4 July 2008
-- Description: Function to return the people in CSV format belonging to a activity based on the activity party type code
-- =============================================
CREATE FUNCTION fn_RL_GetActivityPartyList
(
@ActivityId uniqueidentifier,
@ActivityPartyType int
)
RETURNS nvarchar(3000)
AS
BEGIN
DECLARE @result nvarchar(3000)

SET @result = ''

SELECT @result = Coalesce(@result + ';', '') + partyidname from filteredactivityparty party where party.activityid = @ActivityId and party.participationtypemask = @ActivityPartyType

RETURN SUBSTRING(@result ,2, LEN(@result))
END
GO

The @result will get filled by the SELECT query with the values from the filteredactivityparty table separated by a ;. The first character is a ; as well and therefore I do remove this one in the RETURN statement.
After the + in the SELECT query you can place your own query. This example is just extremely useful in my next post :)

Make sure that you do grant access to the correct people in order to use this function. In the case of a CRM report, add the reporting group. See the below example, but make sure that you do change the guid to the guid which is valid for your system.

GRANT EXECUTE ON [dbo].[fn_PGGM_GetActivityPartyList] TO [PGGM-INTRA\ReportingGroup {05e0584a-1d94-424c-8014-c2f3b1b92ccc}]
GO

Happy reporting!

Determine the logged in user

Some time ago I wrote a post around how to show and hide fields based on the logged in user. Apparently this doesn't work for Dynamics CRM 4.0 anymore for two reasons. This post will help you to get it working again.

The first one is that you are not allowed to use the <%= approach anymore. You do now have to use the function:

Page.Response.Write(string strText);

The second reason is that the variable "Microsoft.Crm.Security.User.Current.UserAuth.UserId" does not exist anymore. Microsoft has changed the internal structure of how they work with the current user. You now should use this "Microsoft.Crm.Security.User.Current.SystemUserId.ToString()". These two changes lead to the following code

<script language="javascript">
var loggedInUser = '<%Page.Response.Write(Microsoft.Crm.Security.User.Current.SystemUserId.ToString())%>';
</script>

If you do have a single mistake in the aspx page you are changing, then you'll get the following error:

The VirtualPathProvider returned a VirtualFile object with VirtualPath set to '/MicrosoftCRM/sfa/accts/edit.aspx' instead of the expected '//MicrosoftCRM/sfa/accts/edit.aspx'

This has nothing to do with the VirtualPathProvider, it's just that you have a mistake in your aspx page.

Good luck!

MVP for the 3rd consecutive year

Hi Guys,

As a reward for community work last year, Microsoft has again reawarded me the Microsoft CRM MVP! I am very much pleased with this award because as one of my colleagues said: Getting to the top is hard, but staying there is even harder!

Last year I have spend a lot of time on doing community work again. I have spend a lot of time preparing for presentations like deepdive sessions and CRM 4.0 presentations. Furthermore this blog has been extended with quite some posts. And although there were not as much posts from me as in the year before, I did do spend a lot of time on the newsgroups en forums to help out people.

Once again I would like to encourage all of you to share your knowledge. Answer questions in newsgroups and forums. Or prepare a presentation about something you have been working on and share this with your colleagues. My girlfriend has a t-shirt which says: "Knowledge is power". I agree with that, but I am confident that sharing knowledge is even more powerfull!

See you around in the communities!

UPDATE: Have a drink with your fellow CRM consultants

For quite some time I've been thinking about organizing a drink for all the Microsoft Dynamics CRM consultants in the Netherlands. Mostly I've been thinking about the format. Who should be invited, should Microsoft be a sponsor etc. From one day to another, these questions were answered by Johan van Dijk. He started the "Dynamics Netwerk Borrel". He organizes drinks for consultants who are working with Dynamics CRM, AX or NAV. These drinks are sponsored by Microsoft Partners. The first drink is on the 21st of August and is sponsored by Centric. The location is in Gouda, Netherlands. Feel free to subscribe on: http://www.dynamicsnetwerkborrel.nl/

See you there!

If you're not from the Netherlands, then use this as an example and set up such a similar event. It's always nice to have some drinks with CRM consultants right?

UPDATE:
The date has been changed to the 21st of August.

Windows Home Server

Who of you guys is working with Windows Home Server (WHS)? Last weeks I've been working with this in my spare time. I've grabbed an old pc, added some harddisk space and memory and installed this product. Now I'm figuring out all of the possibilities.

Of course there's the most important feature: Backup. Every night the WHS server wakes up all of my pc's and laptops and makes a backup. Once my work laptop was stolen and I did lose data... I had a hard time back then, but that won't happen again. My laptop might still get stolen, that's a risk of living near Amsterdam, but I won't ever my data again.

The second feature I really like, is the ability to have a share to place your files on. By default it creates different folders like Photos, Music, Movies etc. And there's an even greater feature related to that. Some of you probably have a server at home as well. You have to admit that you have been struggling with harddrives and free spaces right? Well, I've added several harddisks to this machine and it just combines them all to one virtual harddrive. There's never the problem that one harddrive is full. Just add a harddrive and you can add files to the shares again.

Then there's the feature of accessing your server from the internet. You can easily set up the server to be accessible from the internet. Microsoft even offers a friendly url like: http://Yourserver.homeserver.com. From this site you can access your shares and even remote desktop to your home computers, if they support RDP.

Now it would be great to have CRM on this installed as well right? It would be wonderful to have your own home CRM system which contains all the addresses of your friends, birthdays etc. Integrate this with Facebook and Linked-in to always be up to date. Also use a mobile solution to have the data integrate with your phone and then we're living in a utopia right? I think it would be great that if I end up with my car somewhere in Spain and I do know somebody in Barcelona, that I can search for my friend in my crm system, click the button "use address in TomTom" and drive there to say "HI!". That would be awesome.

But thats not possible, yet. WHS isn't designed to be an Domain Controller or even part of a domain. Doing so appears to be possible but it is very much unsupported. I'll dig a bit into this after the Power Pack 1 has been released. For the users who wan't to have AD and install CRM at home now, they should go and get Windows SBS and get it running in your home. Not only is this product a lot more expensive, it also doesn't have some of the neat featurs of WHS.

In short, Windows Home Server is a great product for what it's designed for: Allowing the home users to have a centralized place for their files and backups.