Friday, 15 September 2017

{know-how} D365 CRM - Get optionset label from Stringmap using FetchXML - c#,JS, SSRS



Hello All,

Good to be back with another know how post. Lets go through a simple way to retrieve a MSCRM optionset label using fetchxml that can be used with C#/JS/SSRS.

Usual way to retrieve an optionset value in c# or JS is by using RetrieveAttributeRequest and refer this blog for the same.

RetrieveAttributeRequest - Option set label retrieval

This time, wanted to stick with old school SQL approach by using stringmap table, so replicated the same logic into fetchxml query and below is the simple fetchxml. This will be handy in SSRS report development to retrieve optionset details.

What is stringmap?
Stringmap is a table in crm which contains all the optionset options and its related details. Will write a quick article soon around stringmap and its fields 😉

Coming back to the topic, below is the fetchxml to get optionset label, hope the fields are self explanatory.

FetchXML

<fetch>
    <entity name="stringmap" >
        <attribute name="value" />
        <filter type="and" >
            <condition attribute="objecttypecodename" operator="eq" value="account" />
            <condition attribute="attributename" operator="eq" value="customertypecode" />
            <condition attribute="attributevalue" operator="eq" value="2" /> 
        </filter>
    </entity>
</fetch>

Result


<result>
    <value>Consultant</value>
</result>

Thursday, 7 September 2017

{know-how} MSCRM Actions - Newly created actions not available or missing in plugin registration tool



When we create a new action, sometimes the new action will not be visible in plugin registration tool, this could be due to metadata refresh. Try below options and it should be visible after this.
  1. Deactivate and activate action
  2. Refresh the plugin registration tool
  3. Close and Reopen the plugin registration tool - This option works most of the time

{know-how} MSCRM FetchXML SSRS - Improve performance with No-Lock - FetchXML Nolock



A quick tip around improving CRM data retrieval performance using fetchxml, this is applicable to both RetrieveMultiple  and SSRS performance.

What is no-lock?
Whenever we query CRM using fetchxml,a lock is issued against the table. when we use No-lock the process of issuing lock is skipped. This type of data retrieval is also called as dirty read because there is a chance that you might read the uncommitted data before rollback.

Advantage?

  • Improves SSRS report performance or RetrieveMultiple performance on high transaction oriented tables


Example:

<fetch mapping="logical" no-lock="true" >
    <entity name="account" />
</fetch> 

Sunday, 20 August 2017

{Know-how} D365 - SSRS Report Prefiltering fetchxml/SQL


Hello All,

FetchXML and SQL querying are some of the best possible ways to retrieve data from MSCRM for BI or Data manipulation.


Previous articles around fetchxml querying are as follows, now lets jump into fetchxml/sql prefiltering


What is prefiltering and why should I use it?

 As the name suggests, before execution of the report we restrict the scope to work based on relevant records. This is improves performance and also enables advanced find filtering before report execution.


How to enable prefiltering for FetchXML datasets?
  • Under entity node, add an attribute enableprefiltering attribute and set its value to true

 <fetch distinct="false" mapping="logical">
   <entity name="account" enableprefiltering="1">
      <attribute name="accountid" />
      <attribute name="name" />      
   </entity>
 </fetch>


How to enable pre-filtering for SQL datasets?
  • Add an alias with prefix CRMAF_ to the filtered view name
SELECT accountid, name  
FROM FilteredAccount AS CRMAF_FilteredAccount

{Know-how} D365 CRM Javascript - Optionset related methods - get/set/add/remove/retrieve all/remove all


Hello all,

Optionsets are one of the key data capturing controls, manipulating options are essential to achieve an efficient UX experience in all systems including our MSCRM. Below are the quick reference to D365 CRM optionset related javascript methods, which helps in achieving the same.

Here we are going to use "Status Reason"(statuscode) field as our optionset. Lets not wait anymore, Js snippets are as follows😉


Get the selected option - returns the label/text and value

Xrm.Page.getAttribute("statuscode").getSelectedOption()
{"value":1,"text":"Active"}



Get the selected option's value

