Find contacts with upcoming birthdays
Some of us would find searching for birthdays based on the month or day irregardless of the year in the birth date field for the Contact entity interesting. The methodology I am going to describe in my first CRM Team blog can be performed for other date fields in the Account or other custom entities as well. Note that these directions are for birth dates in the format of Month\Day\Year.
Microsoft Dynamics CRM 3.0 stores it’s dates in a format that includes both the date, time, and year. This is good for keeping accurate date storage for things like contact’s birthdays, creation, and modification dates and times. However, this is problematic with the Microsoft Dynamics CRM 3.0 Advanced Find feature because it does not allow for searching for a date or a range of dates without also including the year. This means that if you searched for all of your Microsoft CRM Contacts who have a birthday in the next month, that it would return 0 results. This is because the search query generated would be something like the following pseudocode: “Select contact.fullname,… where contact.birthdate < 06/01/2007 and contact.birthdate < 07/01/2007”
Performance Notes: It is recommended to test these in a test environment first because it may exert an increased load on the Microsoft CRM SQL Server depending on which query or queries below are used. Also limit the data returned if applicable to the data needed and do not return all columns or all records unless absolutely necessary.
Indexes to create for better performance with Contact birthdate queries:
CREATE NONCLUSTERED INDEX [ndx_birthdate] ON [dbo].[ContactBase]
( [BirthDate] ASC
)WITH (FILLFACTOR=90)
ON [PRIMARY]
–If using custom contact fields, create these indexes
CREATE INDEX ndx_Contact_new_birthday_day on ContactExtensionBase(new_birthday_day)
CREATE INDEX ndx_Contact_new_birthday_month on ContactExtensionBase (new_birthday_month)
CREATE INDEX ndx_Contact_new_birthday_year on ContactExtensionBase (new_birthday_year)
METHOD 1: Adding Birthdate day, Birthdate Month, and Birthdate year fields to the Contact entity and using the Javascript Onsave method.
Note: This method will not populate the Birthday_Month, Birthday_Day, and Birthday_Year fields until a new contact is saved or an existing contact is saved. The other thing to note is that this will add slightly to the times it takes to open the contact form due to the addition of the javascript code if users are located in remote sites with higher latency connections.
1. Connect to the Microsoft CRM Web site as a Microsoft CRM System Administrator or a user who has the System Customizer Role
2. Click on Settings in the Left hand Navigation toolbar and Click on the Customization button.
3. Double-Click on the Contact entity (Schema Name = contact).
4. Click on Attributes in the Left Hand Navigation bar in teh Entity: Contact form.
5. Click on the New button on the action bar.
6. Type in the the following names for each corresponding field listed: Display Name = Birthday_Day; Schema Name = New_birthday_day; Requirement Level = No Constraint; Searchable = Yes; Type = int; Format = None; Minimum Value = 1; Maximum Value = 31; Description: Field to allow searching by Birthday days regardless of the year of the birthday.
7. Click on the Save and New button to create a new attribute with the following information in it: Display Name = Birthday_Month; Schema Name = new_birthday_month; Requirement Level = No Constraint; Searchable = Yes; Type = int; Format = None; Minimum Value = 1; Maximum Value = 12; Description: Field to allow searching by Birthday Months regardless of the year of the birthday.
8. Click on the Save and New button to create a new attribute with the following information in it: Display Name = Birthday_Year; Schema Name = new_birthday_year; Requirement Level = No Constraint; Searchable = Yes; Type = int; Format = None; Minimum Value = 1900; Maximum Value = 3000; Description: Field to allow searching by Birthday year.
9. Click on the Save and Close button to close the Birthday_Month attribute.
10. Click on the Forms and Views link in the left hand navigation short cut bar.
11. Double Click on the Form to open the main form.
12. Click on the Add Fields button and add the three fields created above (schema name = new_Birthday_day, schema name = new_birthday_month, schema name = new_birthday_year) to the form on the Details tab in the Personal Information Section.
13. Click on the Form Properties in the Common Tasks bar.
14. In the Form Properties window, select the OnSave button and click on Edit and add in the following code:
15. Mark the check box for “Event is Enabled” and paste in the following code into the body of the script section on the Event Detail Properties — Webpage Dialog form.
var bMonth = crmForm.all.birthdate.DataValue.getMonth() + 1;
var bDay = crmForm.all.birthdate.DataValue.getDate() ;
var bYear = crmForm.all.birthdate.DataValue.getYear()+1900;
crmForm.all.new_birthday_month.DataValue = bMonth;
crmForm.all.new_birthday_day.DataValue = bDay;
crmForm.all.new_birthday_year.DataValue = bYear;
crmForm.all.new_birthday_month.ForceSubmit = true;
crmForm.all.new_birthday_day.ForceSubmit = true;
crmForm.all.new_birthday_year.ForceSubmit = true;
16. Click on the Dependencies tab in the Event Detail Properties — Webpage Dialog window and add the Birthday, Birthday_Day, Birthday_Month, and Birthday_Year fields to the Dependent fields list.
17. Click OK to close the Event Detail Properties — Webpage dialog window.
18. Click on the OnLoad Event in the EventList in the Form Properties — Webpage Dialog and click on the Edit button.
19. Mark the check box for “Event is Enabled” and paste in the following code into the body of the script section on the Event Detail Properties — Webpage Dialog form.
crmForm.all.new_birthday_day.disabled = true;
crmForm.all.new_birthday_month.disabled = true;
crmForm.all.new_birthday_year.disabled = true;
19. Click on the Dependencies tab in the Event Detail Properties — Webpage Dialog window and add the Birthday, Birthday_Day, Birthday_Month, and Birthday_Year fields to the Dependent fields list.
20. Click OK to close the Event Detail Properties — Webpage dialog window.
21. Click on Save and close to save the Form: Contact form.
22. Click on Save and Close to save the Entity: Contact form.
23. Either choose “More Actions | Publish All Customizations” or highlight the Contact Entity and Click on the Publish button on the action toolbar.
Note: This method requires that existing contacts be opened and re-saved in order for the new_birthday_day, new_birthday_month, and new_birthday_year fields to be updated. It is recommended for better performance to use Method 5 with a pre-callout since the new_birthday_day, new_birthday_month, and new_birthday_year fields will not have to be added to the form for that method.
To search for contacts with birthdays in a certain month, create an Advanced find similar to the following with these steps:
Advanced Find Example 1: Search for all active contacts with birthdates in April
1. Click on the tools menu and select Advanced Find…
2. Click on the New button in the Advanced Find form.
3. Set the “Look For” field to be Contacts and change the Saved View to Active Contacts (a different view could be used if needed, i.e. ‘My Active Contacts’ to further restrict to only contacts owned by the current user)
4. Under the filter conditon of “Status equals active”, add a new filter condition of “Birthday_Month” Equals 4
5. Click on the Find Button to find all active contacts with a birthday in the month of April.
Advanced Find Example 2: Search for all Active contacts with birthdates from April through June
1. Click on the tools menu and select Advanced Find…
2. Click on the New button in the Advanced Find form.
3. Set the “Look For” field to be Contacts and change the Saved View to Active Contacts (a different view could be used if needed, i.e. ‘My Active Contacts’ to further restrict to only contacts owned by the current user)
4. Under the filter conditon of “Status equals active”, add a new filter condition of “Birthday_Month” Equals 4
5. Add a new filter condion under the Birthday_Month Is Greater than or Equal to 4 that has Birthday_Month Is Less Than or Equal To 6
5. Click on the Find Button to find all active contacts with a birthday in the month of April, May, and June.
Advanced Find Example 3: Search for all active contacts with birthdays on a certain day.
1. Click on the tools menu and select Advanced Find…
2. Click on the New button in the Advanced Find form.
3. Set the “Look For” field to be Contacts and change the Saved View to Active Contacts (a different view could be used if needed, i.e. ‘My Active Contacts’ to further restrict to only contacts owned by the current user)
4. Under the filter conditon of “Status equals active”, add a new filter condition of “Birthday_Month” Equals 5
5. Under the filter conditon of “Status equals active”, add a new filter condition of “Birthday_day” Equals 4
6. Click on the Find Button to find all active contacts with a birthday on the fourth day of the month of May
I’ll talk about another way to do this in an upcoming blog.
