The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Find contacts with upcoming birthdays III

Find contacts with upcoming birthdays III

  • Comments 3

This is the final installment in the trilogy.

METHOD 3: Using Excel Dynamic PivotTable

1. Go through the steps in method 1 to create the birthday_day, birthday_month, and birthday_year fields for the Contact entity.

2. Use a Contact View or Advanced Find view to see a group of contacts

3. Click on the Export to Excel toolbar button and select the Dynamic Worksheet option.

4. Click on the Select Columns button and add columns like the following: Address1: City, Address1: Phone, Address1: Post Office Box, Address1: Street 1, Address1: Primary Contact Name, Address1:State/Province, Address1: Zip/Postal Code, Birthday, Business Phone, FirstName, FullName, and LastName (include any fields relevant to data needed).

5. Click on Open to open the Contacts_Advanced_Find_View.xls file (if you used the Advanced Find view)

6. Click on the button in Excel to Enable automatic refresh to allow refreshing the Microsoft CRM Data.

7. Right Click on the Pivot table and choose the Pivot Table Wizard item.

8. Click on the Edit Query button on the External Data toolbar in Microsoft Excel.

9. Click OK on the Microsoft Excel message "If you modify the query, columns that you deleted from teh Excel external data range will reappear as new columns, unless you also eliminate those columns from the query."

10. Click OK on the Microsoft Query message indicating "This query cannot be edited by the Query Wizard."

11. Click on the SQL toolbar button in Microsoft Query.

