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

(0) Share

Report

Posted on by praneshJ

121

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 ,

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

Categories:

Power Apps portals

I have the same question (0)

All responses (13)

Answers (3)

justinburch Microsoft Employee on at

Like (0)

Report

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)

Was this reply helpful? Yes No
praneshJ 121 on at

Like (0)

Report

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

Was this reply helpful? Yes No
oliver.rodrigues 9,368 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
justinburch Microsoft Employee on at

Like (0)

Report
Hi @praneshJ,
You would setup your JSON retrieval to take advantage of the paging cookie and iterate through the results.
Use your API to query for the results, return that there's a paging cookie in your JSON response
Store the results and, through the API, query for the next pageset
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.

Was this reply helpful? Yes No
OOlashyn 3,496 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
praneshJ 121 on at

Like (0)

Report
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

Was this reply helpful? Yes No
Verified answer

OOlashyn 3,496 Most Valuable Professional on at

Like (0)

Report
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 %} ] }

2021-02-08 20_32_...

Was this reply helpful? Yes No
praneshJ 121 on at

Like (0)

Report
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

Was this reply helpful? Yes No
Verified answer

OOlashyn 3,496 Most Valuable Professional on at

Like (0)

Report
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?

Was this reply helpful? Yes No
praneshJ 121 on at

Like (0)

Report

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

Was this reply helpful? Yes No