Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create a report from powerapps with two sharepointlist sources

Hi All.


I have a repeating table in a gallery (using Shane Young's brilliant Youtube videos). I have two Sharepoint lists, one called Subject and the other one is called Activities.


One subject can have many activities. The activity that’s belong to the same subject have the same MasterID as the Subjects ID. So that’s how I link them together in the powerapp.


And now I want to build a report with this data like one of Shane youngs other videos (Htmltext to PDF-report). One of the columns in the subjectlist is company. From the powerapp before Im creating the report I want to select which company I want to create the report for. I need to have a concat for all subjects that’s belong to the company. And I need another concat in that concat to show all the activities that’s belong to the subject. Similar like this.


Company 1

Subject 1

Activity 1 (Linked to subject 1)

Activity 2

Subject 2

Activity 1 (Linked to subject 2)

Activity 2

Activity 3


When Im doing my report today Im first doing a ClearCollect and filtering depends on which Company the user Is selecting. And Im getting the Subjects and activities that’s belong to the Company. I could only managed to use the in operator here and its getting me for using “=”.


ClearCollect(SubjectCollection,Filter(Subject, Company = Company.SelectedText.Value);

ClearCollect(ActivityCollection, Filter(Activities, MasterID in SubjectCollection.ID))


When my collections Is filtered Im using a HTMLTEXT that’s concating every subject and activity. and matches them up with the MasterID = ID.


"<!DOCTYPE html>



<meta name='viewport' content='width=device-width, initial-scale=1.0'>


table, th, td {

  border: 1px solid black;

  border-collapse: collapse;


th, td {

  padding: 6px;



    padding: 0px;

    margin: 0px;

    font-size 8px;






 (SubjectCollection, "<h2>"& Team &"</h2> <p><Strong> Description: </strong>"& "<br>" & 'Description’ &"</p><br><br>" &


       Filter(ActivityCollection, MasterID = SubjectCollection[@ID]),

      "<p><h3>Aktivitet</h3>"  & Aktivitet & "<br>"  &"</p><p><strong> Start </strong>" & "<br>" ),"<br><hr>")&"





My problem is that Im having a Delegation warning because Im using “in” operator and a collection that can only show me the first 2000 rows. The customer is going to have more than 2000 activities.

I want to have a similar report but without the delegation warning and no problem to filter records above row 2000. I want to filter all the rows from the  sharepointlist.


Anybody have any suggestions how I need to build it or maybe give me an example?

I guess I cant use the collections anymore because of the delegation problem.


Best regards


Community Support
Community Support


As we all know that the In operator is not delegable in Sharepoint, but SharePoint is a delegable data source.

So the workaround:

1.  Add a gallery to get all SharePoint records,

2. Use Gallery.AllItems which is delegable instead of the SP list

Please check the following screenshot. The left Gallery gets all records from SP list, the middle gallery is applied a filter function with 'In' operator to SP list directly, and the right gallery is also applied a filter function with 'In' operator, the difference is that it filters the LeftGallery.AllItems. We can see the right gallery doesn't have a delegation warning. (Delegation threshold set 5)


Hope this helps.


Not applicable

Thanks for the reply @v-siky-msft !


If Im displaying all the records from the gallery that you have in the left gallery. 

My problem here is that the gallery only shows the first 100 rows when im filtering. 

Do you know a workaround for that? I have to scroll down to get my filtering now.


Best regards



Could you share the detailed steps or screenshots?

You should create the left gallery to show all Activities items, and then use Leftgallery.AllItems instead of Activities in filter formula

ClearCollect(ActivityCollection, Filter(Leftgallery.AllItems, MasterID in SubjectCollection.ID))




Not applicable


Here is my code for filtering in collections. 

Company = CompanyDD_1.SelectedText.Value;
ClearCollect(ActivitiesCollect, Filter(Activities, MasterID in SubjectGallery.AllItems.ID))


Here is my code in powerapps when im filtering the collections.

I have tried to use = as a operator but i cant and i dont know why. 

Is there a function that i have missed that maybe could make the = availavle


In my SP list i have more than 2000 records and when im doing your solutions its only filtering on the first 100 rows. I have to scroll down to filtering all records.


When i tried your solutions the gallery only showed me the first 100 rows. And i had to scroll down to do more filtering.




Have you tried this?

1. Add a galley named ActivitiesGallery, set Items property to Activities

2. Modify your formula as below:


Company = CompanyDD_1.SelectedText.Value;
ClearCollect(ActivitiesCollect, Filter(ActivitiesGallery, MasterID in SubjectCollection.ID))



Helpful resources

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,362)