How to obtain FetchXML

OK. I think this comes up more often that you can remember. I hope this helps with anyone trying to learn CRM and how to put FetchXML queries to use.

1. Login into the CRM Web Application.
2. Go to the Entity you want to build a FetchXML Query.
3. From HomeGrid view of the Entity, select “Advanced Find”.
4. If you want FetchXML of an existing View, pick the view or if creating a new one, select NEW for the view.
FetchXML.xml from Advanced Find

5. Once you have built your Query (aka Saved Query), click on Download FetchXML. Save FetchXML.xml file.

Open it in a text editor and Viola, you have your FetchXML Query.

In case you were wondering how to get Layout XML, look up my other post here.

Enjoy!.

Advertisements

What’s new in Microsoft Dynamics CRM 2015 Fall Wave Release Code Name Vega

Editor’s note: Post I gathered this info is gone! Nowhere to be seen. As the article notes, this is pre-release/unconfirmed information on Microsoft’s release plans.

An updated version of the Microsoft Dynamics CRM roadmap surfaced today and it featured a release schedule beyond the Q4 2014 major release codenamed “Vega”, which may become “CRM 2015”. Moving into the year 2015, the Dynamics CRM roadmap continues to separate features between social, marketing, Parature, and core CRM.

Microsoft Dynamics CRM roadmap 2014 2015 (click to enlarge)

Microsoft Dynamics CRM 2014/2015 Roadmap

As for Dynamics CRM Vega, this is a release focusing on Microsoft Dynamics CRM Core product. The Fall Wave release Vega applies to both CRM Online and CRM On-Premise.

So Vega is apparently going to be officially named: CRM 2015. It will be released before end of this year (Q4 2014). It is expected to include some core product features in Dynamics CRM that many have been waiting for!

New features and enhancements in Vega Dynamics CRM 2015 are:

Business Process Improvements:

  • Rule-based branching
  • Single Edit experience
  • Support for multiple entity loops
  • Programmability through Client API
  • Active processes
  • Change of currently active process
  • Move Stages
  • Stage selection and Changing of currently active stage

User Experience Improvements:

  • Multi Entity Search Capability (horraayy!)
  • A new Advanced Search Button
  • Nested Quick Create forms

Mobility Features:

  • The addition of 29 new Languages
  • More Dashboards and new concept of Home-pages
  • Support of “Any Device”
  • Ability to have offline drafts

Enterprise Sales Features:

  • Entity Hierarchy
  • Cross-sell and Up-sell capabilities (also know as Next Buy Actions)
  • External Pricing features
  • Attribute Inheritance
  • Product Relationship
  • Bundles

XRM and Development Advances:

  • Complex calculations (Calculated Fields & Roll-up of values) (Yes!)
  • Hierarchical Relationship Property
  • Ability to query multiple entities
  • Hierarchical Security Models
  • Field Level Security for system fields (out of the box fields)

Analytics and Business Intelligence Enhancements:

  • Power BI in CRM Web client
  • Transactional reporting of CRM on Windows Mobile Client Application
  • Roll up on different fields across hierarchies

Specific for CRM Online (Microsoft’s Online First motto):

  • Self-Service
  • Sign up process Simplification
  • Global Expansion from 42 to 61 Markets (Latin America data center)
  • Multi-Geographical tenants
  • Scaling out Organization Synchronization
  • Minimal downtime Upgrades
  • Addition of Load balancing

Supported OS and Software Requirements:

  • Windows Server –2012 & 2012 R2
  • Windows Client – Vista, Windows 7 & Windows 8
  • ADFS – 2.0, 2.1, 2.2
  • SQL Server – 2012
  • Exchange Server –2010 & 2013
  • Outlook – 2010 & 2013

Software and OS no longer supported:

  • Windows Server 2008 & 2008 R2
  • SQL server 2008 & 2008R2
  • Windows Small Business Server ( all version )
  • 2008 Series Terminal Servers
  • Windows Vista
  • SharePoint 2007
  • Exchange 2007
  • IE 8, IE 9
  • Office 2007

This post has content from this Dynamics Community post

Product management team has identified features across eight categories where they will be focusing in 2015 and beyond

Microsoft Dynamics CRM post Q4 2014 wave

Enjoy!

DISCLAIMER: All of this pre-release information and is subject to change at any point in time.

 

Where is my Layout XML?

I’m writing this mainly because it gets asked so much and in some cases the newbies are trying to figure out where on earth am I going to get the Layout XML to add a custom view or modify the lookup to use a different view.

Here is a quick way of accomplishing this.

Problem:

Want to create a Layout XML string to be used in JavaScript on a form. Most of us know how to obtain the FetchXML using Advanced Find. that’s easy breezy.

