MS Dynamics 365

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

noreply@blogger.com (HayerCRM) — Thu, 16 Jul 2020 08:34:00 +0000

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>

</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
)

Following link provide more details: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/column-comparison

Premjit Singh
Attendite Ltd.
Twitter

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

noreply@blogger.com (HayerCRM) — Mon, 02 Jul 2018 12:54:00 +0000

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

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

noreply@blogger.com (HayerCRM) — Mon, 25 Jun 2018 14:02:00 +0000

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.

Premjit Singh
Attendite Ltd.
Twitter

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

noreply@blogger.com (HayerCRM) — Fri, 28 Apr 2017 15:48:00 +0000

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

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

noreply@blogger.com (HayerCRM) — Thu, 14 Apr 2016 16:01:00 +0000

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

How to reset Auto-numbering in Dynamics CRM Online

noreply@blogger.com (HayerCRM) — Wed, 03 Feb 2016 13:20:00 +0000

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

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

noreply@blogger.com (HayerCRM) — Fri, 30 Oct 2015 09:13:00 +0000

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

CRM 2015 - How to change existing or add new theme

noreply@blogger.com (HayerCRM) — Fri, 22 May 2015 08:16:00 +0000

From long time customers were waiting the day Microsoft will allow them to change user interface of Dynamics CRM, so that they can have their own logo and colour theme applied to CRM to match their organization's brand. Finally the day has arrived. In spring 2015 release of CRM, Microsoft has launched a new feature to change theme. However we will not have full control like normal website, but still it is enough for customers to create a custom look and feel.

You can use your company logo instead of MS Dynamics logo and make changes to the default colors and visual elements provided in the un-customized CRM system. But themes are not included in solutions exported from CRM organization, but we can use export/import if needed, however there are only few options and it will take same amount of time to copy and paste from one CRM organization to another.

Personally I found it very useful because it allowed us to make all our CRM systems (Live, development & UAT) look different. We have selected different colours for all three different environment to eliminate the confusion.

To change the theme, follow the steps below:

1. Go to setings > customizations in new menu. (Which is far better then the previous one, as display sub-options under the main options. Which made the life a lot easier for desktop users.)

2. Under customizations, click on Themes.

3. Under themes you will see a default theme. If you have plans to revert back to default theme then don't edit it and create a new theme or clone the existing one. In this example I am going to create a new theme instead of cloning. Upload your own logo and set all the colors as you want. Though I am using some flashy colors, please forgive me if they are not meeting usability standards, I am selected those just to test this new feature.

4. Save and Publish the theme. you can preview it if you want. But as we have not deleted the original theme we have an option to go back to that. You can switch between existing themes by simply setting one as default.

Once you are happy with the new look, set it as default and enjoy.

P S Hayer

The E-mail Router service could not run the service main background thread. The E-mail Router service cannot continue and will now shut down.

noreply@blogger.com (HayerCRM) — Fri, 06 Mar 2015 16:13:00 +0000

Because of some issues 'Microsoft CRM Email Router' service stopped automatically. On manual start it stopped immediately saying SystemState.xml is corrupt.

I found the actual error message (following) in event log. But most useful message was prior to this error message saying "Error accesing SystemState.xml. Restore file with last backup."

#16192 - The E-mail Router service could not run the service main background thread. The E-mail Router service cannot continue and will now shut down. System.Configuration.ConfigurationErrorsException: The E-mail router service cannot access system state file Microsoft.Crm.Tools.EmailAgent.SystemState.xml. The file may be missing or may not be accessible. The E-mail Router service cannot continue and will now shut down. ---> System.Xml.XmlException: Root element is missing.
at System.Xml.XmlTextReaderImpl.ThrowWithoutLineInfo(String res)
at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
at System.Xml.XmlDocument.Load(XmlReader reader)
at System.Xml.XmlDocument.Load(String filename)
at Microsoft.Crm.Tools.Email.Providers.ConfigFileReader..ctor(String filePath, ServiceLogger serviceLogger)
at Microsoft.Crm.Tools.Email.Providers.SystemState.Initialize(ServiceLogger serviceLogger)
at Microsoft.Crm.Tools.Email.Agent.ServiceCore.InitializeSystemConfiguration()
--- End of inner exception stack trace ---
at Microsoft.Crm.Tools.Email.Agent.ServiceCore.InitializeSystemConfiguration()
at Microsoft.Crm.Tools.Email.Agent.ServiceCore.ExecuteService()

