Aggregate on Fetch XML to get the average of decimal field having NULL value

(1) Share

Report

Posted on by ANISH

Hi All,

Doing aggregate on Fetch XML to get the average of decimal field having NULL value throwing error while doing RetrieveMultiple.
I believe it was working before as also mentioned here https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/org-service/sample-use-aggregation-fetchxml

Is anyone else is facing the same in or after CRM version 1710 (9.1.0.4233) online ?

Query

String FetchMembers = @"<fetch mapping='logical' no-lock='true' aggregate='true' output-format='xml-platform' version='1.0'>
<entity name='customentity'>
<attribute name='score' alias='avg_score' aggregate='avg'/> 
<filter type='and'>
<condition attribute='lookup' operator='eq' value='{0}' />
<condition attribute='statecode' operator='eq' value='0' />
</filter>
</entity> </fetch>";

*This post is locked for comments

All responses (14)

Answers (0)

Suggested answer

Dynamics Objects 365 550 on at

Like (0)

Report

RE: Aggregate on Fetch XML to get the average of decimal field having NULL value

Anish, to check if the problem is with FetchXML , you can do this quick check -

Paste this in you web browser --> https://<<YourOrgName>>.api.crm.dynamics.com/api/data/v9.1/<<YourCustomEntityPluralName>>?fetchXml=<<Your Fetch XML>>

Do share the feedback :)

Cheers!!
DigitalFlow 80 on at

Like (1)

Report

RE: Aggregate on Fetch XML to get the average of decimal field having NULL value

ANISH,

I can confirm that we're having the same issues with FetchXML queries in the latest online organizations.

The following fetch throwing "An unexpected error occured" for us:

<fetch no-lock='true' distinct='false' mapping='logical' aggregate='true'>

                            <entity name='orb_demand'>

                                <attribute name='orb_quantity' alias='quantity_sum' aggregate='sum' />

                                <filter type='or'>

                                    <condition attribute='orb_parentdemandid' operator='eq' value='abc640d3-f66f-e911-a97b-000d3a38cba7'/>

                                </filter>

                            </entity>

                        </fetch>

It is only happening on this entity when selecting this specific decimal field and using an additional filter.

When choosing a different field or leaving out the filter, it's not giving an error.

Seems like a bug, as we didn't change anything recently.

Kind Regards,

Florian
gdas 50,085 on at

Like (1)

Report
RE: Aggregate on Fetch XML to get the average of decimal field having NULL value

Hi Anish,

I tried all your code and its working fine for me , can you please share screenshot what error you are getting . See here I have tried with below code in a console and its working for me . I would suggest to do the same and let us know .

String FetchMembers = @"<fetch distinct='false' mapping='logical' no-lock='true' aggregate='true' output-format='xml-platform' version='1.0'> <entity name='rib_school'> <attribute name='new_defimalfield' alias='new_defimalfield' aggregate='avg'/> <attribute name='new_decimalfield2' alias='new_decimalfield2' aggregate='avg'/> <filter type='and'> <condition attribute='rib_accountlookupid' operator='eq' value='{0}' /> <condition attribute='statecode' operator='eq' value='0' /> <condition attribute='rib_name' operator='not-null' /> </filter> </entity> </fetch>"; string lookupid = "A16B3F4B-1BE7-E611-8101-E0071B6AF231"; EntityCollection OppMem = organizationService.RetrieveMultiple(new FetchExpression(String.Format(FetchMembers, new Guid(lookupid)))); EntityCollection corpStatusCollection = new EntityCollection();

Doing average of any attributes should not give any error if the attributes set to null . I have intentionally does not put any value in the "new_decimalfield2" field and I am getting expected result .see below screenshot .

"
ANISH 95 on at

Like (0)

Report

RE: Aggregate on Fetch XML to get the average of decimal field having NULL value

Hi Sreevalli,

1. I am passing valid GUID, for example I mentioned empty guid. Sorry

2. If we put check for field2, it will stop throwing error but does not solve the issue. With your option, we need to put null checking for all the fields which is being used for [avg].

For example, suppose one record have data for Field1 but does not have data for Field2, and and vice versa for another record. In this case neither it will consider record1 nor it will consider record2.

Apologize, if I am missing something here.

Thanks,

Anish
Suggested answer

Sreevalli 3,256 on at

Like (0)

Report

RE: Aggregate on Fetch XML to get the average of decimal field having NULL value

Hi,

1. Why are you passing empty guid in the condition, you can use Guid.Empty() instead of Guid(0000.....)

