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;
            }
        }
Advertisements

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>