Tag Archives: CRM

System Form Dependency To A Custom Entity – CheckList

Posted on by
Reply

Following checklist should be completed in order to completely remove the all the dependency before exporting the solution and importing to your target environment.

  1. Make sure all relationships are removed ( i.e navigation from left hand side on form editor)
  2. Any Lookup field from that custom entity is removed
  3. The most important and neglected one and for which I find people scratching their heads is when this lookup is removed from the form and even entity is deleted. Still  Get dependency error something like below:dependency.PNG
    Resolution:  The lookup field was being referenced on another lookup which was acting as a “Dependent Lookup”. hence this entity was showing as system form dependency.  So i went to other lookup field —> changed properties and unchecked the option “Show related records”.You can also try to remove that field –> Publish —> add again —-publish.

Hope this helps!

 

Advertisement

Connect to Dynamics CRM 365 Online from Excel [Reporting]

Posted on by
1

I have seen a few blogs on how to connect to dynamics crm but most of them talks about connect with Odata endpoint. However with dynamics 365, Odata has  been deprecated hence you cannot use them and will get error.

Below is the step by step process to connect to dynamics crm 365 online from Excel.

  1. If you do not have power query option in your excel or its an old version. Please download the excel addin from here : https://www.microsoft.com/en-us/download/details.aspx?id=39379   and run the setup chose 32 or 64bit as per your excel.
  2. Once done, Launch Excel and select “Power Query” Tab –>”From Online Services” Option  and select “From Dynamics 365(Online) from the dropdown.
    1.png
  3. In the next tab add Web Api endpoint of your online CRM ( you can find it from Customizations–>Developer Resources–>Copy “Instance Web API” url and paste it below:
    2
  4. In the next authentication screen – select “Organizational Account” and then click on “Sign In”.
    3
  5. Following step 4 will take you to your organization sign page. Enter username and password and click Sign In.
    4.PNG
  6. If Signed In successfully; you will be landed back to the authentication page. Click “connect”.5.PNG
  7. Post this you will be presented with the connected org details( such as entities etc).
    6
    7.PNG
    I hope this helps.

How to connect to Dynamics CRM Onpremises AD/IFD from a Windows Form Application

Posted on by
Reply

Being a CRM developer, you often need to develop some external application. In this blog i will talk about and provide the code to connect to dynamics CRM Onpremises in a windows form application.

Login

public static ConnectToCrm(string username, string password, string domain, string Url)
{

ClientCredentials _clientCreds = new ClientCredentials();
_clientCreds.Windows.ClientCredential.UserName = username;
_clientCreds.Windows.ClientCredential.Password = password;
_clientCreds.Windows.ClientCredential.Domain = domain;

if (username == "" || password == "" || domain == "" || Url == "")
{
MessageBox.Show("Please Enter All Details To Login!");
}

_service = (IOrganizationService)new OrganizationServiceProxy(new Uri(Url), null, _clientCreds, null);

OrganizationServiceContext _orgContext = new OrganizationServiceContext(_service);
Guid orgId = ((WhoAmIResponse)_service.Execute(new WhoAmIRequest())).OrganizationId;

if (orgId != null)
{
MessageBox.Show("Successfully Connected to CRM. Click OK");
}
else if(orgId==null)
{
MessageBox.Show("Could Not Connect to CRM. Click OK & Try Again!");
}
}

The _service can be utilized in your methods to retrieve the data and perform actions in dynamics CRM. i.e, below:

var fetchExpression = new FetchExpression(fetchXml);
EntityCollection fetchResult = _service.RetrieveMultiple(fetchExpression);

hope this helps!

cheers!

Count Total Subgrid Records in Dynamics CRM using JavaScript

Posted on by
2

In one of my previous blog I explained,how a rollup field can be used to count child records : Count the number of related child records using a Rollup field

However, Rollup field is calculated with an Async System Job, therefore, if you need something to be triggered on every form load; you will have to write a JS.

Here is a quick code to count the number of related sub-grid record on a form onload. You can put an alert of the count or add that number to a field.

function getTotalGridRecordCount() {
 debugger;
 try {

setTimeout(function () {
 if (Xrm.Page != null && Xrm.Page != undefined && Xrm.Page.getControl("contactopportunitiesgrid") != null && Xrm.Page.getControl("contactopportunitiesgrid") != undefined) {
 var count = Xrm.Page.getControl("contactopportunitiesgrid").getGrid().getTotalRecordCount();
 alert("Total Opportunities:"+count);

}
 }, 5000);
 }
 catch (e) {
 Xrm.Utility.alertDialog(functionName + "Error: " + e.message || e.description);
 }
}

Add script to the form and call this function on Onload event. Open the form to see it in action:

1

 

Hope this helps! cheers!

Enable “Read” Auditing In Dynamics CRM 365

Posted on by
Reply

Let’s agree, we all have been waiting for this feature since quiet long now.

Records “Read” Audit! yes you have heard it right. This feature will let you capture details about who is reading/retrieving the data just like create/update/delete etc.

This feature is available in Dynamics CRM 365 Online  v8 and above.

1

Note : The auditing logs will be available in the Office 365 Security & Compliance Center not in the tradition record audit history in CRM which actually makes sense as it will be gigantic data and shouldn’t be kept inside CRM.

Follow below steps on how to start utilizing this feature:

  1.  To enable auditing please follow below method:
    https://docs.microsoft.com/en-us/dynamics365/customer-engagement/admin/enable-use-comprehensive-auditing#enable-auditing-in-dynamics-365
  2. To see the logs , follow below :
    https://docs.microsoft.com/en-us/dynamics365/customer-engagement/admin/enable-use-comprehensive-auditing#review-your-audit-data-using-reports-in-office-365-security-and-compliance-center

I hope this helps!

Open Lookup Dialog Programmatically using Xrm.Utility–Dynamics V9.0

Posted on by
Reply

Thanks Debajit.

Debajit's Dynamic CRM Blog

This one feature that I am going to pen down here, personally I have longing for it quite sometime now.

So before going into the HOW part of it, let’s understand the why part of it? When do I need show a Lookup dialog Programmatically? Well the answer is, numerous occasions. Like if you need to throw up a lookup dialog on change of field on the form OR you needed to throw the lookup dialog on click of a button on a web-resource.

All this time, we have achieved this but not in a supported way. Probably we may have ended up using or some method of rnal namespace. But all these are unsupported and mere workaround to this perennial problem.

Well, no more messing around. Microsoft has finally brought in the

So let’s see how it works.

Let’s take a not so good example here. Let’s say whenever…

View original post 296 more words

Singleton Retrieve Query Should Not Return More Than 1 Record [Manged Solution Import Error – OnPremise]

Posted on by
2

This is a common error during Managed solution deployments in Dynamics CRM Onpremise environments.

Cause:

This often caused by orphaned CustomControlDefaultConfig records. Its an internal CRM entity.  During back to back managed solution deployment, CRM will create these unwanted orphaned records.

Generally the solution import will take so much time and will not complete – you will have to look into event viewer or trace to look for this error:

singleton.JPG

Solution:

Take the database backup and run below sql script on the database – OrgnnizationName_MSCRM :

/* This script removes possible CustomControlDefaultConfig orphaned records and their associated dependency records.

Acronyms:
OTC = Object Type Code
CCDC = Custom Control Default Config
PETC = Primary Entity Type Code (OTC of entity associated with CCDC)

Steps:
1) Filter Records to correctRecords and recordsToDelete Local Tables
– Scenario 1: CCDC records associated with 2 ids or 2 PETCs
*Calculation 1: Single PrimaryEntityTypeCode records
*Calculation 2: Single CustomControlDefaultConfigId records
*Calculation 3: Multiple records with unique PrimaryEntityTypeCode and CustomControlDefaultConfigId
– Scenario 2: CCDC records associated with a deleted entity
*Includes records which have PETC > NextCustomObjectTypeCode (OTC for next custom entity)
2) Print Final Local Table Information:
– recordsToCheck
– Original table used to filter orphaned records
– Any records printed in this table were not sortable and will also appear in the recordsToDelete table
– recordsToDelete
– Orphaned (or unsortable) records to delete
– These records and all associated dependency records are deleted
– correctRecords
– Records which are correct in the DB
– associatedEntities
– Entities retrieved using PETC/OTC of deleted records
– These entities should be tested on the server to ensure proper behavior
3) Delete Orphaned Records from 3 DB Tables:
– CustomControlDefaultConfigBase
– DependencyBase
– DependencyNodeBase
4) Set Active Record for each PrimaryEntityTypeCode
– Only for single CCDC records associated with a PETC
– Mark as Active by setting OverwriteTime to the default value (1900-01-01 00:00:00.000)
*/

— Declare Variables
declare @recordsToCheck table (uniqueId uniqueidentifier, id uniqueidentifier, entityTypeCode int, overwriteTime DateTime, dependencyNodeId uniqueidentifier)
declare @recordsToCheckCopy table (uniqueId uniqueidentifier, id uniqueidentifier, entityTypeCode int, overwriteTime DateTime, dependencyNodeId uniqueidentifier)
declare @recordsToDelete table (uniqueId uniqueidentifier, id uniqueidentifier, entityTypeCode int, overwriteTime DateTime, dependencyNodeId uniqueidentifier)
declare @correctRecords table (uniqueId uniqueidentifier, id uniqueidentifier, entityTypeCode int, overwriteTime DateTime, dependencyNodeId uniqueidentifier)
declare @singleRecords table (uniqueId uniqueidentifier, id uniqueidentifier, entityTypeCode int, overwriteTime DateTime, dependencyNodeId uniqueidentifier)
declare @associatedEntities table (entityTypeCode int, entityName nvarchar(50))
declare @activeRowCount INT
declare @NewLineChar as char(2) = char(13) + char(10)

/* Step 1: Filter Records */
print ‘Step 1: Filter Records’

/* Scenario 1: Retrieve possible CCDC orphaned records which satisfy the following conditions:
1) Associated with custom entity (OTC > 10000)
2) Associated with an existing entity (entity has not been deleted)
3a) PETC associated with > 1 Id
— or —
3b) Id associated with > 1 PETC */
print ‘Scenario 1: Multiple Ids/PETCs’
insert into @recordsToCheck
select ccdc.CustomControlDefaultConfigIdUnique, ccdc.CustomControlDefaultConfigId, ccdc.PrimaryEntityTypeCode, ccdc.OverwriteTime, dn.DependencyNodeId
from CustomControlDefaultConfigBase ccdc
left join DependencyNodeBase dn
ON ccdc.CustomControlDefaultConfigId = dn.ObjectId
where (
CustomControlDefaultConfigId in (
select CustomControlDefaultConfigId
from CustomControlDefaultConfigBase
group by CustomControlDefaultConfigId
having count(distinct PrimaryEntityTypeCode) > 1
)
or PrimaryEntityTypeCode in (
select PrimaryEntityTypeCode
from CustomControlDefaultConfigBase
group by PrimaryEntityTypeCode
having count(distinct CustomControlDefaultConfigId) > 1
)
)
and PrimaryEntityTypeCode >= 10000
and PrimaryEntityTypeCode in (select ObjectTypeCode from Entity)
print ‘Inserted records into tableToCheck’

— Filter records depending on if they are correct and must be retained, or orphaned and must be deleted.
— Repeat logic process on records until there are no remaining records or no more can be sorted.
— There may be unsorted records left where it’s impossible to determine the correct records. These must also be deleted.
print @NewLineChar + ‘Begin Loop’
select @activeRowCount = Count(*) from @recordsToCheck
while (@activeRowCount <> 0)
BEGIN

— Make a copy of the updated recordsToCheck table
— This will be used to exit the while loop when all possible records have been sorted
delete from @recordsToCheckCopy
print ‘Deleted current recordsToCheckCopy’
insert into @recordsToCheckCopy
select * from @recordsToCheck
print ‘Updated recordsToCheckCopy’

/* Calculation 1: Single PrimaryEntityTypeCode Records */
print @NewLineChar + ‘Calculation 1: Single PrimaryEntityTypeCode Records’

— Find the records which only have 1 CCDC record associated with an existing entity (1 CCDC record per OTC/PETC)
— Insert into the singleRecords table
insert into @singleRecords
select * from @recordsToCheck
where entitytypecode in (
select entityTypeCode from @recordsToCheck
group by entityTypeCode
having count(entityTypeCode) = 1
)
print ‘Inserted records into singleRecords for Type Code’

— Using the singleRecords table, find records which have the same id associated (although they have different PETCs)
— We cannot decipher which is the correct records and must delete all records with the same id
insert into @recordsToDelete
select * from @singleRecords
where id in (
select id from @singleRecords
group by id
having count(id) > 1
)
print ‘Inserted records into recordsToDelete for all single records with same Id’

— Remove these incorrect records from recordsToCheck
delete rtc
from @recordsToCheck rtc
inner join @recordsToDelete rtd
on rtd.uniqueId = rtc.uniqueId
print ‘Removed same incorrect records from recordsToCheck’

— Remove these incorrect records from singleRecords
delete sr
from @singleRecords sr
inner join @recordsToDelete rtd
on rtd.uniqueId = sr.uniqueId
print ‘Removed same incorrect records from singleRecords’

— The remaining records in singleRecords must be correct since there is no other CCDC associated with the entity
— We have already filtered out records with the same id and different PETCs
insert into @correctRecords
select * from @singleRecords
print ‘Inserted remaining single records into correctRecords’

— Remove these correct records from the recordsToCheck
delete rtc
from @recordsToCheck rtc
inner join @correctRecords cr
on cr.uniqueId = rtc.uniqueId
print ‘Removed same correct records from recordsToCheck’

–Remove these correct records from singleRecords
delete from @singleRecords
print ‘Removed remaining records from singleRecords’

— Move incorrect records (using same ID associated with above OTCs) to the table for record deletion
— The correct type codes are already associated with this ID, thus records with the same ID but different OTC must be orphaned records
insert into @recordsToDelete
select * from @recordsToCheck
where id in (select id from @correctRecords)
print ‘Inserted records into recordsToDelete which share same Id’

— Remove these incorrect records from the recordsToCheck table
delete from @recordsToCheck
where id in (select id from @correctRecords)
print ‘Removed same incorrect records from recordsToCheck’

/* Calculation 2: Single CustomControlDefaultConfigId Records */
print @NewLineChar + ‘Calculation 2: Single CustomControlDefaultConfigId Records’

— Find the records which only have 1 CCDC record associated with a specific Id
— Insert into the singleRecords table
insert into @singleRecords
select * from @recordsToCheck
where id in (
select id from @recordsToCheck
group by id
having count(id) = 1
)
print ‘Inserted records into singleRecords for Id’

— Using the singleRecords table, find records which have the same entityTypeCode associated (although they have different Ids)
— We cannot decipher which is the correct records and must delete all records with the same entityTypeCode
insert into @recordsToDelete
select * from @singleRecords
where entityTypeCode in (
select entityTypeCode from @singleRecords
group by entityTypeCode
having count(entityTypeCode) > 1
)
print ‘Inserted records into recordsToDelete for all single records with same Type Code’

— Remove these incorrect records from recordsToCheck
delete rtc
from @recordsToCheck rtc
inner join @recordsToDelete rtd
on rtd.uniqueId = rtc.uniqueId
print ‘Removed same incorrect records from recordsToCheck’

— Remove these incorrect records from singleRecords
delete sr
from @singleRecords sr
inner join @recordsToDelete rtd
on rtd.uniqueId = sr.uniqueId
print ‘Removed same incorrect records from singleRecords’

— The remaining records in singleRecords must be correct since there is no other CCDC associated with the Id
— We have already filtered out records with the same entityTypeCode and different Ids
insert into @correctRecords
select * from @singleRecords
print ‘Inserted remaining single records into correctRecords’

— Remove these correct records from the recordsToCheck
delete rtc
from @recordsToCheck rtc
inner join @correctRecords cr
on cr.uniqueId = rtc.uniqueId
print ‘Removed same correct records from recordsToCheck’

–Remove these correct records from singleRecords
delete from @singleRecords
print ‘Removed remaining records from singleRecords’

— Move incorrect records (using same entityTypeCode associated with above Ids) to the table for record deletion
— The correct Ids are already associated with this entityTypeCode, thus records with the same entityTypeCode but different Ids must be orphaned records
insert into @recordsToDelete
select * from @recordsToCheck
where entityTypeCode in (select entityTypeCode from @correctRecords)
print ‘Inserted records into recordsToDelete which share same Type Code’

— Remove these incorrect records from the recordsToCheck table
delete from @recordsToCheck
where entityTypeCode in (select entityTypeCode from @correctRecords)
print ‘Removed same incorrect records from recordsToCheck’

/* Calculation 3: Multiple Records with unique TypeCode and Id */
print @NewLineChar + ‘Calculation 3: Multiple Records with unique TypeCode and Id’

— Add records which have unique and matching OTCs and Ids to the correctRecords table
— This case is for multiple records that share both the same id and PETC
insert into @correctRecords
select * from @recordsToCheck rtc
where id in (
select id from @recordsToCheck
group by id
having count(distinct entityTypeCode) = 1
)
and entityTypeCode in (
select entityTypeCode from @recordsToCheck
group by entityTypeCode
having count(distinct id) = 1
)
print ‘Inserted records into correctRecords for multiple records with unique Type Codes and Ids’

— Remove these correct records from recordsToCheck table
delete rtc
from @recordsToCheck rtc
inner join @correctRecords cr
on cr.uniqueId = rtc.uniqueId
print ‘Removed same records from recordsToCheck’

— Reset count variable to ensure we are still finding records which can be sorted
select @activeRowCount = count(*) from (
select uniqueId from @recordsToCheckCopy
except
select uniqueId from @recordsToCheck) as subquery

END
print @NewLineChar + ‘End Loop’

— Move remaining recordsToCheck to table for deletion. They may contain both orphaned and valid records,
— but it is impossible to decipher which are the orphaned records in the DB.
— Note: For valid records getting deleted, new CCDC records will get created for an entity if the control tab configuration is updated.
insert into @recordsToDelete
select * from @recordsToCheck
print ‘Inserted unsorted records into recordsToDelete’

/* Scenario 2: Retrieve CCDC orphaned records which are associated with an entity that no longer exists.
This will include records with PETC > NextCustomObjectTypeCode since they are leftover from deleted entities */
print @NewLineChar + ‘Scenario 2: No Associated Entity’
insert into @recordsToDelete
select ccdc.CustomControlDefaultConfigIdUnique, ccdc.CustomControlDefaultConfigId, ccdc.PrimaryEntityTypeCode, ccdc.OverwriteTime, dn.DependencyNodeId
from CustomControlDefaultConfigBase ccdc
left join DependencyNodeBase dn
on ccdc.CustomControlDefaultConfigId = dn.ObjectId
where ccdc.PrimaryEntityTypeCode not in(select ObjectTypeCode from Entity)
print ‘Inserted records into recordsToDelete with no associated entity’

/* Gather Entity Information for Deleted Records */
insert into @associatedEntities
select ObjectTypeCode, Name from Entity
where ObjectTypeCode in (
select entityTypeCode
from @recordsToDelete
)

/* Step 2: Print Local Table Information */
print @NewLineChar + ‘Step 2: Local Table Information’

— Print the records to delete to the console
— These are the orphaned records in the DB
declare @xmlRecordsToDelete xml = (select * from @recordsToDelete for XML auto)
print convert(nvarchar(max), @xmlRecordsToDelete)

— Print the entity information associated with the deleted records using OTC/PETC
— These are the entities that should be tested on the server
declare @xmlAssociatedEntities xml = (select * from @associatedEntities for XML auto)
print convert(nvarchar(max), @xmlAssociatedEntities)

— Print any remaining unsorted records. These will also appear in the recordsToDelete table
— If there exist unsorted records, the solution used for import may also be in a bad state
declare @xmlRecordsToCheck xml = (select * from @recordsToCheck for XML auto)
print convert(nvarchar(max), @xmlRecordsToCheck)

— Print correct records which are kept in the DB
declare @xmlCorrectRecords xml = (select * from @correctRecords for XML auto)
print convert(nvarchar(max), @xmlCorrectRecords)

/* Step 3: Delete Orphaned Records */
print @NewLineChar + ‘Step 3: Delete Orphaned Records’

— Delete records from the CCDC table
delete from CustomControlDefaultConfigBase
where CustomControlDefaultConfigIdUnique in (select uniqueId from @recordsToDelete)
print ‘Deleted from CustomControlDefaultConfigBase’

— Delete associated dependency records
— Only delete if not associated with any correct records
delete from DependencyBase
where DependentComponentNodeId in (
select rtd.dependencyNodeId from @recordsToDelete rtd
inner join @correctRecords cr
on rtd.dependencyNodeId <> cr.dependencyNodeId
)
or RequiredComponentNodeId in (
select rtd.dependencyNodeId from @recordsToDelete rtd
inner join @correctRecords cr
on rtd.dependencyNodeId <> cr.dependencyNodeId
)
print ‘Deleted from DependencyBase’

— Delete associated dependency node records
— Only delete if not associated with any correct records
delete from DependencyNodeBase
where ObjectId in (
select rtd.id from @recordsToDelete rtd
inner join @correctRecords cr
on rtd.id <> cr.id
)
print ‘Deleted from DependencyNodeBase’

/* Step 4: Set Active Record for each PrimaryEntityTypeCode */
print @NewLineChar + ‘Step 4: Set Active Record for each PrimaryEntityTypeCode’

— For each correct record whose PETC is only associated with this record, mark it as the active record.
— Since it is the only record associated with the entity whose OTC = PETC, it must be the active record.
— Setting overwrite time to the default value will mark the record as active.
update CustomControlDefaultConfigBase
set OverwriteTime = ‘1900-01-01 00:00:00.000’
where CustomControlDefaultConfigIdUnique in (
select uniqueId from @correctRecords where entitytypecode in (
select entityTypeCode from @correctRecords
group by entityTypeCode
having count(entityTypeCode) = 1
)
)

 

I hope this helps.

Cheers!