Solution:

  1. Create an empty solution and add the Entity in question which you are trying to change. If you are querying deep and want to display related records field values.
  2. Create a Personal View with the required layout. Save and Publish.
  3. Export this solution as an UNMANAGED Solution. Save the ZIP file.
  4. Unzip the solution file.
  5. Open customization.xml file using Notepad++ and under SavedQueries node for the entity in question, look for the Personal View you created using Name.
  6. Viola, you will see the LayoutXML and FetchXML.

Happy Customization.

Dynamics CRM Report using Excel 2013, oData and PowerQuery.

Cannot say how excited was when I finally found a way to solve the problem of reporting Dynamics CRM Online data with all its restrictions. Hail Excel. Not any Excel, but Excel 2013. Well not just Excel alone, but it was the glue that is bringing different technologies together and made this possible. This is an extension of my previous post on getting around the 10K record limitation.

Working for my customer to build a report that brings Contacts and its associated Account information. These are the ways I tried and their limitations. This is a CRM Online deployment and we have close to 40K contacts and an almost equal number of accounts. I did narrow the record count to by using filters judiciously to not make the system crawl. With these restrictions in mind, here is what I did. Hopefully this keep head in proper shape by not banging on the wall.

a) Advanced Find: Very nice tool to quickly visualize if something possible and I was get all the columns required to provide the report from Contacts and its associated accounts. Viola, this was easy, so I thought. Ran the query and came back with, you guessed correctly, the first 5000 records only. OK, onto the next solution. (I know there is way and tool to increase this limit. Not an ideal scenario users running searches and ending up pulling so many records. )

b) Report to the rescue, so I thought. I took the FetchXML from the Advanced Find and fired up BIDS 2008 R2 and started to put together the report. (Check SDK for how to do this). I placed the correct columns from Contacts and Accounts in the report and hit preview and viola, I instantly got the report to run in Preview. Well, Preview was able to pull in the first 500 contacts that matched the criteria. So I created a NEW report in CRM, used the RDL file from BIDs and published it in CRM.  I was able to specify the criteria and clicked on “Run Report”. Well I should hand it to CRM, it did run, I’m guessing that it timed out due to heavy load of 33K records it was pulling for this report. So I was able to taste a small bit of the fruit, but the not the whole yet.

At this point, I’m wracking my brain on how can I pull 60K records from CRM Online and produce this report. If anyone can come up any other ways of producing this report, please do let me know. Maybe I’m doing something wrong.