It gave me an idea that I need to find and restore the file from backup (if there is any). File was located at C:\Program Files\Microsoft CRM Email\Service\Microsoft.Crm.Tools.EmailAgent.SystemState.xml.

But Unfortunately there was not any backup. So I just renamed the file and restarted the service. It worked and started picking up pending emails gradually.

For future use I have taken backup of SystemState.xml and Configuration.bin as suggested by Microsoft

P.S. Hayer

How to reset Auto-numbering and where does CRM stores Prefix and Number

noreply@blogger.com (HayerCRM) — Fri, 06 Mar 2015 11:18:00 +0000

I Recently came across a question on a forum about how to update Auto-numbering number and where does CRM stores it in Database? I have looked in CRM tables and found that CRM stores it in [OrganizationBase] table. But as it is unsupported to update in SQL, I was not sure that if updating the number will work or not. I personally avoid doing unsupported things. But to find out the consequences I decided to give it a go in my Development environment.

Note: To update Auto-Numbering by Supported way, have a look at How to reset Auto-numbering in Dynamics CRM Online.

Following query returned the current auto number values.

SELECT [KbPrefix]

,[CurrentKbNumber]

,[CasePrefix]

,[CurrentCaseNumber]

,[ContractPrefix]

,[CurrentContractNumber]

,[QuotePrefix]

,[CurrentQuoteNumber]

,[OrderPrefix]

,[CurrentOrderNumber]

,[InvoicePrefix]

,[CurrentInvoiceNumber]

,[UniqueSpecifierLength]

FROM [dbo].[OrganizationBase]

Then executed the following query to reset number:

Update [dbo].[OrganizationBase]

set [CurrentKbNumber] = 10000;

Resetting number only can cause duplication if you already have records with that number. So to avoid it I have updated the Prefix as well.

CRM allows to update Prefix using UI but Number field is locked as shown below:

Got to Settings > Administration > Auto-numbering

Now on new Article creation, I can notice that Number has been set to 10000. Done some light testing and could not find any issues.

Note: It is not a Microsoft supported way of updating Auto-numbering, I would recommend to update Auto-Numbering Supported Way by updating the Organization using sdk.

P.S. Hayer

Dynamics CRM: Create ServiceAppointment

noreply@blogger.com (HayerCRM) — Wed, 01 Oct 2014 16:23:00 +0000

A service appointment represents an appointment for service. The schema name for this entity is ServiceAppointment. The service appointment entity represents a block of time on a calendar. This entity stores the availability blocks for resources and can be used to determine resource availability and scheduling.

A service appointment can be customized independently from other activities to accommodate the customer's business requirements for service delivery. A service appointment must have a corresponding service. It can be already bound with a set of resources specified by an activity party (ActivityParty) list.

Following code can be used to create a Service Activity.

// Create the ActivityParty. Resource can be a system user and user's Calendar will be booked for the duration.

var resource = new ActivityParty

{

PartyId = new EntityReference(SystemUser.EntityLogicalName, _userId)

};

// Create and instance of ServiceAppointment

var appointment = new ServiceAppointment

{

Subject = "Test Service Appointment",

ServiceId =

new EntityReference(Service.EntityLogicalName, new Guid("B39F04D6-7C93-E111-9675-005056B41D39")),

ScheduledStart = DateTime.Now.AddDays(5),

ScheduledEnd = DateTime.Now.AddDays(15),

Location = "On Site",

Description = "Test Appointment created for testing.",

Resources = new ActivityParty[] { resource },

IsAllDayEvent = true,

IsBilled = true

};

var request = new CreateRequest {Target = appointment};

var response = (CreateResponse) _service.Execute(request);

var serviceAppointmentId = response.id;

