Thursday 16 July 2020

Finally we have Column Comparison in FetchXml, SDK and OData!



Microsoft has made an announcement and finally we have Column Comparison available using FetchXml, SDK and OData.

Now we will be able to perform column comparisons for the Common Data Service. Column comparisons will also work in the Power Apps expression language with CDS version 9.1.0000.19562 or later.

We can perform a column comparison for the following condition operators using FetchXML, Web API, or the SDK API:

  • Equal
  • NotEqual
  • GreaterThan
  • GreaterEqual
  • LessThan
  • LessEqual

Columns comparison using FetchXML:

<fetch>
  <entity name='contact' >
    <attribute name='firstname' />
    <filter>
      <condition attribute='firstname' operator='eq' valueof='lastname'/>
    </filter>
  </entity>
</fetch>

Columns comparison using Web Api:

https://<environment-root>/contacts?$select=firstname&$filter=firstname eq lastname

Columns comparison using SDK Api & Organization Service:

public ConditionExpression
(
  string attributeName,
  ConditionOperator conditionOperator,
  bool compareColumns,
  object value
)

public ConditionExpression
(
  string attributeName,
  ConditionOperator conditionOperator,
  bool compareColumns,
  object[] values
)




Premjit Singh
Attendite Ltd.
Twitter

Monday 2 July 2018

Microsoft Flow: Sql Server Table Names is not Visible in Flow




Recently came across a limitation/issue while creating a flow to export some data from Azure SQL Server. Flow does not show table in the list if it does not have a Primary Key, so we made a DateTime field Primary Key (bad practice anyway). But it was not showing in list of list of tables.

Then came across SQL Connector Documentation and found it's limitations there. Second point under limitations says that table must have an IDENTITY column.

Limitations

The triggers do have the following limitations:
  • It does not work for on-premises SQL Server
  • Table must have an IDENTITY column for the new row trigger
  • Table must have a ROWVERSION (a.k.a. TIMESTAMP) column for the modified row trigger


Then we changed our SQL table as below and it started showing up in the list of tables in flow:

