View-FetchXML

You are a Dynamics 365/CDS developer. How you build your FetchXML string in JavaScript/C# code?

Are you use the function: Download Fetch XML in the Advanced Find window?

Advanced Find

Wow, look like you have a lot of tasks 😰 after you Download Fetch XML

  • Open the xml file with the text editor
  • Find " and Replace with '
  • Edit, join to make a correct string, also with a dynamic parameter
  • …..

Instead click the button Download Fetch XML in the Advanced Find window, you can click the button View FetchXML near it by install the solution View-FetchXML

Advanced Find With View-FetchXMl

Ok, What is the difference ? Check the demo

Demo View-FetchXML

The solution View-FetchXML help you

  • Easy copy/paste with 3 types of string: FetchXML, JavaScript and C#
  • Easy edit the dynamic values with fetchData (object JavaScript) or (Anonymous Types C#)

Download

ViewFetchXML_1_3_managed.zip

Dynamics 365 Online S2S Authentication - Full Explain

Server-to-Server (S2S) authentication (only support Dynamics 365 online) was great, it use ClientId/SecretKey to authentication (instead use UserName/Password) and it use without Dynamics 365 license.

But everybody have problem when use CllientId/SecretKey, the main problem is HTTP Error 401 - Unauthorized: Access is denied

I headache this problem more than 3 days, and now, I succeed authentication with ClientId/SecretKey.

This post I will fully explain you how I can achieve it.

Important noted: do the correct order step by step here

1. Register App with Azure Active Directory

After you register App, save these value here to use it later

  • Application ID
  • Url redirect
  • SecretKey

Application ID

Save Application ID

Redirect URI

Save Redirect URI

SecretKey

Save SecretKey. Rember the SecretKey only show once.

When register App use Application Type = Native and Assign Permission to Dynamics CRM Online

2. Create new Office365 user

We call this user as ApplicationUser

  • Assign CRM Online license
  • Role: Dynamics 365 Service Administrator

ApplicationUser

Create new Office 365 user with Dynamics 365 license and role

3. Create a custom security role and assgin to ApplicationUser

Open Dynamics 365 by CRM Administrator account

  • Clone a System Administrator security role to new ApplicationUserRole
  • Assign ApplicationUserRole to ApplicationUser user

ApplicationUserRole

Clone System Administrator role to ApplicationUserRole

Assign ApplicationUserRole

Assign ApplicationUserRole to ApplicationUser

  • Open Application User form for this ApplicationUser

    • Change form from User to Application User (if not change)
    • Update field Application ID from step 1.a (Application ID)
    • Save and go back to Enabled Users

    Update Application ID

    Update Application ID for ApplicationUser

  • Change view to Application Users and check field Application ID URI for ApplicationUser empty

Application ID URI Empty

Make sure Application ID URI of ApplicationUser is emtpy

4. Remove Dyanmics 365 license

Back to 365 admin and remove Dynamics CRM license from ApplicationUser you already do in step 2 Remove License

S2S Authentication no need a license, if you assign license to ApplicationUser it not work.

5. Manually build url and use it once

The syntax url here

https://login.microsoftonline.com/{talent-id}/oauth2/authorize?client_id={application-id}&response_type=code&redirect_uri={url-redirect}&response_mode=query&resource={crm-url}&state={new-guid}

  • talent-id: your azure active directory id. Azure portal -> Azure Active Directory -> Properties -> Directory ID talent-id
  • application-id: step 1.a
  • url-redirect: step 1.b
  • crm-ur: your full Dynamics 365 url, E.g.: https://abcd.crm.dynamics.com
  • new-guid: generator a new GUID

My final manually url here:

https://login.microsoftonline.com/12b5c856-99c8-4268-b99f-b5bfd02ae0f3/oauth2/authorize?client_id=2015d711-986e-4728-87d5-228994e190ba&response_type=code&redirect_uri=http://myapplicationusers.com&response_mode=query&resource=https://abcd.crm.dynamics.com&state=5cebc804-b7d6-4e26-b850-ed45708f37fa

6. Grant ApplicationUser to App

  • Use the manually url build on step 5. Open IE private window and paste the url to IE address bar
  • Process first time login (and only do it once) with the ApplicationUser you create in step 2, it redirect to url-redirect. Leave it and close your IE browser.

Grant permission to ApplicationUser

First and only once login to grant permission to ApplicationUser

7. Check Application ID URI

Goback to Application Users view and check the Application ID URI (step 3.e.1) now have value same value with Applcation ID

The key I found here, if this field NULL, 401 return.

ApplicationUser with Application ID URI

8. Build console application

  • Create a console application
  • Use code below and now you can see WhoAmI

Note: you can use authenticationResult.AccessToken for WebAPI call in the header request

class Program
{
    static private Uri GetServiceUrl(string organizationUrl)
    {
        return new Uri(organizationUrl + @"/xrmservices/2011/organization.svc/web?SdkClientVersion=8.2");
    }
 
    static void Main(string[] args)
    {
        var organizationUrl = "https://phuoc2017mar26.crm.dynamics.com";
        var aadInstance = "https://login.microsoftonline.com/";
        var tenantID = "12b5c856-99c8-4268-b99f-b5bfd02ae0f3";
        var clientId = "2015d711-986e-4728-87d5-228994e190ba";
        var appKey = "your-private-secret-key-here";
        var clientcred = new ClientCredential(clientId, appKey);
        var authenticationContext = new AuthenticationContext(aadInstance + tenantID);
        var authenticationResult = authenticationContext.AcquireToken(organizationUrl, clientcred);
        var requestedToken = authenticationResult.AccessToken;
        using (var sdkService = new OrganizationWebProxyClient(GetServiceUrl(organizationUrl), false))
        {
            sdkService.HeaderToken = requestedToken;
            var request = new OrganizationRequest()
            {
                RequestName = "WhoAmI"
            };
            var response = sdkService.Execute(new WhoAmIRequest()) as WhoAmIResponse;
            Console.WriteLine(response.UserId);
            Console.ReadKey();
        }
    }
}

Run the code and I get the result here

Result

I get UserID GUID of ApplicationUser we already grant permission to App

GREAT

Some mistakes

  • Don’t assign Application ID to user.
  • Don’t create a custom security role and assign to application user, everybody always use default System Administrator role and assign to application user.
  • Build correct url above but login with another user that have assign Application ID, or login with user don’t assign application key
  • Forgot remove license from user before use url above.
  • Manually url not match with url-redirect when register application in Azure Active Directory
  • Don’t do correct order I list here

Finally your target

  • Application User should in Application Users view
  • Application User should have 3 field (not blank)
    • Application ID
    • Application ID URI
    • Azure AD Object ID
  • Application User should remove Dynamics 365 license
  • Application User must have a custom security role

Hope this post working for you.

Please, don't hard code

When building hyperlinks to CRM in SSRS reports, please don’t hard code

  • Wrong: hard coding your CRM URL. Do not set your URL to “https://crmserver/….” If you do this use the hyperlink will only work in one environment and will have to be rewritten to work in another environment.
  • Right: Use the CRM_URL parameter in your report. This makes your links will work in all environments, even when offline.
    Parameters!CRM_URL
  • Wrong: Using OTC (ObjectTypeCode) in your hyperlink expression. ObjectTypeCode never changes for System entities, but for custom entity ObjectTypeCode will change when customization is imported into a new environment.
    ="OTC=1&ID={“ & Fields!accountid.Value.ToString() & ”}”
  • Right: Use the logical entity name instead of ObjectTypeCode.
    ="LogicalName=account&ID={“ & Fields!accountid.Value.ToString() & ”}”

Get Sql ConnectionString from CRM server code

Sometime, your business logic need access direct SQL Server, and you want to know the ConnectionString of CRM Database.

This code will help you get Sql ConnectionString from CRM Plugin/Custom Action/Custom Workflow (before that I saved the ConnectionString to the custom Options entity)

public void Execute(IServiceProvider serviceProvider)
{
    var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
    var platformContext = context.GetType().InvokeMember("PlatformContext", BindingFlags.GetProperty, null, context, null);
    var transaction = (SqlTransaction)platformContext.GetType().InvokeMember("SqlTransaction", BindingFlags.GetProperty, null, platformContext, null);
    throw new InvalidPluginExecutionException(transaction.Connection.ConnectionString);
}
  • Remember to add reference System.Data.dll to your project

Limitation

  • CRM On-Premises only
  • Plugin/Custom Action/Custom Workflow when register the option Isolation Mode should selected None
  • Access SQL code should out side the Plugin/Custom Action/Custom Workflow pipe line transaction. (E.g: use at Pre-Validation)

Actual project experience

  • Requirement: End user want change exchange rate at the end of month after a lot of records created/locked on this month with current temporary exchange rate.
  • Analysis: You cannot use the SDK code because a lot of records locked by CRM
  • Solution:
    • Create a custom action, get SQL ConnectionString, and then use the ConnectionString to update records
    • Create a custom ribbon button, then call custom action to update data.

Hide Next Stage, Set Active, Back Button in the Business Process Flows

I have a requirement in my current project that hide Next Stage, Set Active and Back button in the Business Process Flows

To do that, I used an UnSupported code code below

function hideBPFButton() {
    hideBPFButtons();
    window.addEventListener("resize", hideBPFButtons);
    Xrm.Page.data.process.addOnStageSelected(hideBPFButtons);
}

function hideBPFButtons() {
    var dom = (Xrm == undefined || Xrm.Internal == undefined || Xrm.Internal.isTurboForm() == undefined || Xrm.Internal.isTurboForm() == false) ? document : parent.document;
    $("#stageSetActiveActionContainer", dom).remove();
    $("#stageBackActionContainer", dom).remove();
    $("#stageNavigateActionContainer", dom).remove();
    setTimeout(function () { $("#processStagesContainer", dom).width(1894); }, 50);
}

In the form OnLoad, call function

hideBPFButton();

Before apply code

Before apply code

After apply code

After apply code

How to code: change stage, set active

  • Client side: check Xrm.Page.data.process and Xrm.Page.ui.process
  • Server side: reference 3 fields: processid, stageid and traversedpath: in the entity record

The following code query processid by Process Name in the entity workflow

<fetch distinct="false" mapping="logical" output-format="xml-platform" version="1.0">
  <entity name="workflow">
    <attribute name="workflowid">
    <filter type="and">
      <condition attribute="type" operator="eq" value="1">
      <condition attribute="category" operator="eq" value="4">
      <condition attribute="name" operator="eq" value="yyyyyy">
    </filter>
  </attribute></entity>
</fetch>

The following code query stageid by processid and Stage Name in entity processstage

<fetch distinct="false" mapping="logical" output-format="xml-platform" version="1.0">
  <entity name="processstage">
    <attribute name="processstageid">
    <filter type="and">
      <condition attribute="processid" operator="eq" value="xxxxxx">
      <condition attribute="stagename" operator="eq" value="yyyyyy">
    </filter>
  </attribute></entity>
</fetch>

Checked, Tested work with CRM2016, CRM2016 SP1, CRM2016 Online

UnSupported code, use it with your own risk.