Timetracking is pretty simple. You set start time, end time, and write down what you did. Be it task description, project, company you worked for as a freelancer. There’s quite a number of tools, web subscriptions and techniques, you name it, but still they lack something. Some sort of easy and elegant way of data input. Of course, there’s an automated way of tracking the computer time you spent working with this or that app, you get those pop ups to punch in / punch out, but I still have got a feeling it is still not what it should be… Maybe someday…

Well, today I will show you how to use MS Outlook Journal. Why Outlook? Most likely you use it and hence you don’t have to install another 3rd party app. I personally tried several e-mail clients and somehow Outlook still wins, although there are so many annoyances and pitfalls. How only do they do it?

You may have found several articles from MVPs describing how valuable Outlook Journal is, how you can track time spent on this task, that task, blah blah. What they do not tell (even if you ask) is how the hell you get a neat report out of it? They start like – export it to Excel, or well maybe Access. Write down a VBA script to get the data to… And you are like …What?!

Trick No.1 – Set Up Your Custom View

On the left side you can see the list of views available for you Journal. You can modify existing one or create a new one. Creating new is the best. You define it exactly as you need it, and leave the others intact.

Selecting Define Views opens the dialog with all the necessary settings. Select the e.g. Table. You get a list of events like Tasks / Phone Calls etc. The controls on these dialogs are self explanatory. There’s no need to get into details. Select All Journal folders, so that you can use the view on all of them. And press OK.

After that you get to the screen with view options. You can set which fields you are going to show in the table, set grouping e.g. company / category /  set sorting, filtering etc. Good option is to set column formatting so that you get start and end date formatted in a human readable form.

Trick No.2 – Create Your Custom fields

In the previous dialog press Fields button. In a dropdown select Journal Fields, and by clicking add button add all those fields you need into the view.

Here comes the essential trick how to get the duration in a reportable format. Journal in its default views returns something like 8hours (8h) and 30 minutes (30m) and you face nasty conversion later on. Even the exported Journal via Menu / Export / MS Excel  / returns duration in minutes!?

Click New Field. Type Name – Total, or New Duration whatever you like. Select Type: Formula and click Edit button. Then you prepare your formula. It is a bit Microsoft clunky, but you can type the formula in as well. And you can create pretty complex ones. All point and click, without any real programming required. For calculating the duration you may use e.g. : Round (([End] – [Start])*24,2). It subtracts the two dates, returns the fraction of the day as difference, multiplied with 24h to get the hours and then round it to precision of 2 decimal places if you don”t want to lose those 30minutes chunks.

Press OK and afterwards it looks like this:

Add a bit of styling / formatting and filtering to meet your criteria. You can set the view to show only the records for this month. Press the Field button and click through condition setup procedure.

Additionally, it is possible to set up custom sorting order on multiple columns.

Format the columns in a nice way e.g. Start date and End date in a human readable form.

Your view should look pretty good now. For summary, just select all the records, copy and paste into Excel and put a sum formula below the Total column. If you are familiar with Pivot tables and named ranges (tables) you can prepare Pivot and just replace the source range with your copied data and you are done. However, fine tuned Outlook Journal View may be pretty sufficient for reporting purposes already.

After a successful import of external Contact database we faced an issue with all the first names appearing as Dipl. Ing. XXXX, or MUDr. YYYY. The decision was made to take all those academic degree substrings out to salutation field and leave the Contact firstname with first name string only. Reimporting the whole contact base was quite unappealing, so we decided to create a short update script on the DB level. It relies on the format with dots behind the academic degrees but it can be easily modified to used to remove any string. The interesting part is the CASE syntax, that allows using only update statement without writing a procedure.

UPDATE  contactbase SET
salutation =
CASE
	WHEN PATINDEX('%.%',REVERSE (firstname)) = 0 THEN ''
	WHEN PATINDEX('%.%',REVERSE (firstname)) > 0 THEN
		REVERSE(SUBSTRING(REVERSE (firstname),PATINDEX('%.%',REVERSE (firstname)),LEN(firstname)))