Xrm.Page.getAttribute("statuscode").getValue()
1



Get the selected option's text/label

Xrm.Page.getAttribute("statuscode").getText()
Active



Get all the options for the optionset - Returns an array of options

Xrm.Page.getAttribute("statuscode").getOptions()
[{"value":1,"text":"Active"},{"value":100000000,"text":"Test1"},"value":100000001,"text":"Test2"}]



Set the optionset value(selected option) - Set to Test1

Xrm.Page.getAttribute("statuscode").setValue(100000000)



Remove an option from optionset - Remove Test1

window.frames[0].Xrm.Page.getControl("statuscode").removeOption(100000000)



Add an option to optionset - Add Test1 Again


Xrm.Page.getControl("statuscode").addOption({value:100000000,text:"Test1"})



Remove all options from an optionset

Xrm.Page.getControl("statuscode").clearOptions()


Hope this helps!

Thursday, 27 July 2017

{Know-how} Dynamics 365 - SSRS pass prefiltering report parameter and open report using URL - JavaScript


Reporting is an essential part in any system. In Dynamics CRM, we have wide variety of reporting options that are flexible enough to meet most of the requirements and one such scenario is explained here. At times we may need to open a report via URL inside an IFrame or in a new window based on the requirement and in our scenario it is new window.


Scenario:
On click of a ribbon button in an account record, open account prefiltered report in a new window.

Code Explanation:
To form a report URL we need following
  1. Organization URL - Organization URL  and in my case https://contoso.crm.dynamics.com
  2. Report Id- unique identifier of the report
  3. Action - It can be either run or filter and for our scenario it should be run
  4. Context - It should be records
  5. ETC - Entity type code and in our case its for account
  6. Record Id - unique identifier of the current record
Note: The parameters 4,5 and 6 are specific to prefiltering. Skip the same if you want to run a report without preregistering


JavaScript code snippet:
             var orgUrl = Xrm.Page.context.getClientUrl();  
             var action = "run";  
             var context = "records";  
             var reportId = "report id goes here";
             var recordid = Xrm.Page.data.entity.getId().replace("{", "").replace("}", "");  
             var etc = Xrm.Page.context.getQueryStringParameters().etc;  
             var url = orgUrl +  
               "/crmreports/viewer/viewer.aspx?action=" + encodeURIComponent(action) +  
               "&context=" + encodeURIComponent(context) +  
               "&id=%7b" + encodeURIComponent(reportId) + "%7d" +  
               "&records=%7b" + encodeURIComponent(recordid) + "%7d" +  
               "&recordstype=" + encodeURIComponent(etc);  
             var customParameters = encodeURIComponent(url);  
             Xrm.Utility.openWebResource("reportcontainer.html", customParameters)  

HTH! Please leave your valuable feedback in comments!

{Know-how} Add a ribbon button using Ribbon WorkBench

Hello,

Though its an age old topic, would like to write down step-by-step approach to add an ribbon button for reference.

Prerequisite:
CRM instance with ribbon workbench solution  or Xrmtoolbox



Scenario:
Add a ribbon button on account and on click of the ribbon button show the URL of the record.

Steps:
  1. Open ribbon workbench
  2. Select the solution that contains account entity. 
  3. Add a new js command. Using command we can define JavaScript function or an URL action called on click of ribbon button.
  4. Lets add a Javascript action and call our Alert function which is available in existing Js webresource
  5. Now add the ribbon button and fill in the necessary details. Also link the command to the button and publish the changes
  6. Once done navigate to account and click the button to view the alert
Code snippet to alert URL:
 function alertURL() {  
 var url= "{0}/main.aspx?etn={1}&id={2}&pagetype=entityrecord"  
  Xrm.Utility.alertDialog( url.replace("{0}",Xrm.Page.context.getClientUrl()).replace("{1}", Xrm.Page.data.entity.getEntityName()).replace("{2}", encodeURIComponent(Xrm.Page.data.entity.getId())));  
 }  


HTH! Please leave your feedback on comments section!

Contact Form

Name

Email *

Message *