P. S. Hayer

Ref: Appointment entities

CRM 2011: Get list of audited attributes from AuditBase table

noreply@blogger.com (HayerCRM) — Thu, 28 Aug 2014 10:12:00 +0000

Recently, I was asked to get all the fields and their entities which were audited in a day so that we can decide if we really need to audit all of those fields and disable auditing where its not required.

As we know there is no Filtered view for audit entity and even advance find don't support Audit search, so it is bit difficult to get the list of fields audited. But Kelvin's blog put me on right path to write this query and get desired result.

-- Declaring Variable and Temp Tables

DECLARE @AttributeMask VARCHAR(MAX), @ObjectTypeCode INT, @EntityName VARCHAR(100), @LogDATETIME DATETIME

DECLARE @CurrentAttribute VARCHAR(MAX)

DECLARE @Audit Table(AttributeMask VARCHAR(MAX), ObjectTypeCode INT, EntityName VARCHAR(100), LogDATETIME DATETIME);

DECLARE @Result Table (AttributeId INT, ObjectTypeCode INT, EntityName VARCHAR(100), LogDATETIME DATETIME);

DECLARE @todaysdate DATETIME;

-- Set the date to bring all the fields Audited today

SET @todaysdate = CAST(GETDATE() AS DATE);

-- Get all todays records from AuditBase; You can remove where clause to get everything

INSERT INTO @Audit

SELECT a.AttributeMask, a.ObjectTypeCode, e.Name, a.CreatedOn FROM Audit AS a

INNER JOIN MetadataSchema.Entity e on a.ObjectTypeCode = e.ObjectTypeCode

WHERE CAST(a.CreatedOn AS DATE) = @todaysdate;

-- Using Cursor to go through each and every record in @Audit Table

DECLARE DataAuditCursor CURSOR FOR

SELECT * FROM @Audit

OPEN DataAuditCursor

FETCH NEXT FROM DataAuditCursor

INTO @AttributeMask, @ObjectTypeCode, @EntityName, @LogDATETIME

WHILE @@FETCH_STATUS = 0

BEGIN

-- Run while Attribute mask have comma(s) in it

WHILE CHARINDEX(',',@AttributeMask,0) <> 0

BEGIN

SELECT

@CurrentAttribute=RTRIM(LTRIM(SUBSTRING(@AttributeMask,1,CHARINDEX(',',@AttributeMask,0)-1))),

@AttributeMask=RTRIM(LTRIM(SUBSTRING(@AttributeMask,CHARINDEX(',',@AttributeMask,0)+1,LEN(@AttributeMask))))

IF LEN(@CurrentAttribute) > 0

INSERT INTO @Result Values(@CurrentAttribute, @ObjectTypeCode, @EntityName, @LogDATETIME)

END

INSERT INTO @Result VALUES((CASE WHEN ISNULL(@AttributeMask, '') = '' THEN NULL ELSE @AttributeMask END),

@ObjectTypeCode, @EntityName, @LogDATETIME)

FETCH NEXT FROM DataAuditCursor

INTO @AttributeMask, @ObjectTypeCode, @EntityName, @LogDATETIME

END

CLOSE DataAuditCursor;

DEALLOCATE DataAuditCursor;

-- Select Distinct to get all the Attributes and their entities

SELECT DISTINCT

r.EntityName

,(SELECT TOP 1 a.Name FROM MetadataSchema.Attribute a

INNER JOIN MetadataSchema.Entity e ON

a.EntityId = e.EntityId

and a.ColumnNumber = r.AttributeId

and e.ObjectTypeCode = r.ObjectTypeCode) 'AttributeName'

FROM @Result r;

Happy Coding

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

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

Dynamics CRM: How to get Business Closure Dates in Plugin

noreply@blogger.com (HayerCRM) — Mon, 23 Jun 2014 09:48:00 +0000

using System;
using System.Linq;
using HayerCrmPackage.Plugins.Entities;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;

