Find User with least number of Tasks due on a Date

In this scenario, we need to find a user in a team with the least number of Tasks assigned to them on a particular date so that we can assign a newly created Task record to them. This example can easily be extended to look at all activities due on a date (activitypointer record).


using System;
using System.ComponentModel;
using System.ComponentModel.Design;
using System.Collections.Generic;
using System.Workflow.ComponentModel.Compiler;
using System.Workflow.ComponentModel.Serialization;
using System.Workflow.ComponentModel;
using System.Workflow.ComponentModel.Design;
using System.Workflow.Runtime;
using System.Workflow.Activities;
using System.Workflow.Activities.Rules;

using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using Microsoft.Crm.Sdk.Query;
using Microsoft.Crm.Workflow;
using Microsoft.Crm.Workflow.Activities;

namespace CustomWorkflow
{    
    [CrmWorkflowActivity("Find User with least Tasks due on specified Date", "Custom")]
	public partial class AvailableUser: SequenceActivity
	{
        protected override ActivityExecutionStatus Execute(ActivityExecutionContext executioncontext)
        {
            # region get an instance of the Crm Web service
            IContextService contextservice = (IContextService)executioncontext.GetService(typeof(IContextService));
            IWorkflowContext context = contextservice.Context;
            ICrmService crm = context.CreateCrmService(true);
            # endregion

            GetUserWhoHasLeastTasksDueOn(crm);

            return ActivityExecutionStatus.Closed;
        }

        private void GetUserWhoHasLeastTasksDueOn(ICrmService crm)
        {
            # region variables
            List users = null;
            int? leastcount = null;
            Guid leastuser = Guid.Empty;
            # endregion

            /* get a list of users in the team */
            users = GetTeamMembers(crm);

            /* only proceed if we have found some users */
            if (users != null)
            {
                foreach (Guid userid in users)
                {
                    /* get a list of Task activities due on the specified date for this user */
                    List tasks = FindTasksDueOnDateForUser(userid, crm);

                    /* check if this user's due Tasks are less than what we've found so far */
                    if (tasks != null && ( leastcount == null || leastcount > tasks.Count))
                    {
                        leastcount = tasks.Count;
                        leastuser = userid;
                    }
                }
            }

            /* assign our output User variable a value */
            if (!leastuser.Equals(Guid.Empty))
                user = new Lookup(EntityName.systemuser.ToString(), leastuser);
        }

        private List FindTasksDueOnDateForUser(Guid userid, ICrmService crm)
        {
            List tasks = null;

            try
            {
                ConditionExpression owner = new ConditionExpression("ownerid", ConditionOperator.Equal, userid.ToString());
                ConditionExpression duedate = new ConditionExpression("scheduledend", ConditionOperator.On, dueDate.Value);

                FilterExpression filter = new FilterExpression();
                filter.AddCondition(owner);
                filter.AddCondition(duedate);
                filter.FilterOperator = LogicalOperator.And;

                ColumnSet cols = new ColumnSet(new string[] { "activityid" });

                QueryExpression query = new QueryExpression();
                query.ColumnSet = cols;
                query.Criteria = filter;
                query.EntityName = EntityName.task.ToString();

                BusinessEntityCollection collection = crm.RetrieveMultiple(query);

                if (collection != null)
                    tasks = collection.BusinessEntities;
            }
            catch
            {
            }

            return tasks;
        }

        private List GetTeamMembers(ICrmService crm)
        {
            List members = null;

            try
            {
                RetrieveMembersTeamRequest request = new RetrieveMembersTeamRequest();
                request.EntityId = team.Value;
                request.MemberColumnSet = new ColumnSet(new string[] { "systemuserid" });

                RetrieveMembersTeamResponse response = (RetrieveMembersTeamResponse)crm.Execute(request);

                members = new List();

                if (response.BusinessEntityCollection != null)
                    foreach (systemuser user in response.BusinessEntityCollection.BusinessEntities)
                        members.Add(user.systemuserid.Value);
            }
            catch
            {
            }

            return members;
        }

        # region workflow dependencies (inputs and outputs)
        public static DependencyProperty dueDateProperty = DependencyProperty.Register("dueDate", typeof(CrmDateTime), typeof(AvailableUser));

        public static DependencyProperty teamProperty = DependencyProperty.Register("team", typeof(Lookup), typeof(AvailableUser));

        public static DependencyProperty userProperty = DependencyProperty.Register("user", typeof(Lookup), typeof(AvailableUser));

        [CrmInput("The estimated close date")]
        public CrmDateTime dueDate
        {
            get
            {
                return (CrmDateTime)base.GetValue(dueDateProperty);
            }
            set
            {
                base.SetValue(dueDateProperty, value);
            }
        }

        [CrmInput("The team handling this Task")]
        [CrmReferenceTarget("team")]
        public Lookup team
        {
            get
            {
                return (Lookup)base.GetValue(teamProperty);
            }
            set
            {
                base.SetValue(teamProperty, value);
            }
        }

        [CrmOutput("Available User")]
        [CrmReferenceTarget("systemuser")]
        public Lookup user
        {
            get
            {
                return (Lookup)base.GetValue(userProperty);
            }
            set
            {
                base.SetValue(userProperty, value);
            }
        }
        # endregion
    }
}

Self Referential Many to Many Relationship

I created a Many to Many relationship to and from the User entity- nothing out of the ordinary. I then tried to programmatically retrieve the records associated to this relationship (ie, to the intersect table). I specified the following intersect rule:


