Solved: Re: Collection from selected columns of SharePoint...

Anonymous · ‎01-11-2019

Hello all. We've got a collection that is pulling from a SharePoint list and it works great. However, we were looking to limit the memory and increasing the speed so we were thinking we could limit the amount of data in the app by just grabbing the few columns we needed from the list. If we do it with Collect() we can define columns and we put in two of them but only the first populates. Is this even possible or am I wasting my time for a small performance increase?

Drrickryp · ‎01-12-2019

Hi @Anonymous,

A collection by definition exists in memory on the local machine. Once a collection is created, performance should be almost instantaneous so there won't be much of a difference depending on the number of columns. That being said, there are sometimes other reasons for limiting the number of columns in a collection. This is done by shaping the data before creating the collection using the AddColumns, DropColumns, RenameColumns and ShowColumns functions. These are typically used inside of the Collect or ClearCollect() function. If you are working with collections, these functions are very important to know about. An example doing what you are requesting would be

ClearCollect(colCustomers, ShowColumns(Customers, "LastName", "Email")

to only show a few columns from what could be a list with a lot of columns. Please note that the column names must be enclosed in double quotes. Also please be aware that these functions are not delegatable, so if your list is >2000 items, you will run into problems with them when you create the collection unless you filter the list down to <2000 items.

The inverse function DropColumns() is used to eliminate columns from the data before making the collection. None of these functions actually changes the underlying data in the list in any way.

For a complete reference see function-table-shaping.

View solution in original post

Drrickryp · ‎01-12-2019

Hi @Anonymous,

A collection by definition exists in memory on the local machine. Once a collection is created, performance should be almost instantaneous so there won't be much of a difference depending on the number of columns. That being said, there are sometimes other reasons for limiting the number of columns in a collection. This is done by shaping the data before creating the collection using the AddColumns, DropColumns, RenameColumns and ShowColumns functions. These are typically used inside of the Collect or ClearCollect() function. If you are working with collections, these functions are very important to know about. An example doing what you are requesting would be

ClearCollect(colCustomers, ShowColumns(Customers, "LastName", "Email")

to only show a few columns from what could be a list with a lot of columns. Please note that the column names must be enclosed in double quotes. Also please be aware that these functions are not delegatable, so if your list is >2000 items, you will run into problems with them when you create the collection unless you filter the list down to <2000 items.

The inverse function DropColumns() is used to eliminate columns from the data before making the collection. None of these functions actually changes the underlying data in the list in any way.

For a complete reference see function-table-shaping.

Anonymous · ‎01-13-2019

Thanks @Drrickryp, based on that it looks like we would always retrieve the full SharePoint list (say 10 columns) and then we can trim it down and leave just the 2 we want to use for the app. The savings would the be on the app memory size for the remainder of the app session.

We've got an app built that is a bit of a kiosk so you would start it up, make some choices (which fills this collection) and then that collection is used for the remainder of the day but nobody ever goes back to that setup screen execept on initial load. We were looking at ways to shave time, reduce memory etc so retrieving just the columns from the list made sense in thinking it would reduce the overall amount of data being pulled and stored on start. We'll experiment with dropping the unnecessary columns and see if it reduces our memory footprint and we can call that a win for the apps session life vs for the first time it fires up.