Building a Canvas app Gallery and set its source a a SQL Server view, will the Gallery load the items in batches ?

[johnjohn123]

I am working a Power Apps Canvas, which have a lot of SharePoint lists and those SharePoint lists have a lot of relationships between each other, as follow:-

1) Project >> each Project will have zero-To-many TaskList >> each TaskList will  have zero-To-many Task >> each Task will have zero-To-Many Audit-trail >> each Task will have zero-To-many AssignedUsers >> each Task will have a Status (open, active,completed, closed).. and much more to go

now due to this business logic, we find it very difficult (or even impossible) to get a gallery which show for example the following, without getting delegation warnings:-

1) A gallery to list all the Audit-Trails under a Project, with the ability to filter the Audit-Trail using a drop-down that list all the Projects.

So we are planning to do this move:-

1) Instead of using SharePoint as the backend, to use SQL Server tables.

2) Instead of filtering the source for the Gallery inside Power Apps formulas, to write SQL Server views, for example in the above case (to get Audit Trails under a Project) to write the following view:-

CREATE VIEW ProjectTrail AS
	SELECT p.Title, AT.Date, AT.Creator, AT.Description
		FROM Project P, TaskList TL,Task  T ,Audit-trail AT 
		WHERE P.ID = TL.ProjectID
		AND TL.ID = T.TaskListID
                AND AT.TaskID = T.ID
                Where P.Title = *******

But i have these questions on my above approach:-

1) Generally speaking, if we define a view as the source for a Gallery >> then will the gallery loads all the items at once? or it will load them in batches, and as the users scroll down more items will be fetched from the database to the Power Apps? similar to how SharePoint list data will be loaded inside the Galleries..

2) Can i pass the P.Title from Power Apps to the view? as we need the users to be able to filter the above view dynamically by selecting the project title from a dropdown item inside the Power Apps screen.

3) what are the disadvantageous of using Views as the source for the galleries ?

4) Can we rely on views to get any data we want from the database without having to worry about writing formulas inside Power Apps which raise delegation warnings?

[Pstork1]

1) Paging and Delegation are two separate but related issues.  Galleries always use paging to download records 100 records at a time.  But if the items setting has Delegation issues then you will only be able to get the maximum record count for the data source 500-2000 depending on the setting.

2) Views can be filtered the same way that Tables can be filtered.  So if P.Title is a column in the view it can be used to filter that view.  If its a column in the Table the view is based on, but not in the view then it can't be used for filtering.

3) Views are frequently Read Only since they may not contain all the fields that need to be added when editing a record.  That can be one of the major disadvantages.  But since they only retrieve the columns you need and can combine multiple related tables they can provide better performance since less data is transferred.

4) Views can be helpful for that, but may not solve all your delegation issues.

[johnjohn123]

@Pstork1  Thanks a lot for the reply...

1) Yes i know that Delegation and Paging are totally different issues.. but when you say "items setting has Delegation issues", now if i define the filtering inside the View (mainly using Where)>> then i should not care about delegation, as SQL Server does not have any filters that can not work (non-delegable if we use Power App term).. am i correct?

2) ok thanks,, so since the P.Title is an nvarchar(50), so i should be able to filter the projects based on it (or based on the ID) .. without having to worry about any delegation errors.. am i correct?Also in this case i should not to worry about passing the P.Title from power apps to the SQL views.. as i can apply the normal Filter() power apps function to the SQL view? so in this case the SQL view's Result will be filtered using the SQL Views's Where condition + the condition/s inside the Filter() power apps function? am i correct?

3) Ok

4) can you give me a case where i will face a delegation warning on a SQL View?

Thanks

[Pstork1]

1) SQL Views are like Tables.  They are a predefined set of records.  There is no way that I'm aware of to pass a parameter to a SQL view using Power Apps.  So its not quite the same as filtering the records in Power Apps where you can choose the value to filter on.

2) No, the SQL view will return all the records (or up to the data row limit) available in the view and then it will be filtered in Power Apps.  IF you are using a delegable action then it will be filtered on the SQL server side.  But it wouldn't use the SQL where clause directly.  Some of the things you can do with a Where are not delegable, like pattern matching with wild cards.

4) Check the list of delegable actions in the documentation. SQL Server - Connectors | Microsoft Docs . For example Filter() will not work with DateTime data types in SQL

[johnjohn123]

@Pstork1  ok thanks again for your help.. so regarding point-2 , i have these 2 further questions:-

1) now i can use whatever filtering inside the Where clause of the SQL view (even non-delegable actions), then if i want to do further filtering i can do them inside the Power Apps,

2) and if i am filtering inside Power Apps using delegable action (such as comparing ProjectTitle or ProjecyID) ,then the filtering will be done inside the SQL server and the gallery should show all the available items, if the user chose to scroll to the end of the gallery, am i correct?

am i correct?

Thanks

[Pstork1]

1) Yes, but the records in the view will always be static because you define the Where clause when you create the view and there is no way to pass parameters in so it has to be defined as a static query.  Once the records are returned you can do further filtering.  At that point you are subject to the rules of delegation.

2) Yes the selection will be done inside the SQL server if you are using delegable queries.  But the record limit is still the record limit.  A delegable query can look through 5,000 records or more, but it can still only return less than the data row limit.  So you couldn't have a delegable query return 4,000 out of 5,000 records.

[johnjohn123]

@Pstork1  but regarding your second point, now galleries will keep showing data regardless of the record limit, as Galleries are smart enough to get 100 items at a time,,, i have a gallery (i use it for testing), which were able to show 20,000 items inside it when the user keep scrolling... from my SharePoint experience, if you use a delegable action, then the gallery will be happy to show all the records from the data source (i tested this with 20,000 items been shown),, this is not subject to record limit, because those items are not been fetched at once but been fetched in 100 batches.. i already tested this multiple times. so if your view return 20,000 items, and you keep scrolling you will be able to view all those items

[Pstork1]

That is all true because of paging.  But it will only work if the Item formula for the Gallery is fully delegable.

[johnjohn123]

@Pstork1  yes i am talking about filtering a gallery using delegate action, so the gallery can show all the items as you scroll.. there is not record limit... the record limit will happen if you are building for example a collection which filter the data-source using delegate or non-delegable actions.. but gallery bypass the record limit since it uses paging

[Pstork1]

Yes, by using paging in connection with delegation a gallery bypasses the record limit.  But the user experience of scrolling down more than 20 times to get beyond 2,000 records is a terrible user experience.  Apps built with that kind of UI are apps that will never be used by users.  The point when working with large data sets in Power Apps is to let the user select the subset of records they need to work on, not scroll through massive screens of data trying to find what they want.