asked on

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

Open in new window

Feridun Kadir

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

pbu-ts

ASKER

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

SELECT TicketNumber, *
FROM IncidentBase
WHERE TicketNumber = '000003'

Open in new window

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

Open in new window

pbu-ts

ASKER

Does this method work to turn on tracing within Microsoft Dynamics CRM 2016?

https://support.microsoft.com/en-us/help/907490/how-to-enable-tracing-in-microsoft-dynamics-crm

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?

SOLUTION

Chinmay Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chinmay Patel

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 run the Fetch and see if you are getting result there. If you do, then simply use that FetchXML, instead of QueryExpression to get the results in your Plugin as well.

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.

Feridun Kadir

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.

ASKER CERTIFIED SOLUTION

Feridun Kadir

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pbu-ts

ASKER

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?

pbu-ts

ASKER

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?

Chinmay Patel

Hi pbu-ts,

XRMTooling is not required for your scenario. Try : https://technet.microsoft.com/en-us/library/dn531202.aspx

What happens when you just import the snap-in? Do you get any error then?

Regards,
Chinmay.

pbu-ts

ASKER

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?

pbu-ts

ASKER

I found the answer to this question. The case number that is being passed into this function had a space in it. Thank you to everyone that helped out.

Feridun Kadir

Wow, that was simple in the end!
Glad you got there.