How does PowerApps bundle the Call to the SharePoint list?

[JHenAptive]

I inherited an app where the User selects a record from a Gallery connected to a SharePoint list.  The record's ID is being set to a variable.  That variable is then being used in a 100-ish fields where the field Default Property is being set with 

LookUp([SharePointList], varItem.ID = ID, [FieldName])

My question is this:  Does PowerApps make a Call for every LookUp for every field with the code above?  Or is it bundling the Calls somehow so that PowerApps is reaching out the List only 1 time...or a few times....

Does any of that make sense?  Ultimately, the App has some User perceived consistency issues with either saving or displaying data, and I'm trying to investigate this area as a possibility of the problem.

[poweractivate · Accepted solution]

@JHenAptive 
If same LookUp is repeated again then call to data source would be repeated again each time
Recommend to set variable

Set(gloMatchingRecord,LookUp(yourSharePointList,lookup criteria here...));

Make sure to use ShowColumns for any columns not in your criteria, otherwise you won't see the columns, Power Apps uses explicit column selection by default now.

Set(gloMatchingRecord,ShowColumns(LookUp(yourSharePointList,lookup criteria here...),columns to show here);

However, issue is that a global variable must be overwritten each time.
if wanting to modify the Record later in-memory, a Collection, even if usually for multiple Records, may be better here.

ClearCollect(colMatchingRecord,LookUp(yourSharePointList,lookup criteria here...));

Then to Patch to the first Record

Patch(colMatchingRecord,First(colMatchingRecord),{columns to change in memory});

Then to patch to the data source:

Patch(yourSharePointList,LookUp(yourSharePointList,lookup criteria here...),First(colMatchingRecord))

When Patching only one Record to a Data Source, if the ID (primary key) is still present in colMatchingRecord itself, we may leave out the 2nd argument of Patch in this case and use the 2-arg version of Patch to avoid the extra LookUp (however careful. If the ID is missing, then this creates a new record instead):

Patch(yourSharePointList,First(colMatchingRecord))

See if something like this may help to understand it better @JHenAptive 

[LaurensM · Accepted solution]

Hi @JHenAptive,

Each LookUp will result in an API call to SharePoint. Since you already save the record to a variable, I am not sure why a new LookUp is needed to fetch a particular column.

Try to avoid similar LookUps by saving that to a variable when needed (e.g. OnVisible, OnStart...) and display the column via varVariableName.ColumnName

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[JHenAptive]

I agree, a new LookUp isn't necessary.  The app show clear signs of a beginner learning curve, so I feel for the original developer.  

I wanted to double check the API Call backend before I started to swap all of the fields to something like you pointed out with varVariableName.ColumnName...

...because well....the users of the SharePoint list have also named the field names with user friendly titles that contain spaces and special characters....so my field names are all "Odata__x300....." or '__x2000__Column__x2000__Name'....  so each time I want to change the code, I have to look up the field name in the List Settings.  So, it's going to be a slow process.

Thanks for the info.

[poweractivate]

@JHenAptive Nice. Just keep in mind Power Apps uses the Explicit Column Selection setting on by default (and you should leave it on). NOTE: applicable to Dataverse, SQL Server, etc. only as of this writing, NOT applicable to SharePoint List!

That means whenever you do a Filter or LookUp, ONLY those columns are returned in the Record (LookUp) or Table (Filter) result. This is a very good thing. However, suppose you want your ID to be in the Record. If it wasn't in your criteria, it may be missing from the Record (or each of the Table's Records).

This is where using ShowColumns wrapped around the LookUp or Filter may be very powerful.

However, only use ShowColumns to show the specific columns you're really using in the app, or performance may be degraded unnecessarily.
As you rightly pointed out, if the LookUp is being repeated again by the developer, that is an extra data source call. It is hard to optimize it and required lot of attention to detail. Besides this, beware of common pitfalls:

- Same Filter or LookUp repeated again TWICE or more inside If: This should be converted to keep the If inside same Lookup / Filter

- Patch inside ForAll, Collect inside ForAll. Common  pitfalls.

Should instead be Patch outside ForAll, ClearCollect outside ForAll.

And so forth.

[timl]

Just to clarify - the target data source must support Explicit Column Selection for Power Apps to apply this feature. Currently, SharePoint does not support Explicit Column Selection. The 2 main datasources that do support Explicit Column Selection are Dataverse and SQL Server.

[poweractivate]

@JHenAptive  Yes, @timl is correct for SharePoint List. It is still not supported for SharePoint List. It may not be in the future either. It is only for data sources like Dataverse and SQL Server
For example if I have a SharePoint List like this:

And a formula like this

Upon clicking Run OnStart:

The Collection shows all the columns, not just the ones in the LookUp.

In my case I had only Title in the LookUp.

If Explicit Column Selection were working on a SharePoint List, I should only see the Title, not all of the columns.

so you are right @timl , SharePoint List as of this writing, are still not supporting Explicit Column Selection yet, and it may not in the future as well as it is probably only supported for data sources like Dataverse or SQL Server.

So @timl is correct, for your case @JHenAptive you should not worry about the Explicit Column Selection.

However, if you ever are considering to use Dataverse in the future, or any other kind of data source which may support Explicit Column Selection, you should be proactive and potentially be already concerned about writing the formulas as if they were explicitly selecting columns, now, to make moving over to Dataverse later much easier.

You can also simply turn off the Explicit Column Selection feature and use Datavers without it on until you can rewrite all the formulas, but I recommend leaving the setting on.

If you're pretty sure you may stick with SharePoint, then I recommend not worrying about it.

However, you may get performance concerns when you have a large amount of columns, like maybe hundreds of them for example in a SharePoint List, this is because ShrePoint List, as @timl mentioned, does not currently support Explicit Column Selection. Even in the hundreds of columns I am often OK with using SharePoint List on my end but depending how many Records, how many Lists you are using and how many columns each list has, how you are using the formulas, etc. you may start getting issues at the higher end of number of columns.

You may have to break into separate SharePoint Lists that have fewer columns when this happens to improve performance.

If it gets too unwieldy to manage you may want to look at Dataverse, or moving some of it over to Dataverse and keeping the SharePoint List for others (Canvas Apps support using multiple data sources at the same time).,  making sure to keep the Explicit Column Selection setting on to best leverage Dataverse.

However, SharePoint List is very powerful, so I may be able to continue using SharePoint Lists, even when some others may already consider to move to Dataverse.

However, whenever the concern first arises to use Dataverse, it's often best to act on that initial hunch, as in the long run it may be easier to manage in Dataverse, and the extra cost of Dataverse is usually less than the cost of the troubles one may encounter when not acting on that initial hunch.

I am probably biased towards keeping SharePoint Lists, even when it may actually be better to move to Dataverse, as often there is a lot of desire to avoid Dataverse and avoid the extra licensing cost.

However I have found in most cases, I would say the extra cost of Dataverse is actually much less than the cost of the extra maintenance overhead, and having to use advanced techniques, when keeping SharePoint Lists. So when people tend to try and save money not using Dataverse, they may be paying more in the long run (and even in the short run) by working so hard to keep the SharePoint Lists.

Nonetheless, SharePoint Lists are so powerful, that I may keep them well before moving to Dataverse, and I recommend you start with SharePoint Lists first.