Data sources, delegation and refresh

[FredericForest]

Hi,

I'm a bit confused by some of the documentation on data sources, delegation and refresh. Two pages seem to be saying opposite things (to my untraied eyes ^^) and I'd like to get some precisions on that.

From Understand Data Sources for Canvas Apps, I get that :

• Data sources are loaded into tables when the app initialy starts
• Once loaded as a table, we can manipulate them just like an internal table
• These tables can be refreshed with the Refresh() command
• In essence, loading just the data you need in a local collection (which I used to do) is useless because the app loads all your data sources anyway by default and there's no way around that...

From the Understand Delegation in a Canvas App, I get that : 

• Some operations (filter, sum, etc) can be pushed down to the backend system
• Some operations are not delegable and thus will only be performed on a slice of the data

The question that arises is "when is data loaded to PowerApps exactly if the source is delegable" ? Is it both on the App Load event AND when something manipulates the data source with a delegable operation ? Does that mean that whenever I'm using a Filter() on a delegable data source, the operation also includes a Refresh() internally ?

I'm curious about that because I'm trying to avoid the initial on App Load loading of the data source (I'm dealing with a large data set with lots of columns which can't be broken down in 3NF). I'm realising my current methods might have been counter-productive... And well, because I'm generally trying to understand what's going on. ^^

Cheers,

[v-xida-msft]

Hi @FredericForest,

For your first question, no matter which data source (Delegable data source or Non-delegable data source), the data would be loaded into your app when your app is started up.

For your second question, the data would be firstly loaded into your app when your app is started up. Actually, the loaded data (from your data source) could only be cached within your app about 30 seconds in default, after that, the app would fetch data from your data source again and cached within your app.

In other words, your app would fetch data from your data source every 30 seconds and refresh the cache data. The default cache time out is 30 seconds, you could extend the time out to 1 minute via turning on/enabling "Use longer data cache timeout and background refresh" option within Advanced settings of App settings of your app.

For your third question, just as I mentioned above. In default, your app would fetch data from your data source every 30 seconds and refresh the cache data.

Best regards,

Kris

[FredericForest]

@v-xida-msft

Hi Kris,

Thanks! This is both informative... and not at all what I expected ^^.

So let me get this straight : 

• Every data source, delgable or not, is fully querried within the max row limits (500 by default) within the cache timeout delay (every 30 seconds by default).

From your comment, do I understand that there is no imperative cache refresh like calling the data source with filter like in the context of a ClearcCllect?

I really need to put a trace on my backend ^^

[daviddraper2 · Accepted solution]

---

@FredericForest wrote:

@v-xida-msft

Hi Kris,

Thanks! This is both informative... and not at all what I expected ^^.

So let me get this straight : 

• Every data source, delgable or not, is fully querried within the max row limits (500 by default) within the cache timeout delay (every 30 seconds by default).

From your comment, do I understand that there is no imperative cache refresh like calling the data source with filter like in the context of a ClearcCllect?

I really need to put a trace on my backend ^^

---

Yes, there is.  It's called Refresh().

Examplefor Refresh Icon:

Refresh('Data Source');

ClearCollect('Data Source,colDataSource);

SaveData(colDataSource,"colDataSourceDataFile")

[Anonymous]

Hi,

I still don't really understand what is happening if I do something like ClearCollect(colData,Filter(SPListTable, Column1="test")).

If my app can't load more than 500 rows, so it is possible that the item I'm looking for isn't cached right ?

So does PowerApps always go look directly in the SharepointList with delegation (and so the data is up to date, so I don't need a Refresh()) or ... ?

[FredericForest]

Hi @Anonymous ,

I this case, you're working with a local collection called 'colData'. Once loaded, colData won't be linked to your data source. That has the advantage that the call to the source is done only once and therefore it's much quicker to reuse that data since you've got it "in the app".

However, it also means that :

1. You are indeed limited to whatever you have collected (aka : your first 500 rows where Column1 = "test"). You'll need to clearCollect again if you want different data. Personally, I tend to do that with one or more filters in the interface that I know will probably return less than 500, and then I do a countRows() to check if I've hit the max. If that's the case, I show a warning to the user that the data he got might not be complete and may need more specific filters. A more advanced technique is called "paging" where you show the first 500 items of the whole dataset but allow the user to fetch the next 500 ones. Personally I only know how to do that with SQL Server procedures, not on SharePoint lists.
2. When you update the data, you'll need to push the change to the source manually via a Patch(). Forms won't work here since you're working with a local collection. Your option is to do a Patch followed by a clearCollect to get the new version of the data, or patch both the source and the local collection so they stay in sync. There's pros and cons to both methods...

All and all, the Collect-and-Patch pattern is quite powerful, but it tends to be a bit more work for the developper.

Hope this helps!