How to Extract Dynamics CRM Fields to an Excel Sheet

This post is about how to extract Dynamics CRM fields into a Microsoft Excel sheet.  We might need this info for data migration / integration tasks. For instance mapping of Dynamics CRM fields against an old system from which we need to migrate  / integrate data.

Xrm Tool box provides a tool with this feature. Download link is given below.

http://www.xrmtoolbox.com/download.html

1.After download, extract the zip file to a custom folder.

2.  Open the XrmToolBox.exe. Choose Metadata Document Generator

3. Next step is to connect to the organization. So click yes.

4. Create a new connection by clicking on the New Connection button.

5. Create the connection with your CRM server / Online version details.

6. Once connected, Xrm tool box loads all the entities. Now choose your entity or all entities as per your requirement.  Please note the highlighted buttons. Click on Generate document button after choosing the options.

7. Thats it really. You could find your excel document with chosen attributes in the chosen folder.

Thanks to Xrm Tool Box !

Power Map

Power map is a 3-D visualization tool for Excel. Power map is based on Microsoft Bing maps. As the name implies its a powerful visualization map. Power map is more powerful when its used in conjunction with Power BI. Lets take a look.

Power map could detect the following data ( geolocation data)

• Latitude/Longitude (formatted as decimal)
• Street Address
• City
• County
• State / Province
• Zip Code / Postal Code

• Country/Region

Version used for this post:
Microsoft Office Professional Plus 2013
Power BI was activated from the office 365 login in order to utilize Power BI features.
Microsoft Excel -> File-> Options->Add-ins->Manage option set-> COM Add-ins->Go->Select Microsoft Power Map-> OK

"If you have a subscription for Microsoft Office 365 ProPlus, you have access to Power Map for Excel as part of the self-service business intelligence tools. Whenever any new Power Map features and performance enhancements are released, you'll get them as part of your subscription plan."
(Ref:https://support.office.com/en-us/Article/Power-Map-for-Excel-82d65bd7-70c9-48a3-8356-6b0e82472d74 )

We are considering 2 samples in order to understand power maps.

I)  Common scenario in Microsoft Dynamics CRM-  To plot the number of opportunities from certain area ( For instance Based on post code or City ). Its a simple scenario without using Power BI features.

II) Ref : https://eriksvensen.wordpress.com/2015/07/06/visualize-the-danish-mobile-network-history-coverage-using-powerquery-and-powermap-powerbi/

A sample mentioned in Erik Svensen's blog - Danish mobile network history and coverage. 

This sample is an amazing scenario.Full Credit: Erik Svensen with reference to his blog. Tak Erik Svensen !. This is a scenario using power BI features.

Scenario I: Plot the number of Opportunities based on the post code.

We have a table with postcodes ( UK  post code)  and number of opportunities in Excel as shown below.

1. Select the whole table first and then Insert tab-> Map-> Launch Power map

2. Create a new tour

3. Choose Geography and then Geography and map level as shown below. Select Zip against post code. And click next.

4. Drag the Number of Opportunities field to the Height as shown below.

5.  Use the tilt down / up , rotate right / left arrows. Also zoom in / out could be used to have better view.

6. Map label was one of favourites feature.

7. Heat map view. Heat map button is highlighted.

Scenario II

Ref : https://eriksvensen.wordpress.com/2015/07/06/visualize-the-danish-mobile-network-history-coverage-using-powerquery-and-powermap-powerbi/

A sample mentioned in Erik Svensen's blog - Danish mobile network history and coverage. 

This sample is an amazing scenario.Full Credit: Erik Svensen with reference to his blog. Tak Erik Svensen !. This is a scenario using power query- power BI features.

URL for Danish mobile network with reference to Erik's blog - http://mastedatabasen.dk/Master/antenner.json?maxantal=1000 ( In this sample we are selecting only 1000 records)

1. Power BI- Power Query is very useful for bringing data. Please note the tab Power query and the button from Web. Enter the provided url and press ok.

2. Records would be loaded as 'Record'. Click on the To Table button

3. Default values are ok in our case. Press ok



4. Next step is a bit tricky. Please note the highlighted tiny button.

5.  Lets learn Danish language : ) . We are not selecting all fields but only the necessary ones in this sample. Postnummer is post code. vejnavn is street name. Kommune is municipality. We could choose whichever fields we like. As i mentioned at the beginning Power map can detect certain data like post code ( see the full list at the beginning of this post)

6. Expand columns as shown below. Then you could  see the real data.

7. After expansion, you could see data like below. Once we have proper data, click on the close and load button.

8. Next step is to launch power map from the Insert tab. Then click new tour.

9. Select Column1.postnummer.nr  column and then select zip code against it as shown below. Click next.

10. For this sample height is not important. You could play around with it if you prefer. Its not chosen in the below picture.  You could use the tilt down / up , rotate right / left arrows. Also zoom in / out could be used to have better view.

11. Please note the buttons Capture screen ( once you click this button you could paste in paint or word etc just like screen shot, Create video etc. Here is a capture screen sample. Users could create video for presentations using the create video. Power map is very useful in Geomarketing.

Dynamics CRM 2016 Release preview guide

Microsoft Dynamics CRM 2016 release preview guide could be downloaded from the below link.

http://download.microsoft.com/download/D/5/D/D5D38A09-6A5C-4DC0-8649-3A4F39A8F8F3/Microsoft_Dynamics_CRM_2016_Release_Preview_Guide.pdf

JS Development in Dynamics CRM- Tips

    This post is regarding a javascript development tip which is very useful in Dyamics CRM. It was not my idea but my colleagues' (Irina and Alex). And it was an eye opener for me. As we all know we write js code on form load as well as on change event of different fields in Dynamics CRM. At a later point have you ever struggled to see on which fields we have js code ?  I have. Their suggestion was to use attach events by using the addonchange js function in Dynamics CRM. They also mentioned that this would make the js code more transparent. addonChange function is nothing but a js function to be called when the attribute changes. Please read the comments below. Thanks to Irina and Alex for this recommendation.

//Form Load

//onLoad function should be added to the Form load event of Form editor as usual

function onLoad() {

    FillData();// FillData Function call on Form load

    attachEvents();

}

//Function to attach events to different fields

function attachEvents() {

    //Attach Onchange event of lookup1(new_City) and lookup2(new_Profession)

    //You could attach as many fields as you prefer

    //Please note that the function name is passed as a parameter to addonchange function

    //Its the function we would like to call during onchange event of these fields.

    Xrm.Page.getAttribute("new_City").addOnChange(FillData);

    Xrm.Page.getAttribute("new_Profession").addOnChange(FillData);

    //No need to add FillData function call on the onchange event of each field on

    //the Form editor instead here we attach it using addOnChange function.

}

//Fill data based on lookup1 - new_City and lookup2 - new_Profession

function FillData() {

    //Js code to fill in the field based new_City and new_Profession

     //For instance you could fill another field using these 2 field values

}