Dana Sosa

YES! You CAN maintain multiple CRM entity views in a single Dynamic Excel Workbook!

Nick Elders, one of my favorite clients who works for Microsoft Dynamics CRM 4.0 user Community Reinvestment Fund, USA taught me something awesome today. 

I was under the impression that you needed to create ODBC data connections to Microsoft Dynamics CRM filtered views manually in Microsoft Office Excel to create links to multiple Microsoft Dynamics CRM views in a singe dynamic Excel file on multiple worksheets (tabs).  Not something you’re average CRM end user is equipped to do.

But Nick proved me wrong.  And it’s so simple.  If you know how to export to Excel from Dynamics CRM and if you know how to copy and paste data, then you can do it too!

First, what is a “Dynamic Excel Workbook”?

When you export Dynamics CRM data to Microsoft Excel, you have the option of exporting a STATIC worksheet or DYNAMIC worksheet.  Exporting a static worksheet means that it’s a point in time snapshot of the data from CRM.   A dynamic worksheet becomes an Excel file that refreshes the data from the Microsoft Dynamics CRM database each time it’s opened.

Furthermore, if you export a dynamic worksheet (or PivotTable) that you think will be useful to other Microsoft Dynamics CRM users, you can add the list as a Report in CRM, and then share it with others, or make it available to all users.

And, if the recipients are in the same domain as you, and are also Microsoft Dynamics CRM users, you can e-mail a dynamic Excel file, or store it as a shared file.  When recipients open the dynamic file, they will see only the data they have permission to view in Microsoft Dynamics CRM (based on their security role), so the data they see may be different from what you see.  But you’re all accessing the same dynamic Excel workbook!

So, how do you do it?

It’s simple:

1)      Export View 1 as a “dynamic worksheet” to an Excel workbook.

2)      Add a new sheet to the workbook created in step 1.

3)      Export View 2 (from the same or a different entity) as a “dynamic worksheet” into a new Excel workbook.

4)      Copy all rows and columns from View 2’s worksheet to your “clipboard”

5)      Paste the contents of the “clipboard” (the copied rows and columns from View 2’s worksheet) into View 1’s new sheet, created in step 2.

Note:  When you’re opening with the Microsoft Office Excel file:

If you are using Excel 2003, click Enable automatic refresh when prompted.

If you are using Excel 2007, you get a message that says the file you are trying open is in a different format than specified by the file extension. Click Yes.  Then if you get the security warning Data connections have been disabled, click Options, and then click Enable this content, and then click OK.

What Happens To CRM When I Delete a Record in Outlook?

So… you say you’re using Microsoft Dynamics CRM 4.0 and you’re using Microsoft Office Outlook… well, then you’re naturally using the Microsoft Dynamics CRM for Outlook client!   (If you’re not, you can download it from the link.)

One of the most common questions I hear when I’m training our customers on the Microsoft Dynamics CRM for Outlook client is a variation on this one:

WHAT HAPPENS TO CRM WHEN I DELETE A RECORD IN OUTLOOK?

Well, here’s an answer:

The outcome is the same whether you’re using Microsoft Dynamics CRM 4.0 for Outlook or Microsoft Dynamics CRM 4.0 for Outlook with Offline Access when you delete a record that is linked to CRM.  The only difference will be in the timing of the deletion from the Dynamics CRM database if you are in Offline mode with the Microsoft Dynamics CRM 4.0 for Outlook with Offline Access client when you delete the record in Outlook.

This table describes the various conditions that will apply when you delete records in either Outlook or Microsoft Dynamics CRM and how they will be synchronized.

A few other things to be aware of when you’re working with the Microsoft Dynamics CRM 4.0 for Outlook client:
 

·         De-activating a Contact in CRM has no impact on the contact record in Outlook.

·         The synchronization process only updates Outlook records when the linked CRM record has been modified since the last synchronization. 

A good example of this is:

Dana Sosa is a Contact of yours under the Inetium Account in Dynamics CRM.  You and other users synchronize Dana Sosa to your Outlook Contacts.

You update the Inetium Account in CRM to reflect its legal name:  Inetium LLC.

When you and your colleagues synchronize CRM and Outlook next, Dana Sosa’s Company Name has not been changed in the Outlook Contact record.

You’re perplexed.  Dana’s “Parent Customer” name looks accurate in the CRM Contact record….

This is because the CONTACT record was NOT modified in CRM.   The Account record was modified by the name change in CRM – not the Contacts related to the Account.  Accounts do not synchronize with Outlook objects, only Contacts (e-mail, tasks, and appointments) do.

In order for the Company Name change to be accurately synchronized to Dana Sosa’s contact record in Outlook, a change must be made and saved to my CRM contact record (thus changing the modified date.)

This is a great use for a “Company Name Change” manual workflow…  but that’s a topic for another blog post…

·         The “local data groups” option on the CRM Menu add-in is used to configure BOTH what data will be taken Offline if you’re using the Microsoft Dynamics CRM 4.0 for Outlook with Offline Access AND which records will be synchronized to your Outlook Contacts, Mail, Tasks and Appointments from Dynamics CRM.

Be cognizant of this if you intend to synchronize Outlook with your PDA!

If you are using the Microsoft Dynamics CRM 4.0 for Outlook with Offline Access client, you will need to take into consideration how much CRM data you want to take Offline with you (think Accounts, Contacts, Opportunities and their related Activities so you can work with it ALL when you’re on an airplane) versus how much data you want to synchronize with your mobile device (think Contact records for phone numbers and e-mail addresses – you probably really only want to see the Contacts you work with regularly on your phone.)

Because “Local Data Groups” define the synchronization for BOTH the Outlook objects and the Offline CRM database, you may have to get creative with how you configure your “Local Data Groups” if you have a complex record ownership model.