LinkEntity filteringLink = new LinkEntity();
filteringLink.LinkFromEntityName = relationshipName;
filteringLink.LinkFromAttributeName = secondaryEntityPK;
filteringLink.LinkToEntityName = secondaryEntityName;
filteringLink.LinkToAttributeName = secondaryEntityPK;
filteringLink.LinkCriteria = filter;

LinkEntity link = new LinkEntity();
link.LinkFromEntityName = primaryEntityName;
link.LinkFromAttributeName = primaryEntityPK;
link.LinkToEntityName = relationshipName;
link.LinkToAttributeName = primaryEntityPK;
link.LinkEntities = new LinkEntity[] { filteringLink };

But I kept getting an error saying: ” ‘geni_followedby’ entity doesn’t contain attribute with Name = ‘systemuserid’.\n Platform\n”.

Looking at the database revealed the obvious issue- because it’s a self referential relationship, both key’s have the same name (systemuserid), so they get renamed to systemuseridone and systemuseridtwo. So I changed my code as follows (quick hack):


LinkEntity filteringLink = new LinkEntity();
filteringLink.LinkFromEntityName = relationshipName;
filteringLink.LinkFromAttributeName = secondaryEntityPK + "two";
filteringLink.LinkToEntityName = secondaryEntityName;
filteringLink.LinkToAttributeName = secondaryEntityPK;
filteringLink.LinkCriteria = filter;

LinkEntity link = new LinkEntity();
link.LinkFromEntityName = primaryEntityName;
link.LinkFromAttributeName = primaryEntityPK;
link.LinkToEntityName = relationshipName;
link.LinkToAttributeName = primaryEntityPK + "one"
link.LinkEntities = new LinkEntity[] { filteringLink };

Using JavaScript to assign a record

A while back, a user on the Microsoft Dynamics forums asked how to assign a record using JavaScript. Here’s the code, remember to update the userid and recordid variables.

var header = GenerateAuthenticationHeader();

var userid = "838975f4-029c-de11-9693-0003ffc4c746";
var recordid = "9721c859-d185-dc11-ac34-000c291d8da3";
var target = "TargetOwnedAccount";

var xml = "" +
"<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
"<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
header +
" <soap:Body>" +
" <Execute xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
" <Request xsi:type=\"AssignRequest\">" +
" <Target xsi:type=\""+ target +"\">" +
" <EntityId>"+ recordid +"</EntityId>" +
" </Target>" +
" <Assignee>" +
" <PrincipalId xmlns=\"http://schemas.microsoft.com/crm/2006/CoreTypes\">"+ userid +"</PrincipalId>" +
" <Type xmlns=\"http://schemas.microsoft.com/crm/2006/CoreTypes\">User</Type>" +
" </Assignee>" +
" </Request>" +
" </Execute>" +
" </soap:Body>" +
"</soap:Envelope>" +
"";

var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");

xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Execute");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);

var resultXml = xmlHttpRequest.responseXML;

(I pasted it here so that I can find it easily in future).

SetStateDynamicEntity on Incident Entity

I wrote a plug-in that needs to perform some validation when a user tries to close an Incident record in CRM and verify whether it can be closed or not. I registered it against the SetStateDynamicEntity, however it didn’t trigger. I then registered it against the Create of the IncidentClose entity, but it didn’t trigger there either (probably because I registered in the parent pipleine rather than the child). I then realised there is a Close message and registered against that instead, and it worked!

The SetStateDynamicEntity message is only triggered when an Incident is re-activated after being closed.

Querying a PartyList field

CRM doesn’t allow a PartyList field to be queried using a QueryExpression (it doesn’t allow it using Advanced Find either). A colleague had tried to query the Campaign Response records to ascertain whether a Contact had responded to a Campaign before before performing some other process(es).

The trick was to query the Activity Pointer entity (where the regardingobjectid is the Campaign) and then perform a link to the Activity Party entity (where the party is the Contact). The following function shows this:


private static bool AlreadyResponded(string strCampaignId, string strCustomerId)
{
ConditionExpression condition = new ConditionExpression();
condition.AttributeName = "regardingobjectid";
condition.Operator = ConditionOperator.Equal;
condition.Values = new object[] { strCampaignId };
FilterExpression filter = new FilterExpression();
filter.Conditions = new ConditionExpression[] { condition };
filter.FilterOperator = LogicalOperator.And;
# region link to the Activity Party
entityLinkEntity link = new LinkEntity();
link.LinkFromEntityName = EntityName.activitypointer.ToString();
link.LinkToEntityName = EntityName.activityparty.ToString();
link.LinkFromAttributeName = "activityid";
link.LinkToAttributeName = "activityid";
ConditionExpression linkCondition = new ConditionExpression();
linkCondition.AttributeName = "partyid";
linkCondition.Operator = ConditionOperator.Equal;
linkCondition.Values = new object[] { strCustomerId };
FilterExpression linkFilter = new FilterExpression();
linkFilter.Conditions = new ConditionExpression[] { linkCondition };
linkFilter.FilterOperator = LogicalOperator.And;
link.LinkCriteria = linkFilter;
# endregion
QueryExpression query = new QueryExpression();
query.ColumnSet = new AllColumns();
query.Criteria = filter;
query.EntityName = EntityName.activitypointer.ToString();
query.LinkEntities = new LinkEntity[] { link };
BusinessEntityCollection collection = crm.RetrieveMultiple(query);
if (collection != null && collection.BusinessEntities.Length > 0)
return true;
return false;
}