cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
praneshJ
Helper III
Helper III

How to fetch more than 5000 rows in JSON using fetch xml

Hello Experts,

 

I have a report on one of the web pages, on the report I have provided the selections to end users so that they can select the KPIs and can see the desired data they want to see on the report.

 

On the web page I have ajax call which fetches the data from CDS table using web template and form a JSON and return it in the response.

 

Now when user is selecting the KPIs from the selections as shown below ,

praneshJ_0-1612259002860.png

for the given date range and combination of selections, data in the corresponding CDS table has more than 6300+ rows

but the JSON is returning only 5000 nodes in the response.

anything beyond 5000 nodes are not getting returned, hence the further calculation going wrong.

 

Please advice how to return more than 5000 nodes in the JSON response, any possible workaround for this is really appreciable.

 

Regards,

Pranesh 

 

13 REPLIES 13

Hello @OOlashyn ,

Thank you again for your quick response,

Yes I mean to have 4 distinct groups as you mentioned,

 

My another question - I have a one column in table of Microsoft data verse say 'marks', the data type of the column is choice and values present that column are like 0, 15, 30, 45, 60.

 

I want to sum those values using group by, but due to its datatype is choice I am not able use sum aggregate function in fetch xml, 
Please advice me how to use choice datatype data to calculate sum,

any quick response is appreciable.

 

Regards,

Pranesh 

 

Hi @praneshJ,

There is no possibility to sum choise values directly. What you can do instead is group them by distinct value and count number of occurrences and then in js code just multiply and sum them as you need. 

----------------------------------------------------
If you find this post helpful consider marking it as a solution to help others find it.

View solution in original post

Hello @OOlashyn,

Thanks again for reply,

 

Now I am facing issue at filtering data where in web template when two entities are linked using <link-entity> tag

please refer below code,

{% assign StartDate = request.params.StartDate %} 
{% assign EndDate = request.params.EndDate %}
{% assign IS_AS_val = request.params.IS_AS_val %} 
{% assign SelectedSiteName = request.params.SelectedSiteName %} 


{% fetchxml DaysProgrammingOffered %}
<fetch  version='1.0' output-format='xml-platform' mapping='logical' aggregate="true" distinct='false'>
<entity name='contact'>
     <attribute name='contactid' />
	 <attribute name='fullname' />
        
   <link-entity name='cr8e7_stc_studentactivity' from='cr8e7_contactlookup' to='contactid' alias='StudentActvityandContactLink'>
    <attribute name='cr8e7_activitydate' alias='daysprograming_count' aggregate='count'/> 
    <attribute name='cr8e7_name' groupby='true' alias='StudentName' /> 
    <attribute name='cr8e7_student_id' groupby='true' alias='StudentId' /> 
    <attribute name='cr8e7_inschoolafterschool' groupby='true' alias='IS_AS' /> 
    <attribute name='cr8e7_grade' groupby='true' alias='Grade' /> 
    <attribute name='cr8e7_site' groupby='true' alias='Site' /> 
    <attribute name='cr8e7_sitelookup' groupby='true' alias='SiteLookup' /> 
    
		<filter type='and'>
            <condition attribute='cr8e7_activitydate' operator='ge' value='{{StartDate}}' /> 
            <condition attribute='cr8e7_activitydate' operator='le' value='{{EndDate}}' />
            <condition attribute='cr8e7_inschoolafterschool' operator='eq' value='{{IS_AS_val}}' /> 
            <condition attribute="cr8e7_attendance" operator="in" ><value>564950002</value><value>564950001</value></condition>
		</filter> 
		<link-entity name='cr8e7_stc_site' from='cr8e7_stc_siteid' to='cr8e7_sitelookup' alias='DefaultActivityAndSiteLink'>
		  <attribute name='cr8e7_stc_siteid' />
		  <attribute name='cr8e7_name' />

			<filter type='and'>
			  <condition attribute='cr8e7_name' operator='eq' value='{{SelectedSiteName}}' /> 
			</filter>
		 </link-entity>
    </link-entity>
    </entity> 
</fetch>

{% endfetchxml %}

This code is again throwing "Exception has been thrown by target of an invocation".

What is wrong in this I am unable to figure it out.

Please help me to get rid of this error.

Hi @praneshJ ,

I would advise you to validate your code in Fetchxml Builder - that should reduce the number of errors. Regarding your query - I think the problem is that you are using attributes without aggregation. In an aggregated query, you cannot fetch properties on their own only with aggregation.

----------------------------------------------------
If you find this post helpful consider marking it as a solution to help others find it.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Users online (34,764)