Introduction

When dealing with the account entity you may decide to display the year founded as part of the company information. This information can come from various public sources of information. It feels natural to think of year as a date since it is part of a date. But of course it is really in fact a “date part”. It is part of what makes up a date but in and of itself is actually just a number. Handled alone it is best stored as a numeric value which makes it easier and faster for filtering and searching purposes. The problem is that if you try to store a date as a numeric value in CRM 2011 that value will be displayed with a comma on the form. If you can live with that fine, but if that bugs the heck out of you then you have to look at the pros and cons of other options.

Let me just preface this discussion with my option that when deciding between making it a date or a text field in order to fix formatting, text is the way to go. Maybe I’m preaching to the choir but I had this discussion on this topic recently. I’ll briefly go over some points on text versus the date data type.

Validation

It is true that as a text field that users could (if you let them) enter invalid data, but you could say the same thing about pretty much every other field on a form. For example, users can enter anything they want into say a Stock Exchange and Ticker Symbol fields if you let them. This is fixed with a couple lines of JavaScript to only allow numbers to be entered if it is that big of a deal. While a date field will provide data type validation for you it is doing it in an invalid way for its purpose.

Searching

It is also true that searching in advanced find would be a little more interesting since you would have to use “begins with” and “ends with” operators if you want to search a date range. But then again look what happens when you try to search by date data type. The user is going to have to select an actual date (not a year) which means if you want to be sure that you find everything you have to start with 1/1/year and end with 12/31/year anyway. In either case any user that wants to search the field is going to have to be educated on the proper method of retrieving accurate results.

Data Entry

Here is what year founded looks like on a form as a number and a date.

If the premise to change the year field away from a number is to get rid of the comma number formatting then it is actually worse as a date field since the month and day are invalid pieces of information. The user is forced to come up with an arbitrary month and day before they can enter a year.  This means you will eventually get dates all over the place which in itself is deceptive because someone may think the date itself is correct.

Here is what the user will see if they try to enter a year:

Not only that but if the user tries to even use the calendar pop up they have to know to click on the month title to get the year display and then most likely have to click many times to get to the target year.

Data Import

If you are performing integration with other systems you may need to import a year field. Usually it will be stored as a numeric value or text data type. Making it a date in CRM creates the need to convert this information into an arbitrary date. Knowing how this typically goes whoever is doing the import will just slap January 1 on every record. Again this is somewhat deceptive with month a day being meaningless information. To me is seems like extra work for a field that isn’t a date anyway.

Conclusion

Number is the best data type choice when dealing with a year field. It is unfortunate that Microsoft didn’t implement any built in field formatting functionality for numeric values in CRM 2011. Yes I know you can do JavaScript but it would have been nice to have it out of the box.  I would imagine it being a requested feature that may find its way into the next major release, but for now text is the lessor of the two evils if you can’t deal with seeing a comma in your year fields.