END,
firstname =
CASE
	WHEN PATINDEX('%.%',REVERSE (firstname)) = 0 THEN firstname
	WHEN PATINDEX('%.%',REVERSE (firstname)) > 0 THEN
		REVERSE(SUBSTRING(REVERSE (firstname),-1, PATINDEX('%.%',REVERSE (firstname))))
END

Then we modified the fullname field as well (since it is concatenating firstname and lastname automatically at import)

UPDATE contactbase SET yomifullname = ISNULL(FirstName, '') + ' ' + ISNULL(LastName, '')

Of course, test it in your scenario first (e.g. write similar SELECT statement) otherwise you may corrupt your data as your structure may consist of different input values! …And backup your DB first – just in case

SELECT salutation,
'SALUTATION' =
CASE
	WHEN PATINDEX('%.%',REVERSE (firstname)) = 0 THEN ''
	WHEN PATINDEX('%.%',REVERSE (firstname)) > 0 THEN
		REVERSE(SUBSTRING(REVERSE (firstname),PATINDEX('%.%',REVERSE (firstname)),LEN(firstname)))
END,
'FIRSTNAME' =
CASE
	WHEN PATINDEX('%.%',REVERSE (firstname)) = 0 THEN firstname
	WHEN PATINDEX('%.%',REVERSE (firstname)) > 0 THEN
		REVERSE(SUBSTRING(REVERSE (firstname),-1, PATINDEX('%.%',REVERSE (firstname))))
END,
 
firstname,
lastname,
suffix
FROM contactbase

The following simple code sample turns the regular text field in CRM 4.0 to a content aware field allowing only typing numbers. If other then number character (specified as keycode) is typed – the input is ignored and the user is “forced” to type in only predefined characters. With a bit of effort this approach can be used to create more advanced check routine following certain mask or pattern – e.g. telephone number checking a dash position etc.

ValidateCharacters = function() {
    var key = event.keyCode;
    if(key >= 48 && key <= 57) {
        event.returnValue = true;
        return true;
    }
    else {
        event.returnValue = false;
        return false;
    }
}
 
crmForm.all.<fieldname>.attachEvent('onkeypress',ValidateCharacters);

One of the customers was facing a problem with connection to CRM 4.0 using Outlook Client. Client was installed without any problems, but within configuration following error occured. The error was not saying anything special – something like: The underlying connection was closed: an unexpected error occured on receive.

After reinstallation of Outlook Client the error was still present, but the interesting point was that after providing wrong server name and port the error was different – so apparently the connection to the server was ok (not blocked by firewall etc.). After some time and investigation (~6hrs),  I found out that actually the server installation was moved from one server to another – and the installation was attached to an existing database, which left the old server entries in MSCRM_CONFIG DB.

After checking following entries in [MSCRM_CONFIG].[dbo].[DeploymentProperties]:

• ADSdkRootDomain
• ADWebApplicationRootDomain
• AsyncSdkRootDomain

The first two were set to the old instance of the server together with the old port number and the last was left empty.

Setting all the entries (Open the table in SQL Mgmt Studio, or write an update statment) to the new value of <servername>:<port> and restart of the IIS running the CRM server using iisreset command fixed the issue completely.

CRM 4.0 supports user roles. Each role contains user access permissions to entities and functions of the system. On top of that single user can be assigned multiple roles and in that case user access permissions are combined and must be tested correctly.
Testing user access permissions can be tedious task, especially when you actually log on and log off each time you need to swith the user. There are two options for making this task a bit more convenient.

Scenario 1 – Local installation

Use Run As option for running second instance of Internet Explorer, and enter the other user credentials. Shift+RightClick the IE icon and select Run As different user option. Then enter the login and password of the other user, and you can test his / her user access permissions.

Scenario 2 – IFD

In case of IFD deployment and form authentication, there is a different approach. You start a new session in IE and log in using the other user credentials. New Session option is available from IE 8.