namespace HayerCrmPackage.Plugins
{
    public class PreCaseCreate : Plugin
    {
        public PreCaseCreate()
            : base(typeof(PreCaseCreate))
        {
            base.RegisteredEvents.Add(new Tuple<int, string, string, Action<LocalPluginContext>>(20, "Create", "incident", new Action<LocalPluginContext>(ExecutePreCaseCreate)));            
        }

        protected void ExecutePreCaseCreate(LocalPluginContext localContext)
        {
            if (localContext == null)
            {
                throw new ArgumentNullException("localContext");                
            }

            var pluginContext = localContext.PluginExecutionContext;
            var service = localContext.OrganizationService;
            var context = new OrganizationServiceContext(service);

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

            var query = new QueryExpression("calendar")
            {
                ColumnSet = new ColumnSet(true),
                Criteria = new FilterExpression()
            };

            // Add condition to get Get Calander where CalanderId is equal to Organization's businessclosurecalendarid
            query.Criteria.AddCondition(new ConditionExpression("calendarid", ConditionOperator.Equal, organization["businessclosurecalendarid"].ToString()));

            // Get Calendar
            var businessClosureCalendar = service.RetrieveMultiple(query).Entities[0];

            // Get the Calendar rules
            IEnumerable<Entity> calanderRules = businessClosureCalendar != null ? businessClosureCalendar.GetAttributeValue<EntityCollection>("calendarrules").Entities : null;
        }
    }
}

To know how to add working days by calculating business closure dates and weekends, please check my blog Here.

Happy Coding

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

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

Dynamics CRM: How to Add working days by calculating business closure and weekends

noreply@blogger.com (HayerCRM) — Fri, 20 Jun 2014 11:27:00 +0000

Recently I received a requirement to set the Incident Auto Closure date to 'CreatedOn' + 7 working days. To add working days to 'CreatedOn' date, we need to exclude all the weekend and business closures. I came up with the following solution to achieve the required outcome.
Add the following Class to your project to calculate working day and business closures.

using System;
using System.Collections.Generic;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;

namespace HayerCrmPackage.Plugins
{
    public class AddWorkingDaysClass
    {
        /// 
        /// Add working days to a specific date by calculating Business Closure and weekends
        /// 
        public static DateTime AddWorkingAndBusinessClosureDays(DateTime specificDate, int workingDaysToAdd, 
            IOrganizationService service, IPluginExecutionContext context)
        {
            var currentDate = specificDate;
            var businessClosures = GetBusinessClosureCalendarRules(context, service);

            // Calculate the working days by taking out the weekends
            int completeWeeks = workingDaysToAdd / 5;
            DateTime date = specificDate.AddDays(completeWeeks * 7);
            workingDaysToAdd = workingDaysToAdd % 5;
            for (int i = 0; i < workingDaysToAdd; i++)
            {
                date = date.AddDays(1);
                while (!IsWorkingDayOfWeek(date))
                {
                    date = date.AddDays(1);
                }
            }

            // Calculate the working days by taking out Business Closures
            for (var i = currentDate; i <= date; i = i.AddDays(1))
            {
                if (i.DayOfWeek == DayOfWeek.Saturday || i.DayOfWeek == DayOfWeek.Sunday)
                    continue;

                foreach (var closure in businessClosures)
                {
                    var startDate = (DateTime)closure["effectiveintervalstart"];
                    var endDate = (DateTime)closure["effectiveintervalend"];
                    var range = new DateRange(startDate, endDate);

                    if (range.Includes(i))
                    {
                        date = date.AddDays(1);

                        if (date.DayOfWeek == DayOfWeek.Saturday)
                        {
                            date = date.AddDays(2);
                            i = i.AddDays(1);
                        }
                        else if (date.DayOfWeek == DayOfWeek.Sunday)
                        {
                            date = date.AddDays(1);
                        }
                    }
                }
            }
            return date;
        }