2. For me it somehow it is making sense that when it’s trying to calculate average, throwing error if there are any errors in the loop. So I think - if we add null check condition on field2 should solve the issue
ANISH 95 on at

Like (0)

Report
RE: Aggregate on Fetch XML to get the average of decimal field having NULL value

Hi All,

Here is my original code, which is not working where field2 does not have value and field1 has value .

String FetchMembers = @"<fetch distinct='false' mapping='logical' no-lock='true' aggregate='true' output-format='xml-platform' version='1.0'> <entity name='tgp_opportunitymember'> <attribute name='field1' alias='alias_field1' aggregate='avg'/> <attribute name='field2' alias='alias_field2' aggregate='avg'/> <filter type='and'> <condition attribute='tgp_opportunity' operator='eq' value='{0}' /> <condition attribute='statecode' operator='eq' value='0' /> <condition attribute='somefield' operator='not-null' /> </filter> </entity> </fetch>"; EntityCollection OppMem = _service.RetrieveMultiple(new FetchExpression(String.Format(FetchMembers, new Guid("00000000-0000-0000-0000-000000000000"))));

Here is my first step which is working.

String FetchMembers = @"<fetch distinct='false' mapping='logical' no-lock='true' aggregate='true' output-format='xml-platform' version='1.0'> <entity name='tgp_opportunitymember'> <attribute name='field1' alias='alias_field1' aggregate='avg'/> <filter type='and'> <condition attribute='tgp_opportunity' operator='eq' value='{0}' /> <condition attribute='statecode' operator='eq' value='0' /> <condition attribute='somefield' operator='not-null' /> </filter> </entity> </fetch>"; EntityCollection OppMem = _service.RetrieveMultiple(new FetchExpression(String.Format(FetchMembers, new Guid("00000000-0000-0000-0000-000000000000"))));

Here is my second step which is not working.

String FetchMembers = @"<fetch distinct='false' mapping='logical' no-lock='true' aggregate='true' output-format='xml-platform' version='1.0'> <entity name='tgp_opportunitymember'> <attribute name='field2' alias='alias_field2' aggregate='avg'/> <filter type='and'> <condition attribute='tgp_opportunity' operator='eq' value='{0}' /> <condition attribute='statecode' operator='eq' value='0' /> <condition attribute='somefield' operator='not-null' /> </filter> </entity> </fetch>"; EntityCollection OppMem = _service.RetrieveMultiple(new FetchExpression(String.Format(FetchMembers, new Guid("00000000-0000-0000-0000-000000000000"))));

Is it same scenario which is working in your case ?
Suggested answer

RaviKashyap 55,410 on at

Like (1)

Report

RE: Aggregate on Fetch XML to get the average of decimal field having NULL value

Hi,

The error "Out of box" is unlikely to be thrown by the code lines you have shared. It has to come from an object which is not initialized for example when you do "service." etc. You can use the above sample code shared by Goutam and see if this works.

Hope this helps.
Suggested answer

gdas 50,085 on at

Like (1)

Report
RE: Aggregate on Fetch XML to get the average of decimal field having NULL value

Try to write like below , its working for me -

string lookupid = "{A16B3F4B-1BE7-E611-8101-E0071B6AF231}"; string fetchValue = "<fetch mapping = 'logical' no-lock='true' aggregate='true' output-format='xml-platform' version='1.0'>"+ "<entity name = 'new_school' >"+ "<attribute name = 'new_defimalfield' alias = 'new_defimalfield' aggregate = 'avg' /> " + "<filter type = 'and' > " + "<condition attribute = 'new_accountlookupid' operator= 'eq' value = '"+ lookupid + "' /> " + "<condition attribute = 'statecode' operator= 'eq' value = '0' /> " + "</filter > " + " </entity > " + "</fetch > "; EntityCollection resultCollection = new EntityCollection(); resultCollection = organizationService.RetrieveMultiple(new FetchExpression(fetchValue));
Suggested answer

Sreevalli 3,256 on at

Like (0)

Report

RE: Aggregate on Fetch XML to get the average of decimal field having NULL value

Hi Anish,

But I still suspect Condition, Could you remove the condition and try to see if it working?
ANISH 95 on at

Like (0)

Report

RE: Aggregate on Fetch XML to get the average of decimal field having NULL value

Hi,

Yes I am 99% sure because I ran only selected piece of code on each attribute separately and I found that any normal field (Decimal) having null values and then I am trying to put the aggregate on fetchxml for average, it is throwing error.

Meanwhile I handled it through LIST but it is annoyed, why sudden error started ? OR it was there from starting ?

Thanks in advance.