c)These two articles from Surviving CRM Blog gave me this idea on dealing with ta large set of data. (http://niiranen.eu/crm/2013/06/dynamics-crm-data-visualization-with-geoflow/ and https://www.youtube.com/watch?v=1er6bu9nmlk ).

SOLUTION:

  1. Download and install PowerQuery for Excel 2013. (Update: PowerQuery has become a part of Excel 2013 with sp1).
  2. Export to Excel a small list of records, can be anything within CRM Org as a Dynamic worksheet. CRM will spit out an .xml file.
  3. Open this in Excel 2013. Under the DATA tab, click on Refresh from CRM to fetch the data and also complete authentication. Save the file as an .xlsx file. (This step is very important to our goal).
  4. Create a oData query using XRM Tools solution withont the org. Note: You cann create the query with related records. Using this in feed definition does not bring in the related record fields. oDate_Query
  5. In my case, I had to create two oData queries, one for Contacts and one for Accounts. In the contacts query, i did pull in the parent Customer Id field in order for me to join with Accounts in Excel.
  6. After you have the queries created and tested in the Browser, in the open Excel 2013 Workbook, create a new Sheet and select “From oData Feed” under DATA/GetExternal Data Tab/From Other Sources”.
  7. Paste the oData query into the link section and complete the dialog with default options. Create a New Sheet when fetching the data executing the oData query.
  8. Do the same for accounts as step 7 with query created in step 5.
  9. In order for records in these two sheets called “Contacts” and “Accounts” to be used in PowerQuery, they have to brought in as a Table from PowerQuery ribbon tab.
  10. You will end up creating two more sheets called “Table_Contacts” and “Table_accounts” for each PowerQuery Table that you create.
  11. There is a MERGE option in PowerQuery ribbon, that will first ask you to establish a unique relationship between these two Powerquery Tables. In my case I selected “Table_Contacts.Parentcustomerid” and “Table_Accounts.Account.
  12. Result is a a new Column with an embedded Table in it. Well this is good that it is presenting as such, however I want the embedded Table column to be expanded into its own fields aslongside with fields from Contacts.
  13. Once you complete the above steps, click on “Done” from PowerQuery.
  14. Bang, you have a report of all Contacts with account data for all 33K contacts.

Let us review what we have accomplished. we have created a report in Excel 2013 with master detail relationship for more than 5K or 10K record, which are a limitation in CRM Online. PowerQuery is awesome tool and thanks the SQL server BI teams for building this tool.

NOTE: all of this possible only when you CRM for Outlook client configured to talk to CRM Online or CRM On-prem instance.

Hope this helps.

CRM 2011: Increase export to excel limit (10000) – Different Approach

Installation Type: CRM Online an/or On-Premise.

Here is the solution if you have CRM Online. I know this works. Using Excel 2013(will not work on 2010).
1. Export a very small subset (say 100 records) of data using Web Client(Browser) using Export to Excel functionality.
2. In the Export dialog, choose “Dynamics Worksheet”. When finished, CRM will spit out an xxx.xml file.
3. Save to Desktop and open it within Excel 2013. Under the Data ribbon tab, select “Refresh from CRM”.
4. Once this is done and you see the exact same set as Export criteria, you can save this file as “.xlsx” file.
5. Create a oData query using open source tools to retrieve say contacts modified in last 30 days.
6. Under Data tab in Excel, “Choose a oData feed from ‘From Other Sources’ under ‘Get External Data’ group”.
7. Paste the oData query into the Browse text box and click NEXT, NEXT, OK.
8. Viola. It will be slow if you have lots of data and you might get kicked out during the import. Be careful with this approach since there is no way to cancel the download. If you have large sets of data, you might want to write oData query that will break it down into smaller pieces.

What I like with approach is it is not a change at Organization level. We can provide this as a feature for Power Users who is more interested in analytics using Power BI (PowerPivot/PowerView/PowerQuery). This approach will bypass the 10000 record limit with Export to Excel from CRM. Just now exported almost 33K contacts record with 19 columns of data. Not sure where the limit is for this approach.

Enjoy!
Prabhakar

GeoCode Plugin using Google Maps or BING Maps

Overview:

In my previous post, I talked about building a Custom Workflow Activity that works for CRM 2011 Online and CRM 2011 On-Premise. Now decided to build a geocoding plugin which will do the same, except it is like Turbo the snail. Designed it in such a way, you can make this to work for any Entity, OOB or Custom and for any number. Any secrets, naaaahhhhh. Just plain old CRM. That’s what I like about CRM. It is LEGO blocks. No end to creativity. Created a config entity to hold all config data, be it Google Maps or Bing Maps. Ok, enough of the blah, blah, blah. Just get to the meat will ya. I felt there might be a need to goecoding to be synchronous, rather than a workflow. Well with 2013, that will change with Realtime Workflows.

Solution:

In this post I will deal with deploying, customizing and seeing the results of the plugin executing. Will post the source code online at Codeplex(https://crmmaps.codeplex.com/) when I make myself a bit free. Will provide a managed solution with just the plugin and config entity.

Step 1: Import Maps Solution.

Once you import the solution, you should see a new Group on sitemap under settings called E31 Maps. This will give you access to Geocode Config entity.

Step 2: Geocode Config Entity entries

In this step, I’m assuming you are adding a record to geocode an address in Account entity.

Entity Name = account(this is the logical name, not schema name of the entity).

Keeping with the above guideline, please fill in the names of the fields that are the key players in the geocoding process.

Inputs:

  • Entity Name
  • Bing Maps Key ( can be empty when using google maps url)
  • Bing Maps Url, or Google Maps Url
  • Street Field(name of the field in contact entity which hold the Street Address value)
  • City Field (name of the field in contact entity which hold the City value )
  • State Field (name of the field in contact entity that holds the State or Province value)
  • Postal Code Field (name of the field in the contact entity that holds ZIP code value)
  • Country Field

Outputs:

  • Latitude Field (name of the field that will hold geocoded Latitude value in contact entity)
  • Longitude Field (name of the field that will hold the geocoded Longitude value in contact entity).

Example of screenshot for Account entity:

GeocodeConfig_Account_Bing

Geocode Config record values for geocoding Account entity using BING Maps

Step 3a: Plugin Registration – New Step for CREATE

Once the Plugin is registered as part of the installation of the managed solution, fire up the Plugin registration tool supplied with 2011 SDK. Create a new STEP for this assembly as shown below.

Bing_Create_Account

STEP Configuration settings for Account CREATE Message

Step 3b: Plugin Registration – New Step for UPDATE

Want to finish the configuration for the UPDATE message so that you can go ahead and fire up the plugins by creating accounts or updating accounts.

  1. Create a new STEP for the assembly for the UPDATE message,
  2. In the Step Configuration section, please select the Filtering attributes to be Input attributes as mentioned in the previous section. The 5 attributes are a) Street, b) City, c) State, d) Zip and e) Country.
  3. Fill in other parts of the Step configuration screen as shown below.

    Bing_Update_Account

    STEP Configuration settings for Account UPDATE Message

  4. Create a PreImage for the UPDATE message step. It is important that I know the records previous address information before the change occurred. Set up the PreImage for these 5 fields. Mainly the ones that you need to compare with the changed values. These fields are picked from the entity for which you registered UPDATE step for. In our case we are dealing with Account. Select these 5 fields only for the PreImage address1_city, address1_country, address1_stateorprovince, address1_line1, address1_postalcode
    Bing_Update_Account_PreImage

    PreImage Setting for Account UPDATE step.

    There you go. You are done. Way the geocoding works is pretty simple. For a Create message, no need for any explanation. I pass in values that are available to the BING Maps geocode api. Bing maps returns a result and result quality(next release). For the Update operation, it is little bit tricky. CRM will send only the fields that changes to the event. In the case for fields that have not changed I get the value from PreImage. then I do the usual processing in passing all values that are parts of the address to the geocode API. Viola. I tested with CRM Online and works. Now you need to tell me if there are other features that must be added to this and if it is performing well.

