Dynamics CRM Plugin Query TotalRecordCount returning -1
I am working on a plugin and have written a function to pull back any incidents that have the ticket number passed into the function. A fairly simple function but for some reason I am receiving back a -1 value from the property TotalRecordCount on the EntityCollection. My understanding is that this means it is not finding any records at all but I know that there is a record with the ticket number being passed in. I must be missing something anyone have any thoughts?
internal EntityReference fetchCaseByCaseNumber(string caseNumber, IOrganizationService service, ITracingService tracingService)
{
Entity incident;
//tracingService.Trace("Creating QueryByAttribute");
//QueryByAttribute querybyattribute = new QueryByAttribute("incident");
//querybyattribute.ColumnSet = new ColumnSet("incidentid", "ticketnumber");
//querybyattribute.Attributes.AddRange("ticketnumber");
//querybyattribute.Values.AddRange(caseNumber);
//EntityCollection incidents = service.RetrieveMultiple(querybyattribute);
tracingService.Trace("Creating Query...");
QueryExpression query = new QueryExpression
{
EntityName = "incident",
//PageInfo = new PagingInfo() {count },
ColumnSet = new ColumnSet("incidentid", "ticketnumber"),
Criteria = new FilterExpression
{
Conditions =
{
new ConditionExpression
{
AttributeName = "ticketnumber",
Operator = ConditionOperator.Equal,
Values = {caseNumber}
}
}
}
};
tracingService.Trace("Running Query");
EntityCollection incidents = service.RetrieveMultiple(query);
tracingService.Trace("fetching total count");
int count = incidents.TotalRecordCount;
// incident = incidents.Entities.First();
if (count == 1)
{
incident = incidents.Entities.First();
}
else
{
tracingService.Trace(String.Format("{0} incidents found with ticketnumber",incidents.TotalRecordCount.ToString()));
throw new InvalidPluginExecutionException("Duplicate ticket number error");
}
EntityReference caseReference = new EntityReference("incident",incident.Id);
return caseReference;
}
Principal Consultant
CERTIFIED EXPERT
I think -1 means that you didn't ask for the total record count to be returned.
You need to set ReturnTotalRecordCount property to true as per this MSDN article: https://msdn.microsoft.com/en-us/library/gg334688.aspx
You need to set ReturnTotalRecordCount property to true as per this MSDN article: https://msdn.microsoft.com/en-us/library/gg334688.aspx
Feridan I was able to get the TotalRecordCount to show per your recommendation but I am still not getting any records back. TotalRecordCount = 0
I am passing in the string value 000003 and I am getting zero records returned. Even though this SQL pulls exactly one record as expected
Here is the Updated Plugin Code as well.
I am passing in the string value 000003 and I am getting zero records returned. Even though this SQL pulls exactly one record as expected
SELECT TicketNumber, *
FROM IncidentBase
WHERE TicketNumber = '000003'
Here is the Updated Plugin Code as well.
internal EntityReference fetchCaseByCaseNumber(string caseNumber, IOrganizationService service, ITracingService tracingService)
{
Entity incident;
tracingService.Trace("Creating Query...");
QueryExpression query = new QueryExpression
{
EntityName = "incident",
PageInfo = new PagingInfo() { ReturnTotalRecordCount = true },
ColumnSet = new ColumnSet("incidentid", "ticketnumber"),
Criteria = new FilterExpression
{
Conditions =
{
new ConditionExpression
{
AttributeName = "ticketnumber",
Operator = ConditionOperator.Equal,
Values = {caseNumber}
}
}
}
};
tracingService.Trace("Running Query");
EntityCollection incidents = service.RetrieveMultiple(query);
tracingService.Trace("fetching total count for incidents with ticket number: {0}", caseNumber);
if (incidents.TotalRecordCount == 1)
{
incident = incidents.Entities.First();
}
else
{
tracingService.Trace(String.Format("{0} incidents found with ticketnumber",incidents.TotalRecordCount.ToString()));
throw new InvalidPluginExecutionException("Duplicate ticket number error");
}
EntityReference caseReference = new EntityReference("incident",incident.Id);
return caseReference;
}
Does this method work to turn on tracing within Microsoft Dynamics CRM 2016?
https://support.microsoft.
I am not seeing any of those registry keys in the registry, should I just create them?
Will this tracing give me more information on the SQL or Fetch XML that is being run against the server?
https://support.microsoft.
I am not seeing any of those registry keys in the registry, should I just create them?
Will this tracing give me more information on the SQL or Fetch XML that is being run against the server?
Hi pbu-ts,
Tracing will give you SQL and not the Fetch (at least in this case). I have one more suggestion, why don't you use FetchXml to run this query?
Use http://www.xrmtoolbox.com/
And one more thing, are you sure the user (In whose context the plugin is running has permissions to retrieve this data? The biggest disadvantage w.r.t. Dynamics CRM is if you try to query data bypassing its filtered views, you will end up bypassing security restrictions applied by Security Roles.
Regards,
Chinmay.
Tracing will give you SQL and not the Fetch (at least in this case). I have one more suggestion, why don't you use FetchXml to run this query?
Use http://www.xrmtoolbox.com/
And one more thing, are you sure the user (In whose context the plugin is running has permissions to retrieve this data? The biggest disadvantage w.r.t. Dynamics CRM is if you try to query data bypassing its filtered views, you will end up bypassing security restrictions applied by Security Roles.
Regards,
Chinmay.
Principal Consultant
CERTIFIED EXPERT
A couple of things, is your SQL code literally what you ran? I ask because the string you test for (000003) is not a valid case number in CRM.
Another thing to point out is that querying the base tables bypasses security, whereas your code will be running in the context of a CRM user so could it be that the user account under which the code is being tested doesn't have rights to query cases? A bit of a long shot, but I thought I'd check.
With regard to the registry values, you should add them if they are not there. Another approach might be to use the SQL Profile to record the queries hitting the CRM database. You'll need to use some filtering otherwise you'll be swamped with data.
Another thing to point out is that querying the base tables bypasses security, whereas your code will be running in the context of a CRM user so could it be that the user account under which the code is being tested doesn't have rights to query cases? A bit of a long shot, but I thought I'd check.
With regard to the registry values, you should add them if they are not there. Another approach might be to use the SQL Profile to record the queries hitting the CRM database. You'll need to use some filtering otherwise you'll be swamped with data.
First, I enabled tracing on the server and so far I am looking at the trace logs in C:\Program Files\Microsoft Dynamics CRM\Trace and it is not showing me any SQL or much more then what I see in the GUI tracelog. I am just getting the exception I throw wrote in line 33. Am I looking in the right place for trace logs? Any thoughts on how to see more?
Second, the SQL I gave is the literal SQL. I have another pre-create plugin that creates custom incident numbers that are a incrementing 6 digit integer.
Third, I changed the user to run under a user with full access and there was no change.
As to the code I can see that it runs in the built in trace logs in the console(new plugin trace logs in 2016). It processes all the way through line 32 and throws the exception in line 33. I even see the record count in the trace log as 0 from line 32 and the ticket number as 000003 from line 25.
Any thoughts?
Second, the SQL I gave is the literal SQL. I have another pre-create plugin that creates custom incident numbers that are a incrementing 6 digit integer.
Third, I changed the user to run under a user with full access and there was no change.
As to the code I can see that it runs in the built in trace logs in the console(new plugin trace logs in 2016). It processes all the way through line 32 and throws the exception in line 33. I even see the record count in the trace log as 0 from line 32 and the ticket number as 000003 from line 25.
Any thoughts?
So per the suggestion I tried to turn on on premise trace using the powershell on https://technet.microsoft.com/en-us/library/hh699694.aspx
I have never used powershell so forgive my ignorance. I ran the following commands and got an error.
Add-PSSnapin Microsoft.Crm.PowerShell
Get-CRMSetting TraceSettings
"Get-CRMSetting : The underlying connection was closed: An unexpected error ocurred on a send."
Microsoft talks about needed to register the powershell commandlets in the article. Do I have to register the XRM Toolkit as discussed here to be able to do anything with CRM using powershell?
I have never used powershell so forgive my ignorance. I ran the following commands and got an error.
Add-PSSnapin Microsoft.Crm.PowerShell
Get-CRMSetting TraceSettings
"Get-CRMSetting : The underlying connection was closed: An unexpected error ocurred on a send."
Microsoft talks about needed to register the powershell commandlets in the article. Do I have to register the XRM Toolkit as discussed here to be able to do anything with CRM using powershell?
Hi pbu-ts,
XRMTooling is not required for your scenario. Try : https://technet.microsoft.
What happens when you just import the snap-in? Do you get any error then?
Regards,
Chinmay.
XRMTooling is not required for your scenario. Try : https://technet.microsoft.
What happens when you just import the snap-in? Do you get any error then?
Regards,
Chinmay.
Chinmay,
Good to know. I do not get an error when I run "Add-PSSnapin Microsoft.Crm.PowerShell" In fact if I run a command like "Get-Help *Crm*" before adding the snappin it comes back blank but when I add the snapping and rerun the command. I get a list of Crm related commandlets so it seems like it is successfully adding the snappin. Do I need to set up any sort of connection or anything before running the powershell commandlets?
Good to know. I do not get an error when I run "Add-PSSnapin Microsoft.Crm.PowerShell" In fact if I run a command like "Get-Help *Crm*" before adding the snappin it comes back blank but when I add the snapping and rerun the command. I get a list of Crm related commandlets so it seems like it is successfully adding the snappin. Do I need to set up any sort of connection or anything before running the powershell commandlets?