CREATE TABLE [dbo].[abc_TriggerToRunExportUsingFlow](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[TriggerOn] [datetime] NOT NULL,
[Description] [nchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[abc_TriggerToRunExportUsingFlow] ADD  CONSTRAINT [DF_abc_TriggerToRunExportUsingFlow_TriggerOn]  DEFAULT (getdate()) FOR [TriggerOn]
GO
Premjit Singh
Attendite Ltd.
Twitter

Monday 25 June 2018

Resolve Error "Unable to Login to Dynamics CRM" after v9 update




Recently we have updated our Dynamics 365 to v9 and we are updating our applications to the latest version of SDK.

As per Microsoft Guideline, approach is to update apps to .NET Framework 4.6.2 and update CRM SDK to latest v9.0 assemblies. But application was still throwing same error message ("Unable to Login to Dynamics CRM").

One of my colleague pointed to a David Branscome's Post about moving away from TLS 1.0 and 1.1 to 1.2 and it reminded me about the post I read few days back.

Because our .NET assemblies were already updated so version 4.6.2 and it should be handling the TLS Protocol issue itself. But our application was still throwing the same error so we decided to try invoke TLS1.2 manually in our application before CrmServiceClient is instantiated. Following line was added to the code:

ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

But it was still throwing same error and then we started looking at assembly references and app.config. In config we found that targetFramework was pointing to 4.5.2 and it worked when we changed to 4.6.2 (as shown below). Once it is updated connection will was created without any issue.


<httpRuntime targetFramework="4.6.2" />



Premjit Singh
Attendite Ltd.
Twitter

Friday 28 April 2017

How to Retrieve record(s) from Dynamics CRM using FetchXml and Web Api

Following JavaScript shows how to retrieve record(s) from Dynamics CRM using FetchXml and Web Api.

function RetrieveUsingFetch() {
var date = new Date();
var today = date.DateFormat("yyyy-MM-dd"); \\ Click here to see function details.
var marketCondition = "";
var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>" +
"<entity name='contractdetail'>" +
"<attribute name='contractid' />" +
"<attribute name='title' />" +
"<attribute name='contractdetailid' />" +
"<filter type='and'>" +
"<condition attribute='statuscode' operator='in'>" +
"<value>2</value>" +
"<value>1</value>" +
"</condition>" +
"<condition attribute='activeon' operator='on-or-before' value='" + today + "' />" +
"<condition attribute='expireson' operator='on-or-after' value='" + today + "' />" +
"<condition attribute='customerid' operator='eq' value='" + Xrm.Page.getAttribute("dbc_customerid").getValue()[0].id + "' />" +
"</filter>" +
"<link-entity name='contract' from='contractid' to='contractid' alias='aa'>" +
" <attribute name='title' />" +
"<filter type='and'>" +
"<condition attribute='statuscode' operator='eq' value='3' />" +
"</filter>" +
"</link-entity>" +
"</entity>" +
"</fetch>";

var uri = "/contractdetails?fetchXml=" + encodeURIComponent(fetchXml);
var clientUrl = Xrm.Page.context.getClientUrl();
var webApiPath = "/api/data/v8.1";
uri = clientUrl + webApiPath + uri;

var request = new XMLHttpRequest();
request.open("GET", encodeURI(uri), false);
request.setRequestHeader("Accept", "application/json");
request.setRequestHeader("Content-Type", "application/json; charset=utf-8");
request.setRequestHeader("OData-MaxVersion", "4.0");
request.setRequestHeader("OData-Version", "4.0");
request.setRequestHeader("Prefer", "odata.include-annotations=\"OData.Community.Display.V1.FormattedValue\"");
request.setRequestHeader("Prefer", "odata.maxpagesize=10");
request.onreadystatechange = function () {
if (request.readyState === 4 /* complete */) {

if (request.status === 200) {
request.onreadystatechange = null; 
var data = JSON.parse(request.response);
if (data.value.length === 1 || data.value.length > 1) {
alert(data.value[0].contractdetailid);
}
} else {
var error = JSON.parse(request.response).error;
alert(error.message);
}
}
};
request.send();
}

Note: Make sure that you have right version of WebApi in your code.

Happy Coding

P. S. Hayer
(ਪ੍ਰੇਮਜੀਤ ਸਿੰਘ ਹੇਰ)

Please check my other (non-CRM) blog here: Programming Blogs

Thursday 14 April 2016

How to retrieve SLA Pause States (On Hold Case Status) in C# code


Following code shows how to retrieve SLA Pause States from Dynamics CRM using C#. I needed code a functionality to calculate case elapsed minutes and I thought it is worth finding a way to load paused statuses from system rather than hard-coding them.


        private static string[] GetSlaPauseStates(IOrganizationService service)
        {
            QueryExpression qe = new QueryExpression("organization")
            {
ColumnSet = new ColumnSet(new string[] { "organizationid", "slapausestates" })
            };

            EntityCollection orgs = service.RetrieveMultiple(qe);

            if (orgs != null && orgs.Entities.Count > 0)
            {
                var org = orgs[0];
                var slaPauseStates = orgs[0]["slapausestates"];
                string[] states = slaPauseStates.ToString().Split(';');
                return states;
            }

            return null;
        }

In Plugin or Custom Workflow we can retrieve the OrganizationId from contact and then we can directly retrieve the Organization by Id. Following is the code to retrieve organizationId.


    IWorkflowContext context = executionContext.GetExtension<IWorkflowContext>();
    IOrganizationServiceFactory serviceFactory =      executionContext.GetExtension<IOrganizationServiceFactory>();
    IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

    var organizationId = context.OrganizationId;

    // Get Organization Business Closure Calendar Id
    var organization = service.Retrieve("organization", context.OrganizationId, new ColumnSet("slapausestates"));
    var slaPauseStates = organization["slapausestates"];

    string[] states = slaPauseStates.ToString().Split(';');


Happy Coding

P. S. Hayer
(ਪ੍ਰੇਮਜੀਤ ਸਿੰਘ ਹੇਰ)
Premjit Singh
Attendite Ltd.
Twitter

Wednesday 3 February 2016

How to reset Auto-numbering in Dynamics CRM Online


In past I have shown how to reset auto-numbering un-supported way. Today I am going to discuss how we can reset auto-numbering by Microsoft supported way.

Number greyed-out so it can't be changed through UI. But we can write a small console application to update it using SDK.



Following code can be used to update the number. In this example I am updating CurrentKbNumber but we can also update CurrentCaseNumber, CurrentContractNumber, CurrentQuoteNumber, CurrentOrderNumber and CurrentInvoiceNumber. However we don't need to worry about prefix and they can be updated through UI.

    // QueryExpression to Retrieve Organization
    QueryExpression qe = new QueryExpression("organization")
    {
        ColumnSet = new ColumnSet(new string[] {"organizationid", "name", "currentkbnumber"})
    };
    EntityCollection orgs = service.RetrieveMultiple(qe);

    if (orgs != null && orgs.Entities.Count > 0)
    {
        var org = orgs[0];
        var organizationId = (Guid)org["organizationid"];

        // Creating a new Object to update. Set the CurrentKbNumber to your desired number
        Organization organizationToUpdate = new Organization { CurrentKbNumber = 100000, Id = organizationId};
        service.Update(organizationToUpdate);
    }    

Once updated, please go to Settings > Administration > Auto-numbering to verify.

Happy Coding

P. S. Hayer

Premjit Singh
Attendite Ltd.
Twitter

Friday 30 October 2015

How to increase Paging Limit (Records Per Page) in Dynamics CRM 2015 Online using C#


In Dynamics CRM default paging limit is set to 50 records per page. Sometime we need to increase Paging Limit (per page) so that I can Activate/Deactivate/Edit/View more than 50 records per page. In CRM On-Premises we have an unsupported option to set it to whatever value we want by running a SQL update query on UserSettings. But with CRM 2015 Online we don’t have access to database so everything needs to be supported. 

From UI we can only set Paging Limit up to 250. ‘PagingLimit’ in UserSettings entity is valid for update so we can write some code to update Paging Limit. However even with code maximum Paging Limit we can set is 500 but it is still double than the maximum we can set through UI.

Complete Solution to update Record per page can be downloaded from CodePlex.


Following is the code to update.

using System;
using System.Linq;
using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;

namespace UpdatePagingLimit
{
    internal class Program
    {
        private static OrganizationServiceProxy _serviceProxy;

        private static void Main(string[] args)
        {
            using (var context = GetOrganizationServiceContext())
            {
                string firstname = "** user’s first name **";

                var userId = (from u in context.CreateQuery("systemuser")
                    where u.GetAttributeValue<string>("firstname") == firstname
                    select u.GetAttributeValue<Guid>("systemuserid")).FirstOrDefault();

                if (userId != Guid.Empty)
                {
                    var usersettingsId = (from s in context.CreateQuery("usersettings")
                        where s.GetAttributeValue<Guid>("systemuserid") == userId
                        select s.Id).FirstOrDefault();

                    if (usersettingsId != Guid.Empty)
                    {
                        var userSettingsToUpdate = new Entity("usersettings") {Id = usersettingsId};

   // 500 is the Maximum you can set
                        userSettingsToUpdate["paginglimit"] = 500;

                        var service = GetOrganizationService();
                        service.Update(userSettingsToUpdate);
                    }
                }
                else
                {
                    Console.WriteLine("Unable to find the System User");
                }
            }
            Console.ReadLine();
        }

        public static OrganizationServiceProxy GetOrganizationServiceProxy()
        {
            var organizationUri = new Uri("https://XXXXXXXX.crm4.dynamics.com/XRMServices/2011/Organization.svc");

            IServiceManagement<IOrganizationService> orgServiceManagement =
                ServiceConfigurationFactory.CreateManagement<IOrganizationService>(organizationUri);


            AuthenticationProviderType endpointType = orgServiceManagement.AuthenticationType;

            AuthenticationCredentials authCredentials = new AuthenticationCredentials();
            authCredentials.ClientCredentials.UserName.UserName = "joe.blogs@example.onmicrosoft.com";
            authCredentials.ClientCredentials.UserName.Password = "Password";
            if (endpointType == AuthenticationProviderType.OnlineFederation)
            {
                IdentityProvider provider =
                    orgServiceManagement.GetIdentityProvider(authCredentials.ClientCredentials.UserName.UserName);
                if (provider != null && provider.IdentityProviderType == IdentityProviderType.LiveId)
                {
                    authCredentials.SupportingCredentials = new AuthenticationCredentials
                    {
                        ClientCredentials = Microsoft.Crm.Services.Utility.DeviceIdManager.LoadOrRegisterDevice()
                    };
                }
            }

            _serviceProxy =
                GetProxy<IOrganizationService, OrganizationServiceProxy>(orgServiceManagement, authCredentials);

            return _serviceProxy;
        }

        public static OrganizationServiceContext GetOrganizationServiceContext()
        {
            var proxy = GetOrganizationServiceProxy();
            var context = new OrganizationServiceContext(proxy);
            return context;
        }

        public static IOrganizationService GetOrganizationService()
        {
            var service = (IOrganizationService) GetOrganizationServiceProxy();
            return service;
        }

        private static TProxy GetProxy<TService, TProxy>(
            IServiceManagement<TService> serviceManagement,
            AuthenticationCredentials authCredentials)
            where TService : class
            where TProxy : ServiceProxy<TService>
        {
            Type classType = typeof (TProxy);

            if (serviceManagement.AuthenticationType !=
                AuthenticationProviderType.ActiveDirectory)
            {
                AuthenticationCredentials tokenCredentials =
                    serviceManagement.Authenticate(authCredentials);

                // Obtain organization service proxy for Federated, LiveId and OnlineFederated environments.
                // Instantiate a new class of type using the 2 parameter constructor of type IServiceManagement and SecurityTokenResponse.
                return (TProxy) classType
                    .GetConstructor(new Type[] {typeof (IServiceManagement<TService>), typeof (SecurityTokenResponse)})
                    .Invoke(new object[] {serviceManagement, tokenCredentials.SecurityTokenResponse});
            }

            // Obtain discovery/organization service proxy for ActiveDirectory environment.
            // Instantiate a new class of type using the 2 parameter constructor of type IServiceManagement and ClientCredentials.
            return (TProxy) classType
                .GetConstructor(new Type[] {typeof (IServiceManagement<TService>), typeof (ClientCredentials)})
                .Invoke(new object[] {serviceManagement, authCredentials.ClientCredentials});
        }
    }
}

Now if you build the project you will get an error complaining about missing reference for Microsoft.Xrm.Services. To get rid of this error download CRM 2015 SDK. Go to \SDK\SampleCode\CS\HelperCode and add DeviceIdManager.cs to you. Now you will have to add System.Security assembly reference.

Your code is ready to build and run. Make sure that you have updated the credentials, Organization service Uri and user’s first name in code. I have set them to dummy data.

Happy Coding
P. S. Hayer