cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerTomas
Level: Powered On

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
Microsoft v-siky-msft
Microsoft

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
PowerTomas
Level: Powered On

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

Microsoft v-siky-msft
Microsoft

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

 

PowerTomas
Level: Powered On

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.

 

 

Microsoft v-siky-msft
Microsoft

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
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,944)