        /// 
        /// Get Business Closure Calendar Rules
        /// 
        private static IEnumerable<Entity> GetBusinessClosureCalendarRules(IPluginExecutionContext context,
            IOrganizationService service)
        {
            // Get Organization Business Closure Calendar Id
            var organization = service.Retrieve("organization", context.OrganizationId, new ColumnSet("businessclosurecalendarid"));

            var query = new QueryExpression("calendar")
            {
                ColumnSet = new ColumnSet(true),
                Criteria = new FilterExpression()
            };

            // Add condition to get Get Calander where CalanderId is equal to Organization's businessclosurecalendarid
            query.Criteria.AddCondition(new ConditionExpression("calendarid", ConditionOperator.Equal, organization["businessclosurecalendarid"].ToString()));

            // Get Calendar
            var businessClosureCalendar = service.RetrieveMultiple(query).Entities[0];

            // Return the Calendar rules
            return businessClosureCalendar != null ? businessClosureCalendar.GetAttributeValue<EntityCollection>("calendarrules").Entities : null;
        }

        private static bool IsWorkingDayOfWeek(DateTime date)
        {
            var day = date.DayOfWeek;
            return day != DayOfWeek.Saturday && day != DayOfWeek.Sunday;
        }

        public interface IRange<T>
        {
            T Start { get; }
            T End { get; }
            bool Includes(T value);
        }

        public class DateRange : IRange<DateTime>
        {
            public DateRange(DateTime start, DateTime end)
            {
                Start = start;
                End = end;
            }

            public DateTime Start { get; private set; }
            public DateTime End { get; private set; }

            public bool Includes(DateTime value)
            {
                return (Start <= value) && (value < End);
            }
        }
    }
}

To consume above class in your plugin, use the following code:

using System;
using System.Linq;
using HayerCrmPackage.Plugins.Entities;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;

namespace HayerCrmPackage.Plugins
{
    public class PreCaseCreate : Plugin
    {
        public PreCaseCreate()
            : base(typeof(PreCaseCreate))
        {
            base.RegisteredEvents.Add(new Tuple<int, string, string, Action<LocalPluginContext>>(20, "Create", "incident", new Action<LocalPluginContext>(ExecutePreCaseCreate)));            
        }

        protected void ExecutePreCaseCreate(LocalPluginContext localContext)
        {
            if (localContext == null)
            {
                throw new ArgumentNullException("localContext");                
            }

            var pluginContext = localContext.PluginExecutionContext;
            var service = localContext.OrganizationService;
            var context = new OrganizationServiceContext(service);

            var targetEntity = (pluginContext.InputParameters != null && pluginContext.InputParameters.Contains("Target"))
            ? (Entity)pluginContext.InputParameters["Target"]
            : null;

            // Converting Entity to Incident
            var incident = (targetEntity != null && targetEntity.LogicalName == Incident.EntityLogicalName) ? targetEntity.ToEntity<Incident>() : null;
            
            // If you want to update the number of working days to add, change the value of 2nd parameter below
            incident.new_AutoClosureDate = AddWorkingDaysClass.AddWorkingAndBusinessClosureDays(DateTime.Now, 7, service,
                        pluginContext);
        }
    }
}

Happy Coding

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

Ref:
1. Adding Working Week Days to a Specific Date
2. Check if Date Time is in Date Range

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

CRM 2013: Timer button is currently disabled

noreply@blogger.com (HayerCRM) — Thu, 19 Jun 2014 11:38:00 +0000

Service pack 1 of Dynamics CRM 2013 came up with an exciting feature of SLA Timer. Timers can be used to track service level agreement KPI to ensure customer commitments are met. Today I was trying to get my hands on it but after installing Service Pack 1, I discovered that Timer option was greyed out on form editor. It was saying:

"This button is currently disabled".

"You may not have selected the item that works with this feature. If you don not have permissions to use this feature, correct your system."

Solution:

Go to Settings > Administration > Install Product Updates

You will get the following screen.

Install the updated by clicking on 'Update' at the bottom of the screen.

it will install the updated and in the mean time you can watch the video on installation screen to know more about this brilliant feature. Once updated you will get the confirmation screen.

Open case form in form editor. You will see that 'Timer' button is enabled not and it is ready to use.

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

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

CRM 2011: How to Cancel/Terminate Workflow Programmatically using C#

noreply@blogger.com (HayerCRM) — Mon, 12 May 2014 12:53:00 +0000

