Tracking the Date Fields by Date, Month or Year

-- Update -- Before you use this code, you may want to look at this Blog Post. It contains a number of fixes and is alot cleaner than my code. :-) (This article goes up 8/11/07)

So one of questions posed on one of our internal distribution groups was a question from a partner on how to do something I had never thought of before… And it turns out it is a limitation of the advanced find feature of Microsoft CRM 3.0.

In this case the partner wanted to be able to run a query to select all of the contacts with a birth date in a specific month. (i.e., give me all contacts born in May.) Guess what the advanced find doesn’t have that logic built into it. L

The good news is that fixing it is WAY easy. (It was with some help from Erika in Developer Support.) So in my case I used the anniversary field.

1. Create a Field with the type of Picklist in my case I called it AnnMonth

2. Create values in the Picklist of January through December. (In order… So January should be 1 and December should be 12 in the list of values.)

3. When you place the field on the form, click the disable button so as not to allow for end user input.

4. Place the following code on the OnChange of the anniversary field, which is on the contact form. (Or if not, place it there. J)

// here we are declaring the variable of AnnDate, which is the

// whole anniversary date. We are also making sure it is in date

// format, which it should be, so this step is probably

// redundant.

var AnnDate = new Date (crmForm.all. anniversary.DataValue);

//here we are using the Jscript command to get the date from the

// Date field and get just the monthvar AnnMonth = AnnDate.getMonth();

//Due to the way that Jscript handles the get month, 0=January,

// so we need to add 1 to make it match the picklist we created

// earlier
var AnnMonthInsert = AnnMonth + 1;

//We are now taking that value and inserting it into the form.
crmForm.all.new_AnnMonth.DataValue = AnnMonthInsert.toString();

5. Publish the results and you are all set. You can now do search of people with dates in months, etc… J

If you are interested in going beyond the months, these commands will get you on the right path:

getFullYear () – Pulls the Full Year (i.e., 1999)

getDate() – Date of the Month (i.e., the 20th)

getDay() – Pulls the Day of the week (0=Sunday, so you would probably want to do a picklist and do the plus one trick above. J)

This is what it should look like when done


Jason Hunt from Invoke Systems showed me a way to do it without all of the variables, so please don’t make fun of my lousy code writing skills. J If I can find his code, it will be appended here below, so you can see what it should look like. J