Using flow to collect Sharepoint List (Almost there)

[Mo_Islam]

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,

Mo

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"
        ]
    }
}

[Pstork1]

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/ 

[Mo_Islam]

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.

[Pstork1]

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.

[Paulie78 · Accepted solution]

@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-Automat...

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?

[Pstork1]

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.

[Paulie78]

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

[Mo_Islam]

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

[Paulie78]

Hi @Mo_Islam 

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

[Mo_Islam]

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.

[Paulie78]

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. 

[Mo_Islam]

Thank you for prompt response @Paulie78 

Also, I downloaded your v3, as my list is still under 5000. I edited everything and when i check the flow results it shows the correct data, however in the PowerApps when I check the collection, it only has one item called 'Value' and not the actual table results. Any ideas?