Javascript Files and Caching

You will notice that the performance of Dynamics CRM can be negatively affected when you have loads of Javascript codes embedded into the form of an entity. It is recommended that you take out the Javascript codes from the form and place them in a separate script file which can then be loaded in the form. This will cause the script to be downloaded only once and therefore improve performance when opening the page again. For some info on how to load javascript files in CRM, look at one of my previous posts.

There is one issue though when doing so. When the file has been changed on the server, it will still be cached on the client's machines. You can force Internet Explorer to check the Javascript file to see if it has been changed. One of my German colleagues (Christian Niss) has learned me that it is possible to modify the iis-cache settings for just the javascript file.

If you switch off "enable content expiration" and add a custom http header for the javascript file only:

Cache-Control: max-age=259200, must-revalidate

It should set client side caching of the file to three days (259200 sec), but each time the script is being used the browser must check if a more recent file is available on the server.



Good luck with caching!

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!