NOTE: It is up to you as a developer to register the appropriate STEPS for the respective Geocode API, if it is BING or GOOGLE. This article is primarily is focused on BING Maps API. I’m working on a release of the plugin that uses Google Maps API. Both plugins will make use of Geocode Config entity to decide where the address fields are in the entity record in question.

As always comments are welcome. enjoy!

Download Solution : https://crmmaps.codeplex.com/

Prabhakar

Launch a Custom Activity form from Ribbon

Launch a Custom Activity form from Ribbon

Well, I had an interesting challenge at my current work and one of the problems that I have to solve is to prefill the Regarding field in an activity.

Problem:

Not happy with the current operation of the Lead Qualify ribbon button. During Qualification, it allows users to create NEW records only. In some scenarios, it is necessary to select existing records, CONTACT, ACCOUNT and/or OPPORTUNITY. In addition to associating the Lead with existing records, I have to mark the Lead as qualified.

NOTE: I did not mess with the existing Qualify ribbon button.

Solution1:

At this point in time, there are only two ways to present custom UI to the end-user. a) HTML Web Resource (aka HTML + jQuery + AJAX),  b) Custom Activity Entity (minus left nav and ribbon cleaned out) and c) using Dialog(somewhat limiting). I choose to take path b) because I needed the flexibility to work with any entity. Dialog restricts the primary entity and hence all lookups and associations.

Challenges:

  1. Launch the Custom Activity default form from Ribbon button.
    1. If you have UR8 or later installed, you can make use of the handy function called Xrm.Utility.openEntityForm.
  2. Open new activity form with ‘Regarding’ field prefilled.
    1. This was a challenge since SDK still says that you cannot set regarding lookup. Below code is what I used to set a Regarding object. In my case it was Lead.
    2. function LaunchCustomActivityForm()
      {
      
          debugger;
      
          var leadid = "698DAC01-B310-E311-8F98-B4B52F67D6E6";
          var leadname = "Debra Garcia (sample)";
      
          var acctid = "538CAC01-B310-E311-8F98-B4B52F67D6E6";
          var acctname = "A. Datum Corporation";
      
          var contactid = "2F8DAC01-B310-E311-8F98-B4B52F67D6E6";
          var contactname = "Maria Campbell";
      
          var params = {};
          params["pId"] = leadid ;
          params["pType"] =  4; //is the ObjectTypeCode
          params["pName"] = leadname ;
      
          //params["partyid"] = leadid ;
          //params["partytype"] =  4;
          //params["partyname"] = leadname ;
      
          // Open the window.
          Xrm.Utility.openEntityForm("new_customact1", null, params);
      }
    3. Lauch the Custom Activity from Ribbon, you have to obtain the record Id and name to pass on to the above function. There is quite a bit of help around the web to figure this one out.
    4. Last step is to write a plugin that sets “Originating Lead” field to the Lead record. This can be post-op on “new_custact1” entity for either a create or update. Also set the Lead as Qualified during the Create
NOTE: Did not spend enough time to see why params with words “party…” is also needed. However just setting the pId, pName and pType are sufficient for the Regarding Lookup to identify the record and record type correctly.

Another bonus with this working this way is the regarding lookup correctly filters based on the record type. Also if you pass a ViewGuid, you can set the default view of this lookup. Oh well the options are endless. Even with the Polaris Flow UI, you can select an existing Contact or Account. You still cannot select an existing opportunity.

Hope this helps. Comments are always welcome.

Thanks,

Prabhakar