cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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>

<html>

<head>

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

<style>

table, th, td {

  border: 1px solid black;

  border-collapse: collapse;

}

th, td {

  padding: 6px;

}

h3{

    padding: 0px;

    margin: 0px;

    font-size 8px;

}

</style>

</head>

<body>

"&Concat

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

Concat(

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

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

</body>

</html>

"

 

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

Tomas

5 REPLIES 5
Highlighted
Community Support
Community Support

Re: Create a report from powerapps with two sharepointlist sources

@PowerTomas 

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)

Snipaste_2020-01-15_15-15-13.pngSnipaste_2020-01-15_15-15-23.png

Hope this helps.

Sik

Highlighted
Frequent Visitor

Re: Create a report from powerapps with two sharepointlist sources

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

Tomas

Highlighted
Community Support
Community Support

Re: Create a report from powerapps with two sharepointlist sources

@PowerTomas 

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))

 

Sik

 

Highlighted
Frequent Visitor

Re: Create a report from powerapps with two sharepointlist sources

@v-siky-msft 

Here is my code for filtering in collections. 

If(Toggle2.Value,
ClearCollect
(SubjectCollect,
Filter(Subject,
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.

 

 

Highlighted
Community Support
Community Support

Re: Create a report from powerapps with two sharepointlist sources

@PowerTomas 

Have you tried this?

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

2. Modify your formula as below:

 

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

 

Sik

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (6,964)