Re-assign Account/User territory in Dynamics CRM 2011

Unfortunately Dynamics CRM 2011 does not support deactivating Territory, If you get a situation where you want to delete the existing territory, you need to first check if it is associated to any accounts or users, if the territory has salespeople(users) or accounts associated with it, the deletion will not succeed, Territory entity have 1:N relationships with “Referential, Restrict Delete” behavior on Account and User, Users cannot directly delete a territory, If the Territory has Users or Accounts associated with it, In order to delete the Territory, need to reassign associated Users and Accounts to different Territory.

Following code helps to re-assign accounts and users territory from one territory to another one.

 private void ReAssignAccountTerritories(IOrganizationService service, string oldTerritoryName, string newTerritoryName)
        {
            try
            {
                string accountsFetchXML = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'>
                                                            <entity name='account'>
                                                                <attribute name='accountid' />
                                                                <attribute name='name' />
                                                                <link-entity name='territory' from='territoryid' to='territoryid' alias='aa'>
                                                                    <filter type='and'>
                                                                        <condition attribute='name' operator='eq' value='{0}' />
                                                                    </filter>
                                                                </link-entity>
                                                            </entity>
                                                        </fetch>", oldTerritoryName);

                var accountsFetchExp = new FetchExpression(accountsFetchXML);

                //Get Accounts with Old Territory
                EntityCollection accountsResults = service.RetrieveMultiple(accountsFetchExp);

                string territoryFetchXML = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'>
                                                            <entity name='territory'>
                                                                <attribute name='territoryid' />
                                                                <filter type='and'>
                                                                    <condition attribute='name' operator='eq' value='{0}' />
                                                                </filter>
                                                            </entity>
                                                        </fetch>", newTerritoryName);

                var territoryFetchExp = new FetchExpression(territoryFetchXML);
                //Get TerritoryId based on TerritoryName
                EntityCollection territoryResults = service.RetrieveMultiple(territoryFetchExp);

                EntityReference territoryReference = null;

                if (territoryResults.Entities != null && territoryResults.Entities.Count > 0)
                    territoryReference = new EntityReference("territory", territoryResults.Entities[0].Id);

                foreach (Entity account in accountsResults.Entities)
                {
                    if (territoryReference != null)
                    {
                        //Update Account Territory with new Territory
                        account.Attributes.Add("territoryid", territoryReference);
                        service.Update(account);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void ReAssignUserTerritories(IOrganizationService service, string oldTerritoryName, string newTerritoryName)
        {
            try
            {
                string usersFetchXML = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'>
                                                        <entity name='systemuser'>
                                                            <attribute name='systemuserid' />
                                                            <attribute name='fullname' />
                                                            <link-entity name='territory' from='territoryid' to='territoryid' alias='aa'>
                                                                <filter type='and'>
                                                                    <condition attribute='name' operator='eq' value='{0}' />
                                                                </filter>
                                                            </link-entity>
                                                        </entity>
                                                    </fetch>", oldTerritoryName);

                var usersFetchExp = new FetchExpression(usersFetchXML);

                EntityCollection usersResults;

                //Get Users with Old Territory
                usersResults = service.RetrieveMultiple(usersFetchExp);

                string territoryFetchXML = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'>
                                                            <entity name='territory'>
                                                                <attribute name='territoryid' />
                                                                <filter type='and'>
                                                                    <condition attribute='name' operator='eq' value='{0}' />
                                                                </filter>
                                                            </entity>
                                                        </fetch>", newTerritoryName);

                var territoryFetchExp = new FetchExpression(territoryFetchXML);
                //Get TerritoryId based on TerritoryName
                EntityCollection territoryResults = service.RetrieveMultiple(territoryFetchExp);

                EntityReference territoryReference = null;

                if (territoryResults.Entities != null && territoryResults.Entities.Count > 0)
                    territoryReference = new EntityReference("territory", territoryResults.Entities[0].Id);

                foreach (Entity user in usersResults.Entities)
                {
                    if (territoryReference != null)
                    {
                        //Update User Territory with new Territory
                        user.Attributes.Add("territoryid", territoryReference);
                        service.Update(user);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

Fetch XML and ConditionExpression operators using C# in Dynamics CRM 2011

Following table describes different types of operators we can use in a ConditionExpression and Fetch XML in Dynamics CRM 2011

ConditionOperator FetchXML Operator Description
BeginsWith like The string occurs at the beginning of another string.
Between between The value is between two values.
Contains like The string contains another string.
DoesNotBeginWith not-like The string does not begin with another string.
DoesNotContain not-like The string does not contain another string.
DoesNotEndWith not-like The string does not end with another string.
EndsWith like The string ends with another string.
Equal eq The values are compared for equality.
EqualBusinessId eq-businessid The value is equal to the specified business ID.
EqualUserId eq-userid The value is equal to the specified user ID.
EqualUserTeams eq-userteams The record is owned by teams that the user is a member of.
GreaterEqual ge The value is greater than or equal to the compared value.
GreaterThan gt The value is greater than the compared value.
In in TheThe value exists in a list of values.
InFiscalPeriod in-fiscal-period The value is within the specified fiscal period.
InFiscalPeriodAndYear in-fiscal-period-and-year The value is within the specified fiscal period and year.
InFiscalYear in-fiscal-year The value is within the specified year.
InOrAfterFiscalPeriodAndYear in-or-after-fiscal-period-and-year The value is within or after the specified fiscal period and year.
InOrBeforeFiscalPeriodAndYear in-or-before-fiscal-period-and-year The value is within or before the specified fiscal period and year.
Last7Days last-seven-days The value is within the last seven days including today.
LastFiscalPeriod last-fiscal-period The value is within the last fiscal period.
LastFiscalYear last-fiscal-year The value is within the last fiscal year.
LastMonth last-month The value is within the last month including first day of the last month and last
day of the last month.
LastWeek last-week The value is within the previous week including Sunday through Saturday.
LastXDays last-x-days The value is within last X days.
LastXFiscalPeriods last-x-fiscal-periods The value is within the last X (specified value) fiscal periods.
LastXFiscalYears last-x-fiscal-years The value is within the last X (specified value) fiscal periods.
LastXHours last-x-hours The value is within the last X hours.
LastXMonths last-x-months The value is within the last X (specified value) months.
LastXWeeks last-x-weeks The value is within the last X (specified value) weeks.
LastXYears last-x-years The value is within the last X years.
LastYear last-year The value is within the previous year.
LessEqual le The value is less than or equal to the compared value.
LessThan lt The value is less than the compared value.
Like like The character string is matched to the specified pattern.
Next7Days next-seven-days The value is within the next seven days.
NextFiscalPeriod next-fiscal-period The value is within the next fiscal period.
NextFiscalYear next-fiscal-year The value is within the next fiscal year.
NextMonth next-month The value is within the next month.
NextWeek next-week The value is within the next week.
NextXDays next-x-days The value is within the next X (specified value) days.
NextXFiscalPeriods next-x-fiscal-periods The value is within the next X (specified value) fiscal period.
NextXFiscalYears next-x-fiscal-years The value is within the next X (specified value) fiscal years.
NextXHours next-x-hours The value is within the next X (specified value) hours.
NextXMonths next-x-months The value is within the next X (specified value) months.
NextXWeeks next-x-weeks The value is within the next X weeks.
NextXYears next-x-years The value is within the next X years.
NextYear next-year The value is within the next year.
NotBetween not-between The value is not between two values.
NotEqual ne The two values are not equal.
NotEqualBusinessId ne-businessid The value is not equal to the specified business ID.
NotEqualUserId ne-userid The value is not equal to the specified user ID.
NotIn not-in The given value is not matched to a value in a subquery or a list.
NotLike not-like The character string does not match the specified pattern.
NotNull not-null The value is not null.
NotOn ne The value is not on the specified date.
Null null The value is null.
OlderThanXMonths olderthan-x-months The value is older than the specified number of months.
On on The value is on a specified date.
OnOrAfter on-or-after The value is on or after a specified date.
OnOrBefore on-or-before The value is on or before a specified date.
ThisFiscalPeriod this-fiscal-period The value is within the current fiscal period.
ThisFiscalYear this-fiscal-year The value is within the current fiscal year.
ThisMonth this-month The value is within the current month.
ThisWeek this-week The value is within the current week.
ThisYear this-year The value is within the current year.
Today today The value equals today’s date.
Tomorrow tomorrow The value equals tomorrow’s date.
Yesterday yesterday The value equals yesterday’s date.

Use NoLock and Distinct in Fetch XML and QueryExpression using C# in Dynamics CRM 2011

NoLock: It indicates that no database locks are issued against the data that would prohibit other transactions from modifying the data in the records returned from the query.

Distinct:  It indicates whether results of the query contains duplicate entity instances

Using these in FetchXML and QueryExpression improves performance for checking/fetching results

 


 public static EntityCollection GetAccounts(IOrganizationService service, string accountName)
        {
            QueryExpression queryExp = new QueryExpression();
            queryExp.EntityName = "account";
            var cols = new ColumnSet(new[] { "name", "accountid" });
            queryExp.ColumnSet = cols;

            ConditionExpression conExp = new ConditionExpression();
            conExp.AttributeName = "name";
            conExp.Operator = ConditionOperator.Like;
            conExp.Values.Add(accountName);

            FilterExpression filterExp = new FilterExpression();       
            filterExp.Conditions.Add(conExp);

            queryExp.Criteria = filterExp;
                        
            queryExp.Distinct = false;
            
            queryExp.NoLock = true;

            var accounts = service.RetrieveMultiple(queryExp);

            return accounts;
        }

        public static EntityCollection GetAccounts(IOrganizationService service, string accountName)
        {            
            string fetchXML = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'>
                                                        <entity name='account'>                                                                
                                                            <attribute name='accountid' />
                                                            <attribute name='name' />
                                                            <filter type='and'>
                                                                <condition attribute='name' operator='like' value='{0}' />                                                                                                    
                                                            </filter>
                                                        </entity>
                                                    </fetch>", accountName);

            var fetchExp = new FetchExpression(fetchXML);

            var accounts = service.RetrieveMultiple(fetchExp);

            return accounts;
        }

Get fetch XML link-entity attributes using C# in Dynamics CRM 2011

Following example code shows how to get Account Primary Contact details using fetch XML link-entity

Note: Specify ‘alias’ name different than ‘attributename’ and use same alias name in GetAttributeValue


        //Get Account PrimaryContact details
        public static void GetAccountPrimaryContactDetails(Guid accountId, IOrganizationService orgService)
        {
            var contactFirstName = default(object);
            var contactLastName = default(object);
            var contactFullName = default(object);

            string fetchXML = string.Format(@"<fetch version='1.0' output-format='xml-platform' no-lock='true' mapping='logical'>
                                                <entity name='account'>
                                                    <attribute name='name' />                                                                                
                                                    <filter type='and'>
                                                        <condition attribute='statuscode' operator='eq' value='1' />                                            
                                                        <condition attribute='accountid' operator='eq' value='{0}' />
                                                    </filter>                                        
                                                    <link-entity name='contact' from='contactid' to='primarycontactid' alias='ab'>
                                                         <attribute name='fullname' alias='as_fullname' />
                                                         <attribute name='firstname' alias='as_firstname' />                                             
											             <attribute name='lastname' alias='as_lastname' />
                                                    </link-entity>
                                                </entity>
                                            </fetch>", accountId.ToString());

            var fetchExp = new FetchExpression(fetchXML);

            EntityCollection accountEntity = orgService.RetrieveMultiple(fetchExp);

            if (accountEntity.Entities.Count > 0)
            {
                //Primary Contact Fullname
                AliasedValue avContactFullname = accountEntity.Entities[0].GetAttributeValue<AliasedValue>("as_fullname");
                if (avContactFullname != null)
                    contactFullName = avContactFullname.Value;
                //Primary Contact Firstname
                AliasedValue avContactFirstname = accountEntity.Entities[0].GetAttributeValue<AliasedValue>("as_firstname");
                if (avContactFirstname != null)
                    contactFirstName = avContactFirstname.Value;
                //Primary Contact Lastname
                AliasedValue avContactLastname = accountEntity.Entities[0].GetAttributeValue<AliasedValue>("as_lastname");
                if (avContactLastname != null)
                    contactLastName = avContactLastname.Value;
            }
        }

Get Merged Accounts/Contacts/Leads in Dynamics CRM 2011

Here is the SQL query and FetchXML to get merged Accounts.

--Get Merged Accounts
SELECT FA.name,fa.masterid AS 'SubOrdinate Account',FA.masteraccountidname AS 'Master Account' 
FROM FilteredAccount (NOLOCK) FA 
WHERE Fa.merged=1

<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'>
  <entity name='account'>
    <attribute name='name' alias='SubOrdinate_Account' />
    <attribute name='masterid' alias='Master_Account' />
    <filter>
      <condition attribute='merged' operator='eq' value='1' />
    </filter>
  </entity>
</fetch>

Here is the SQL query and FetchXML to get merged Contacts.

--Get Merged Contacts
SELECT FC.fullname AS 'SubOrdinate Contact',FC.mergedname AS 'Master Contact' 
FROM FilteredContact (NOLOCK) FC 
WHERE FC.merged=1

<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'>
  <entity name='contact'>
    <attribute name='name' alias='SubOrdinate_Contact' />
    <attribute name='masterid' alias='Master_Contact' />
    <filter>
      <condition attribute='merged' operator='eq' value='1' />
    </filter>
  </entity>
</fetch>

Here is the SQL query and FetchXML to get merged Leads.

--Get Merged Leads
SELECT FL.fullname AS 'SubOrdinate Lead',FL.mergedname AS 'Master Lead' 
FROM FilteredLead (NOLOCK) FL 
WHERE FL.merged=1

<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='true' distinct='false'>
  <entity name='lead'>
    <attribute name='name' alias='SubOrdinate_Lead' />
    <attribute name='masterid' alias='Master_Lead' />
    <filter>
      <condition attribute='merged' operator='eq' value='1' />
    </filter>
  </entity>
</fetch>