community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

SharePoint List Input - All Items in List

Asteroid

Is there a way to get all items in a list without needing to select a view? For instance, we have a list with 20,000+ items but due to the SP view limit we've created numerous filters/views on the frontend. We'd like to do reporting on this list, but how do get everything without going thru a view? Or is there a way to loop thru all views?

Community Operations Manager
Community Operations Manager

@chanmar,

 

You could probably create a bath macro to update the list in the tool and have it pull all of the views. The way the tool is setup you have to go through to the list. This is due to Sharepoints API. You could also look at Sharepoints API documentation and see if there is a way to pull just the lists. you an then use the Alteryx Download tool and create a workflow to pull that data.

 

DanM

Nebula
Nebula

This seems to be the document you need - I've not done this myself, but if you are able to work with your sharepoint admins to give you access then this may be just the solution:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-re...

 

Additionally you can try connecting MS Access to sharepoint (you can point to sharepoint lists as a pass-through table) and then connect Alteryx to Access?

 

 

Nebula
Nebula

may also be worth following this thread and working with your admins to see if they are willing to lift the limit during certain time widows?

https://support.office.com/en-us/article/manage-large-lists-and-libraries-in-sharepoint-b8588dae-938...

 

 

Alteryx Partner

Hello,

 

Not sure if this is still relevant for you, but I just made a REST API call to sharepoint with the Download tool.

 

As input, you past inn your sharepoint folder url and add "/_vti_bin/ListData.svc" at the end. This gives you a list of all sharepoint lsits in that folder.

 

If you add "/NameOfListYouWant" at the end of that again, ir returns every line for that list. 

 

Remember to add your credentials in the Download Tool.

 

You will get the results as xml, so add an xml parse tool after the download tool, and choose spesific Chiled with the value "properties" and tick the box "return child values".

 

Now, someone mentioned that there might be list limitations with the API call. I dont have any lists large enough to test that, BUT you can add query parameters to the url to get first X rows, then second url ucludes a parameter to get next x rows and so on.

 

I found this youtube video usefull.

https://www.youtube.com/watch?v=dvFbVPDQYyk

 

 

 

 

Have you ever tried to build the URL for a REST API service call and ended up confused and frustrated? The OData query syntax is well documented, it's just not that intuitive. Well, if you want to query list data I have a little trick you can use to have the appropriate URL generated for you. The
Asteroid

Thank you for the information! Was just learning about the Download tool at an Alteryx User Group yesterday. I can see this being useful and relevant. I'll check it out!

Alteryx Partner

Good to hear! Let me know if you need any help. I found that using the REST API also is way faster. The out of the box connector takes 30 seconds to read 3 lists, while the REST API takes 2.8 seconds to read 3 lists. 

Asteroid

I've been trying to play with the Download tool, but I seem to get a 403 Forbidden error.

 

We're on Sharepoint 365 -- the URL I tried is:

https://companyname.sharepoint.com/sites/SITENAME/COLLECTIONAME/_api/lists/getbytitle('List%20Name')

Alteryx Partner

Here is the line i pass to the download tool:

https://apps.mycompanyname.com/corporate/Template-Requests/master_lists/_vti_bin/ListData.svc/TestLi...

So there are 3 parts in this string:

1. https://apps.mycompanyname.com/corporate/Template-Requests/master_lists/
This is a folder where many of my lists are stored

 

2./_vti_bin/ListData.svc/
This is added to get list information. If nothing comes after this part, then you get an xml with all lists in the given folder.

3.TestList1
If you add this, then you will get an XML of the list data for a spesific list.

 

And again, you must add credentials in the connection tab of the "download tool".

Labels