12. Copy in one of the Filtered View SQL Statements from Method 2 (in this example, we will use the query below to get the Contacts with birthdates in the next 7 days.

SELECT FilteredContact.fullname, 'http://CRMServer:5555/sfa/conts/edit.aspx?id={'+Convert(varchar(48),ContactId)+ '}', LEFT(DateAdd(dy,0,birthdate),11) AS 'Birthday', FilteredContact.address1_telephone1, FilteredContact.accountidname, FilteredContact.telephone1, FilteredContact.address1_city, FilteredContact.address1_postofficebox, FilteredContact.address1_primarycontactname, FilteredContact.address1_line1, FilteredContact.address1_postalcode

FROM Adventure_Works_Cycle_MSCRM.dbo.FilteredContact FilteredContact

WHERE (isDate(BirthDate)=1) AND (FilteredContact.statecode=0) AND (datepart(dy,birthdate)>DatePart(dy,GetDate()) And datepart(dy,birthdate)<DatePart(dy,DateAdd(dy,7,GetDate())))

ORDER BY datepart(dy,birthdate), FilteredContact.fullname

13. Click on File Return Data to Microsoft Office Excel

14. Note that if the columns in the query are different than the ones used in the initial Dynamics export to Excel, then click on the Data Range properties button in the Microsoft Excel External Data toolbar and mark the Checkbox for "Include field names" to show the field names of the columns selected. Otherwise, you will have to manually change the column names in the first Excel row to match those in the query.

15. This Dynamic Excel worksheet can now be saved and/or used to print out information regarding contacts that have upcoming birthdays.

16. You can also use the corresponding hyperlink in each record to open in Internet Explorer the Microsoft CRM contact form for that particular contact. The following is an example of the hyperlink created:

http://CRMServer:5555/sfa/conts/edit.aspx?id={714E0C0A-75CF-44D8-8F69-D6ED69396612}

METHOD 4: Use Filtered view queries to create a new SQL Reporting Services Report.

1. Use the queries in Method 2 to create a new report or to modify an existing Microsoft CRM Report (it's recommended to create a new report).

2. Refer to the Report Writer's Guide topic in the Microsoft CRM SDK for instructions on how to create and publish a new Microsoft CRM Report.

METHOD 5: Using a pre-callout to populate new fields for the Birthday day, Birthday Month, and Birthday Year part of the Birthdate field.

This method will perform better than Method 1 does with the Javascript due to less information having to be transferred to the client each time the contact form is opened. Note that the method shown here is only for the pre-create callout on the contact entity. There should also be corresponding code written for pre-update and pre-delete configurations to handle cases when the birthdate field is updated or deleted from the Microsoft CRM Contact form.

File:  bDayPreCreateContact.cs

Add a crmsdk Web reference to the CRM Server used with the following URL in a C# Class project like the following:

Folder Name:  crmsdk

URL Behavior:  Static

Web Reference:  http://crmserver:5555/mscrmservices/2006/crmservice.asmx

The Pre-create callout code gets the birthdate value and parses it into a day, month, and a year field and adds it back into the entityXml that is used in CRM.  This means that we don’t have to have the new_birthday_day, new_birthday_month, and new_birthday_year fields on the Microsoft CRM form unless we want to see them there.  We can use them in Advanced find searches though for any new records created with the callout code used below.  After adding this code and compiling it, you will need to perform the following steps:

1.  Copy the corresponding bDayCreate.dll and bDayCreate.pdb files to the %Program Files%\Microsoft CRM\Server\Bin\assembly\ directory on the Microsoft CRM 3.0 server.

2. Edit the Callout.config.xml file in the %Program Files%\Microsoft CRM\Server\Bin\assembly\ directory on the Microsoft CRM 3.0 server and add the following lines and save the file.

<callout entity="contact" event="PreCreate">

<subscription assembly="bDayCreate.dll" class="bDayCreate.BDayPreCreateContact" onerror="abort" />

</callout>

3. Restart IIS on the Microsoft CRM Server using a command like IISReset – this should be done in non-production hours if possible to minimize any downtime to CRM Users although a reset of IIS generally takes less than a minute to be performed.

using System;

using Microsoft.Win32;

using bDayCreate.crmsdk;

using Microsoft.Crm.Callout;

using System.Xml;

using System.Xml.Serialization;

namespace bDayCreate

{

/// <summary>

/// Summary description for Class1.

/// </summary>

public class BDayPreCreateContact: CrmCalloutBase

      {

public BDayPreCreateContact()

            {

//

// TODO: Add constructor logic here

//

            }

public override PreCalloutReturnValue PreCreate(CalloutUserContext userContext, CalloutEntityContext entityContext, ref string entityXml, ref string errorMessage)

            {

                  XmlDocument xdoc = new XmlDocument();

                  xdoc.LoadXml(entityXml);

                  XmlNamespaceManager entityNM = new XmlNamespaceManager(xdoc.NameTable);

                  entityNM.AddNamespace("xsi","http://schemas.microsoft.com/crm/2006/WebServices");

                  XmlNodeList Properties = xdoc.GetElementsByTagName("Property");

                  System.DateTime birthdate = new DateTime();

foreach (XmlNode n in Properties)

                  {

if (n.Attributes.GetNamedItem("Name").Value == "birthdate")

                              birthdate = DateTime.Parse(n.FirstChild.InnerText);

                  }

                  XmlDocument xdoc1 = new XmlDocument();

                  XmlNamespaceManager entityNM1 = new XmlNamespaceManager(xdoc1.NameTable);

                  entityNM1.AddNamespace("xsi","http://schemas.microsoft.com/crm/2006/WebServices");

string [] fields = new string[] {"new_birthday_day", "new_birthday_month", "new_birthday_year"};

int [] ifields = new int [] {birthdate.Day, birthdate.Month, birthdate.Year};

int i = 0;

foreach (string field in fields)

                  {

string v = ifields[i].ToString();

                        xdoc1.LoadXml("<Property type='CrmNumberProperty' Name='"+field+"'><Value formattedvalue='"+v+"'>"+v+"</Value></Property>");

                        XmlNode p = (XmlNode)xdoc.CreateElement("Property");

                        XmlAttribute type = xdoc.CreateAttribute("type");

                        type.Value = "CrmNumberProperty";

                        XmlAttribute name = xdoc.CreateAttribute("Name");

                        name.Value = field;

                        p.Attributes.Append(type);

                        p.Attributes.Append(name);

                        XmlNode x1 = (XmlNode)xdoc.CreateElement("Value");

                        XmlAttribute fvalue = xdoc.CreateAttribute("formattedvalue");

                        fvalue.Value = v;

                        x1.Attributes.Append(fvalue);

                        x1.InnerText = v;

                        p.AppendChild(x1);

                        xdoc.GetElementsByTagName("Properties")[0].AppendChild(p);

                        i++;

                  }

                  entityXml = xdoc.InnerXml;

return base.PreCreate (userContext, entityContext, ref entityXml, ref errorMessage);

            }

      }

}

The Microsoft CRM support team and consulting teams can provide customizations like this for a small fee if you are interested in having someone else do the customization or aren’t comfortable with doing a customization like this. If you are interested, please contact the standard Microsoft CRM Support numbers.

I will be working on the Pre-Update and Pre-Delete code for this example and it will go into a KB article. My team members and I have some additional customizations and topics that will be posted to this blog in a future date. If anyone has comments, additions, or corrections, feel free to leave them here.

Chad Rexin

  • OK, so all three of these posts are helpful but in all the wisdom of the CRM team at Microsoft we are saying this is the only way to get what should be very basic functionality?  

  • Thank you. We implemented a similar feature for client as well. Thanks -Tony

  • Nice methodology.. You saved my day...

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post