Answered

Get items returning more than 1 item

(0) Share

Report

Posted on by lui

178

I'm running to an issue where I'm running a Get items action for a sharepoint list and just want it to return one item.
Top Count = 1

Pagination = 20,000

However, I keep getting a return of two items. It seems like top count doesn't work. Also, how can I just get the first item to return? I believe there's an expression I can use in the apply to each.

Objective:

I'm trying to capture the ID for the first item with a variable.

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

ScottShearer 25,290 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@lui

I just ran a quick test and Top count dies work for me as designed.

Try turning pagination off. I don't believe that it should impact your results.

Was this reply helpful? Yes No
lui 178 on at

Like (0)

Report
Copy link

Link copied!

I need to since I have more than 5000 items in the SP list. Is it possible to add expression on the Apply to each step to get/select the first item?

Was this reply helpful? Yes No
ScottShearer 25,290 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@lui

The 5000 limit refers to the fact that SharePoint will return a maximum of 5000 items. You are filtering your results and limiting to 1 on the server side. Therefore, pagination should not be necessary (you are returning a single item).

@Pstork1 would you care to chime in?

Was this reply helpful? Yes No
lui 178 on at

Like (0)

Report
Copy link

Link copied!

@ScottShearer doesn't pagination allow us to grab more than 5000 times before the filter?

The reason I began to use pagination because this sharepoint list (consists of 10000+ items) was not returning any items. Let's say I need two items to return: ID 100 and ID 10000. Without Pagination, the get items wouldnt return both regardless of the filter. Is this not correct?

Was this reply helpful? Yes No
Verified answer

Pstork1 69,121 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

So here is what I think is going on. Turning on Pagination will divide up the records into paged groups and apply your OData filter to each group. That's why you are getting 2 items returned. They are the top item from each page.

But when using an OData query you don't need to worry about pagination unless you are returning more records than the max record count. This is because the query is actually evaluated on the server. So in your case the query will never return more than one record, but it will return up to one record for each page of results.

I ran a test on a large list I have with 5,101 records in it and it worked fine only returning one record even with pagination turned off.

Was this reply helpful? Yes No

Tono_Analyst2

125 on at

Like (0)

Report

Copy link

Link copied!

I have also observed the same behaviour as @lui.

I have a test SharePoint list with exactly 20,000 records. I have created a column called Color which for testing purposes has exactly 7 records with the value Grey at the following IDs

ID
1
6001
19801
19811
19819
19976
20000

I have tested multiple configurations of the Get Items action to test the behaviour, see outcome below:

Filter Query	Order By	Top Count	Pagination	Limit	IDs Returned
Color eq 'Grey'	ID asc	Blank	Off	N/a	ID: [1]
Color eq 'Grey'	ID desc	Blank	Off	N/a	ID: [20000; 19976; 19819; 19811; 19801]
Color eq 'Grey'	ID desc	2	Off	N/a	ID: [20000; 19976]
Color eq 'Grey'	ID asc	1	On	1	ID: [1]
Color eq 'Grey'	ID asc	Blank	On	100	All 7
Color eq 'Grey'	ID asc	1	On	100	100 records with duplicates?
Color eq 'Grey'	ID asc	3	On	100	All 7
Color eq 'Grey'	ID asc	Blank	On	1000	All 7
Color eq 'Grey'	ID asc	3	On	1000	All 7
Color eq 'Grey'	ID asc	Blank	On	10000	All 7
Color eq 'Grey'	ID asc	1	On	10000	Flow Run timeout
Color eq 'Grey'	ID asc	3	On	10000	All 7
Color eq 'Grey'	ID asc	5	On	10000	8 records with a duplicate? [19801 duplicated]
Color eq 'Grey'	ID asc	3	On	20000	All 7

From the above, it seems that:

Without enabling pagination, you are only able to access the 5000 items prior to filtering and then the output be based on whatever is filtered from these 5000 items.
Without enabling pagination, the top count will work as expected.
With pagination enabled and no top count applied, it seems you can retrieve all of the expected data regardless of the pagination threshold limit.
With pagination enabled the top count seems to be ignored and sometimes even exhibits strange behaviour of causing duplicates.

I do not understand how these settings should be configured and their limit.

Another approach is to create an indexed column which appears to solve the issue without requiring pagination at all. However, the limit of this approach is not clear to me. With 20,000 records it is working, but what happens if there were 1,000,000 records will this still work?

Creating an indexed column seems to be the only approach that allows for the top count to work when dealing with 5000+ records

Filter Query	Order By	Top Count	Pagination	Limit	Column Index	IDs Returned
Color eq 'Grey'	ID asc	Blank	Off	N/a	Color indexed	All 7
Color eq 'Grey'	ID asc	3	Off	N/a	Color indexed	ID [1; 6001; 19801]