Using flow to collect Sharepoint List (Almost there)

(0) Share

Report

Posted on by Mo_Islam

149

Hi,

I have a SP List which has over 4000 items. I found Paul's method of using flow to collect data very useful.

https://www.tachytelic.net/2020/04/many-ways-get-sharepoint-items-power-automate/

His method works very well, however I am having difficulties understanding how to use complex fields which I have in my list. It works for string and integer fields, straight forward but for people picker, choice and lookup I have no idea.

Below in the uri I have so far entered only string and integer, but please can someone help me with the rest.

Thank you,

These are all my fields I need:

--> Also, does anyone know how to generate JSON schema? I need it for the final part of this flow:

{
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "Id": {
 "type": "integer"
 },
 "Title": {
 "type": "string"
 },
 "LessonNo": {
 "type": "integer"
 },
 "EventStart": {
 "type": "string"
 },
 "EventEnd": {
 "type": "string"
 },
 "CourseTeacher": {
 "type": "string"
 },
 "TeacherName": {
 "type": "string"
 },
 "Description": {
 "type": "string"
 },
 "Location": {
 "type": "string"
 }
 },
 "required": [
 "Id",
 "Title",
 "LessonNo",
 "EventStart",
 "EventEnd",
 "CourseTeacher",
 "TeacherName",
 "Description",
 "Location"
 ]
 }
}

Categories:

Building flows

I have the same question (0)

All responses (11)

Answers (1)

Sort by

Pstork1 69,490 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

If you use the Pagination settings available in the Get Items action you don't need to resort to retrieving the results in two halves. You can get the entire set of responses in one action. The flow will automatically retrieve the items in pages until all the items are retrieved. Then you can use a Select action to get only the columns you need. This article explains how to turn on pagination. I've used this for lists that are over 5,000 items without a problem.

https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/

Was this reply helpful? Yes No
Mo_Islam 149 on at

Like (0)

Report
Copy link

Link copied!

Thank you for your advice @Pstork1 Can I ask what is the response time? I need to retrieve the data in no more than 10 seconds at most.

Also, is there any way that I can limit the number of rows pulled based on the date selected?
e.g. If the date on my screen is 20/Nov/2020, I only want to bring data 1 month back and 1 month ahead, therefore from 20 Oct to 20 Dec.

Was this reply helpful? Yes No
Pstork1 69,490 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

If you can't get the response time down to what you want using pagination then there is no other method that will do it faster.

In terms of filtering you can use an OData query to filter the records. Here's a brief article that discusses what an odata query for Get items looks like.

Was this reply helpful? Yes No
Verified answer

Paulie78 8,424 Moderator on at

Like (1)

Report
Copy link

Link copied!

@Pstork1 there absolutely is another method, which is why I wrote the article above (which many people have implemented successfully). That article describes a method in which a query which originally took over two minutes was reduced to under 10 seconds. To collect 17,000 rows of data, and people have used it with many more than that.

Here you can see it running live:
https://www.tachytelic.net/wp-content/uploads/flowSharepointItemsDemo/jQuery-Grid-From-Power-Automate-SharePoint-Data.html
17,000 rows from SharePoint in no time at all.

@Mo_Islam you don't need to worry about generating the schema manually.
Just run the flow then check the run history, get a sample of your compiled data and then use the "Generate from Sample" tool to generate the schema for you, do you know what I mean?

Was this reply helpful? Yes No
Pstork1 69,490 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I understand the other method, but using the pagination settings properly will provide as good or better performance in my experience than the method you are suggesting.

Was this reply helpful? Yes No
Paulie78 8,424 Moderator on at

Like (0)

Report
Copy link

Link copied!

It simply won't, it wouldn't even be close. But ok 😁

Was this reply helpful? Yes No
Mo_Islam 149 on at

Like (0)

Report
Copy link

Link copied!

Thank you @Paulie78
How can I include the complicated field types as part of the uri as described above?
Also, is there a reason why you have 'ID' and 'Id'.

Many thanks

Was this reply helpful? Yes No
Paulie78 8,424 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @Mo_Islam

I am not sure I understand what you mean by complicated field types, can you explain?

Was this reply helpful? Yes No
Mo_Islam 149 on at

Like (0)

Report
Copy link

Link copied!

Hi @Paulie78
Basically, in the part where you select the fields you want under the URI, because I have choice and lookup as well, i get an error. So far only if I choose the string and integer fields, it works but I need to also collect fields of other types.

Was this reply helpful? Yes No
Paulie78 8,424 Moderator on at

Like (0)

Report
Copy link

Link copied!

I don't know how to do that via the API, sorry, I would assume you can still use the internal field name. But I guess you tried that.

Was this reply helpful? Yes No