I was assigned a task where workflow was running on status change and then it waits for few days to send an email message to contact. During the period of wait if status has been changed to something else then workflow should be cancelled.

So I have written a plugin with following code, which check for the status. if status is changed then cancel the workflow as following. There is an easy way to code by hard-coding the process Guid but I tried to write generic code so that I can reuse it if needed.

        /// 
        /// Cancel the running workflow if it is still not completed
        /// 
        /// Organization Service Context
        /// Regarding Entity Id
        /// Name of the workflow
        /// Logical name of the Entity on which worklow is running
        private static void CancelRunningWorkflow(OrganizationServiceContext context,string entityLogicalName, Guid regardingObjectId, string processName)
        {
           // Get Process by name where its running on given incident
           // and current status is waiting, in process or waiting for resources
            var processes = (from p in context.CreateQuery<AsyncOperation>()
                where p.PrimaryEntityType == entityLogicalName
                      && p.RegardingObjectId.Id == regardingObjectId
                      && p.Name == processName
                      &&
                      (p.StatusCode.Value == 10 ||       // Waiting
                       p.StatusCode.Value == 20 ||       // In Process
                       p.StatusCode.Value == 0)          // Waiting For Resources
                select new AsyncOperation {Id = p.Id, StateCode = p.StateCode, StatusCode = p.StatusCode})
                .ToList();

           // Go through each process and set the status to Cancelled
            foreach (var process in processes)
            {
                process["statecode"] = new OptionSetValue(3);
                process["statuscode"] = new OptionSetValue(32);       // Cancelled
                context.UpdateObject(process);
            }
            // Save the changes
            context.SaveChanges();
        }

You can call the method as below:

CancelRunningWorkflow(context, Incident.EntityLogicalName, incident.Id, "Workflow to Stop");

You can create linq Context as following:

OrganizationServiceContext context = new OrganizationServiceContext(_service);

Note: Make sure use have permissions to cancel the process.

Happy Coding

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

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

CRM 2013: How to disable of "Get CRM for Outlook" notification bar permanently

noreply@blogger.com (HayerCRM) — Fri, 09 May 2014 16:23:00 +0000

Recently few of our users complained about "Get CRM for Outlook" notification they receive every time they login to CRM. However it is not as frustrating as popups but some laptop users feel that it takes little screen space and it is bit frustrating to close it every time. After spending little time I discovered that we can get rid of time as following.

Click on System Settings. It will open System settings in new window.

Under out look tab go to the last option "Set whether users see CRM for Outlook message". Select 'No' and click OK.

Users will not get the "Get CRM for Outlook" message again, unitl you want it.

Same Steps can be followed to remove this message from CRM 2011 as well.

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

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

CRM 2011 - Error registering plugins and/or workflows. The resource string "ErrorSerializingRegFile" for the "RegisterPlugin" task cannot be found. Confirm that the resource name "ErrorSerializingRegFile" is correctly spelled, and the resource exists in the task's assembly.

noreply@blogger.com (Unknown) — Tue, 29 Apr 2014 10:07:00 +0000

This is very common error which we get while deploying CRM 2011 plugins solution using Developer Tool Kit. I have experienced this only where source is kept in TFS. I have used SVN in past and have not had any issues. But once we migrated to TFS we started getting this error.

I have seen many people over the internet and new starters in my team asking about the same error. So I thought about writing this blog.

Error: Error registering plugins and/or workflows. The resource string "ErrorSerializingRegFile" for the "RegisterPlugin" task cannot be found. Confirm that the resource name "ErrorSerializingRegFile" is correctly spelled, and the resource exists in the task's assembly.

Cause: When we deploy the solution using developer tool kit in Visual studio, it tries to update RegisterFile.crmregister. Because its checked in to TFS, system fails to check-out and update the file.

Solution: Checkout the RegisterFile.crmregister file manually and deploy again.

I hope it will help many people.

Happy Coding.

P S Hayer

( ਪ੍ਰੇਮਜੀਤ ਸਿੰਘ ਹੇਰ )

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