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 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @praneshJ,

You don't need and cannot write C# code in the web template. Just see what fetchxml query they are performing (see attached image). Also, you can use FetchXmlBuilder from XrmToolBox to generate and test fetchxml queries with aggregation. For example, you want to count the number of entity 2 with distinct names 

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

{% fetchxml feed %}
  <fetch version='1.0' output-format='xml-platform' mapping='logical' aggregate="true" >
  <entity name='entity name'>
   <link-entity name='Entity name2' from='abc' to='Col1' alias='test_alias'>
	<attribute name='name' alias='count_name' aggregate='countcolumn' distinct='true'/>
        <filter type='and'>
		<condition attribute='cr8e7_activitydate' operator='ge' value='{{StartDate}}' /> 
		<condition attribute='cr8e7_activitydate' operator='le' value='{{EndDate}}' />   
		</filter>
	</link-entity>
  </entity>
</fetch>
{% endfetchxml %}   
{
  
    "stddata": [
    
    {% for item in feed.results.entities %} 
      {       
        "Number_Of_Distinct_Names": "{{ item['test_alias.count_name']}}"  
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
  }

 

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

View solution in original post

Hi @praneshJ ,

Your code is throwing an error because you are trying to access the field by its name and not an alias. When you performing grouping you need to use the alias, so instead item.cr8e7_attendance you need to write item.count_attendance. 

Below you can find an example of grouping by year. Each result will represent a year and corresponding number of attendance.

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

{% fetchxml feed %}
 
<fetch version='1.0' output-format='xml-platform' mapping='logical' aggregate="true" >
<entity name='cr8e7_stc_studentactivity'>
          <attribute name='cr8e7_attendance' alias='count_attendance' aggregate='countcolumn' />
          <attribute name='cr8e7_activitydate' groupby='true' dategrouping='year' alias='year' />
          
      <filter type='and'>
            <condition attribute='cr8e7_activitydate' operator='ge' value='{{StartDate}}' /> 
            <condition attribute='cr8e7_activitydate' operator='le' value='{{EndDate}}' /> 
 </filter>
    
    </entity> 
</fetch>
{% endfetchxml %}   
{
    "testdata": [ 
     {% for item in feed.results.entities %}
      {
        "no_of_attendance": "{{ item.count_attendance}}",
        "year": "{{ item.year}}",
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
  }

Can you elaborate a bit would do you mean grouping by data over 4 columns? You want to have 4 distinct groups?

 

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

View solution in original post

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

13 REPLIES 13
justinburch
Microsoft
Microsoft

Hi @praneshJ,

You'll need to utilize the Paging Cookie. Colin Vermander has a great breakdown here: Dynamics 365 portal: Use liquid fetchxml with paging cookie – Colin Vermander (wordpress.com)

Hello @justinburch,

 

Thanks for your reply, the article given is helpful for displaying the data on the web-page,
But I want to perform calculations on the data and display summarised data on the web page.
so I want to return more than 5000 rows in JSON response,

what are the best possible ways to retrieve the more than 5000 rows from CDS tables.

 

Regards,

Pranesh 

 

OliverRodrigues
Super User
Super User

hi @praneshJ I have the feeling you might need to reach out to Microsoft via Support Ticket for this one. 




If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Power Apps Portals Super User


Oliver Rodrigues


 

Hi @praneshJ,

You would setup your JSON retrieval to take advantage of the paging cookie and iterate through the results.

  1. Use your API to query for the results, return that there's a paging cookie in your JSON response
  2. Store the results and, through the API, query for the next pageset
  3. In the same code block, after successful retrieval, do calculations

Note that needing to also do calculations would put effort client-side that might not be ideal and these iterative queries will most likely impact user experience. You might want to shift the server-side - currently, the way to do this securely would be to create a Web App that can validate the Oauth token against your portal. You might be able to leverage Azure Functions and Logic Apps to do the same, but I haven't personally seen anything around this. Either way, you'd have this app do the queries and calculations and return the results.

Hi @praneshJ,

Checkout reply above from @justinburch if you have some extensive calculation. If you want to do the simpler calculation (like sum, count, average) you can benefit from fetchxml aggregation (the system will aggregate results for you and return calculated values). Just create a web template to return aggregated results. Note that fetchxml aggregation has a performance limit of 50k records.

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

Hello @OOlashyn ,

 

Thank you for your reply, I have gone through given fetchxml aggregation link and found that there are certain aggregation functions but the code given for the reference is in C# language.

My question is How can I write the C# code in my web Template? where I am using the logic to fetch the records from the entity, please refer my web template syntax, 

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


{% fetchxml feed %}
  <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
  <entity name='entity name'>
     <attribute name='Col1' />
	 <attribute name='Col2' />
	 
   <link-entity name='Entity name2' from='abc' to='Col1' alias='test_alias'>
	<attribute name='marks' />
	<attribute name='Stdate' />
	<attribute name='enddate' />
	<attribute name='stdId' />
	<attribute name='name' />
		
		<filter type='and'>
		<condition attribute='cr8e7_activitydate' operator='ge' value='{{StartDate}}' /> 
		<condition attribute='cr8e7_activitydate' operator='le' value='{{EndDate}}' />   
		</filter>
	</link-entity>
  </entity>
</fetch>
{% endfetchxml %}   
{
  
    "stddata": [
    
    {% for item in feed.results.entities %} 
      {       
        "Id": "{{ item['test_alias.id']}}",
		"Name": "{{ item['test_alias.name']}}"       
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
  }
  

 Thanks for you time, I really appreciate your help.

 

Regards,

Pranesh 

Hi @praneshJ,

You don't need and cannot write C# code in the web template. Just see what fetchxml query they are performing (see attached image). Also, you can use FetchXmlBuilder from XrmToolBox to generate and test fetchxml queries with aggregation. For example, you want to count the number of entity 2 with distinct names 

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

{% fetchxml feed %}
  <fetch version='1.0' output-format='xml-platform' mapping='logical' aggregate="true" >
  <entity name='entity name'>
   <link-entity name='Entity name2' from='abc' to='Col1' alias='test_alias'>
	<attribute name='name' alias='count_name' aggregate='countcolumn' distinct='true'/>
        <filter type='and'>
		<condition attribute='cr8e7_activitydate' operator='ge' value='{{StartDate}}' /> 
		<condition attribute='cr8e7_activitydate' operator='le' value='{{EndDate}}' />   
		</filter>
	</link-entity>
  </entity>
</fetch>
{% endfetchxml %}   
{
  
    "stddata": [
    
    {% for item in feed.results.entities %} 
      {       
        "Number_Of_Distinct_Names": "{{ item['test_alias.count_name']}}"  
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
  }

 

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

View solution in original post

Hello @OOlashyn ,

 

Thank you for your reply,

Its working for me, I am but confused about group by in fetchxml, I have written below code and its throwing an error "Exception has been thrown by target invocation"

what went wrong in my code I am not getting, please refer code,

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

{% fetchxml feed %}
 
<fetch version='1.0' output-format='xml-platform' mapping='logical' aggregate="true" >
<entity name='cr8e7_stc_studentactivity'>
          <attribute name='cr8e7_attendance' alias='count_attendance' aggregate='countcolumn' />
          <attribute name='cr8e7_activitydate' alias='activityDate' groupby='true' />
          
      <filter type='and'>
            <condition attribute='cr8e7_activitydate' operator='ge' value='{{StartDate}}' /> 
            <condition attribute='cr8e7_activitydate' operator='le' value='{{EndDate}}' />
		  </filter>
    
    </entity> 
</fetch>
{% endfetchxml %}   
{
    "testdata": [ 
     {% for item in feed.results.entities %}
      {
        "no_of_attendance": "{{ item.cr8e7_attendance }}"
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
  }
  

 and Please provide me one example, where I can show group by data over 4 columns and return in json.

 

I really appreciate your time.

 

Regards,

Pranesh

 

Hi @praneshJ ,

Your code is throwing an error because you are trying to access the field by its name and not an alias. When you performing grouping you need to use the alias, so instead item.cr8e7_attendance you need to write item.count_attendance. 

Below you can find an example of grouping by year. Each result will represent a year and corresponding number of attendance.

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

{% fetchxml feed %}
 
<fetch version='1.0' output-format='xml-platform' mapping='logical' aggregate="true" >
<entity name='cr8e7_stc_studentactivity'>
          <attribute name='cr8e7_attendance' alias='count_attendance' aggregate='countcolumn' />
          <attribute name='cr8e7_activitydate' groupby='true' dategrouping='year' alias='year' />
          
      <filter type='and'>
            <condition attribute='cr8e7_activitydate' operator='ge' value='{{StartDate}}' /> 
            <condition attribute='cr8e7_activitydate' operator='le' value='{{EndDate}}' /> 
 </filter>
    
    </entity> 
</fetch>
{% endfetchxml %}   
{
    "testdata": [ 
     {% for item in feed.results.entities %}
      {
        "no_of_attendance": "{{ item.count_attendance}}",
        "year": "{{ item.year}}",
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
  }

Can you elaborate a bit would do you mean grouping by data over 4 columns? You want to have 4 distinct groups?

 

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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (1,980)