Length Function for Overall Row Count on a Dataverse Table

Like (0) Share

Report

Posted on 8 Apr 2023 12:35:27 by ctedesco3307

635

Hey all - I'm trying to do a really simple flow to just count the number of records in a Dataverse table - all I'm doing is this

length(outputs('List_rows')?['body/value'])

Problem is it gives me the wrong number -I get 5000 and I know for a fact there are 15000 in the table. What am I missing?

Thx

All responses (6)

Answers (1)

grantjenkins 11,059 Moderator on 12 Apr 2023 at 13:01:00

Like (1)

Report

Re: Length Function for Overall Row Count on a Dataverse Table

@abm Agreed - using the Rest API is probably a better solution here to get the item count. I'd probably do some testing though as using ItemCount in PowerShell sometimes doesn't give the exact count as it's cached. Not sure if the same in Power Automate and the Rest API.
abm abm 32,506 Most Valuable Professional on 11 Apr 2023 at 11:38:56

Like (0)

Report

Re: Length Function for Overall Row Count on a Dataverse Table

Hi @grantjenkins

Thanks for your reply. My understanding is if you have large data sets to process then setting the pagination always helps. Batch by batch you can deal with the data rather than one is whole go in terms of memory and other resources.

If I want to use the total count for the records I would use the REST API way which I explained earlier in my post.

Thanks again.
grantjenkins 11,059 Moderator on 11 Apr 2023 at 05:37:31

Like (1)

Report
Re: Length Function for Overall Row Count on a Dataverse Table

@abm I didn't quite say the correct thing in my previous post. List rows (Dataverse) and Get items (SharePoint) both retrieve the first 100 items by default (if left blank). You can increase this up to 5000 using Top Count.

If you then turn on Pagination, it will start to return your items in batches of what you've set for Top Count, up to the Threshold you specified. Some examples below.

Example 1:

Settings:

Top Count = blank

Pagination = Off

Result:

You will get up to 100 items returned.

Example 2:

Settings:

Top Count = 5000

Pagination = Off

Result:

You will get up to 5000 items returned.

In order to get more than 5000 items you need to turn on Pagination so it will retrieve the items in batches up to the Threshold you set. This is why it's stated that the maximum is 5000 (5000 per batch).

Example 3:

Settings:

Top Count = blank

Pagination = On

Threshold = 15000

Result:

You will get up to 15000 items returned in batches of 100.

If you had a total of 12000 items that would be returned, then this would go back to your SharePoint list 120 times (120 x 100 = 12000).

Example 4:

Settings:

Top Count = 5000

Pagination = On

Threshold = 15000

Result:

You will get up to 15000 items returned in batches of 5000.

If you had a total of 12000 items that would be returned, then this would go back to your SharePoint list 3 times (3 x 5000 = 15000 which is more than 12000). Much quicker than just leaving the Top Count blank.

Note 1: If you're potentially going to receive more than 5000 items, you should set your Top Count to 5000 (max batch size) and turn on Pagination.

Note 2: List rows present in a table (Excel) has a default of 256 items returned. Same conditions as above apply here too, except 256 if Top Count is blank instead of 100.
abm abm 32,506 Most Valuable Professional on 10 Apr 2023 at 16:37:28

Like (0)

Report

Re: Length Function for Overall Row Count on a Dataverse Table

Hi @ctedesco3307

Thanks for answering @grantjenkins

Interesting question and I tested this with 6000 records. Get Items action step with no threshold returned me 100 records. I set the pagination to 10,000 and it returned me 6000 records as records count.

In-depth analysis into 'Get items' and 'Get files' SharePoint actions for flows in Power Automate | Microsoft Learn

The above documentation says 5000 is the limit for the filter query. I did tested with a filter query Id greater than 0 and it did returned me 6000 with the threshold settings to 10,000. Without threshold it did returned 100.

A bit confused about 5000 limit here? Do I need more records to test this @grantjenkins ?

For finding only the total record count I did used the REST API query and it did returned 6000 record count.
ctedesco3307 635 on 08 Apr 2023 at 15:10:53

Like (0)

Report

Re: Length Function for Overall Row Count on a Dataverse Table

@grantjenkins - ugh... so simple!! thank you! perfect
Verified answer

grantjenkins 11,059 Moderator on 08 Apr 2023 at 12:46:30

Like (2)

Report

Re: Length Function for Overall Row Count on a Dataverse Table

By default, List rows will only return the first 5000 items. You'll need to go into the Settings of the action, turn on Pagination and set a Threshold larger than the number of items in your table. This will return the items in batches of 5000 up to the Threshold you have set.