Password field in CRM 4.0 out-of-the-box functionality is missing. With the standard approach you should not even feel the need to implement one – since everything is taken care of by having AD, user accounts and single sign on capabilities. Well, it works in most cases, but given you face an extension requirement that goes beyond the basic framework capabilities e.g. you are connecting to CRM from external application and you want your user credentials stored and managed in CRM you might find following hint useful.

1. Create new entity

Name it e.g. Credentials (you can control access rights to this entity better than in case of extending e.g. Contact directly)

2. Add fields for user credentials

Login, password. You may want to add some other fields you like expiration date / time.

3. Place fields on the Form

so that they are accessible from Javascript.

4. Add Javascript to the OnLoad event

Edit the Form / Form properties / OnLoad event and add following code:

crmForm.all.new_password.style.display = "none";
 
var pwd = document.createElement("
<input type="password" />");
crmForm.all.new_password_d.appendChild(pwd);
pwd.onkeyup = function()
{
    crmForm.all.new_password.DataValue = b64_md5(pwd.value);
};

The code manipulates the Form. It hides the new_password field placed on the form and instead it creates and displays new input of type password. On typing event it copies the value into the original field (new_password).

Additionally you may want to store the password encrypted in DB. In this case there’s a b64 MD5 hash function used. Of course the function call should not be done every time you type a character – it is not an effective type of coding practice, but in this example it is sufficient and works pretty much okay.

For implementation of MD5 hash in Javascript please refer to e.g. Javascript MD5. Just make sure you set the correct (the same) padding when storing the hash and comparing (i.e. set the padding to “=”).

b64pad="=";

There is nice and working post about Filtered Lookups in CRM 4.0 here:

http://advantageworks.blogspot.com/2008/02/pseudo-filtered-lookup-dialog-in.html

What it does, it basically adds to additional params of one lookup search name from the other lookup. It works great until you are not using special chars e.g. (ľščťžýáíé).
Then you definitely need to encode the string via Javascript function encodeURI(), so that the last line looks like:

target.additionalparams = 'search=' + encodeURI(name);

Otherwise you end receiving squares instead of special chars and no search results at all.

Original code (on-load):

document.FilterLookup = function(source, target)
{
    if (IsNull(source) || IsNull(target)) { return; }
    var name = IsNull(source.DataValue) ? '' : source.DataValue[0].name;
    target.additionalparams = 'search=' + name;
}

Original code (on-change):

document.FilterLookup(crmForm.all.awx_stateid, crmForm.all.awx_cityid);

MS CRM 4.0 support for conditionally required fields is missing from the standard framework. However you can try a piece of Javascript to do the job.

Req property

Fields contain property called “req”.

Available values are

• 0 – normal
• 1 – recommended
• 2 – required

crmForm.all.my_attribute.req = 2;

Labels

Labels can be set to:

• n – normal
• rec – recommended
• req – required

document.getElementById(crmForm.all.my_attribute.id + "_c").className = "req";

Workflows in MS CRM 4.0 contain protection mechanism preventing infinite loop in case the workflow calls itself. The default detection works even if the workflow calls child workflows and number of these calls exceed 7 within 1 hour.

In case you need to update the limit (provided you know what you are doing and your workflows are written correctly) you may try this special feature of Deployment Config Tool. You can download the tool from Microsoft.

Run the command line utility with parameters to increase the limit to e.g. 15 calls

microsoft.crm.deploymentconfigtool.exe workflowsettings update -maximumdepth:15

When you start customizing the CRM 4.0 entity you  basically add attributes and then try to modify the views – add entity view columns, reorder them etc. But what if after clicking on the column header with your mouse nothing happens. You simply cannot select the column for modification – there is no green box around the column header.

There are two ways how to fix the problem:

1. Disable the Internet Explorer Enhanced Security Configuration Settings (follow the instructions for you OS – it is different in 2003 and 2008 windows server) environments.

2. Add about:blank to local intranet zone in IE settings.