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

AddColumns Slowing Down Gallery Filter

Hello!

 

I am using AddColumns to my data set based with a lookup to another dataset in order to set colors/filters in a gallery for the first dataset.  This has massively slowed down the functionality of the app and for any filter applied, the app takes multiple minutes to display that filter on the gallery.  I'm looking for any ideas of another way to meet my goal or how to make AddColumns be more efficient?

 

Dataset A:  List of employee information (name, department, employee number, cell phone...etc._)

Dataset B:  List of employee responses to monthly survey - so each employee will have multiple responses within the data set over time but it's the same set of questions each month

 

The gallery is of Dataset A (list of employees) but within the card I want to show a status based on a response from the LATEST response from the employee in Dataset B. 

 

I was able to achieve this by adding a column to Dataset A with a Lookup(Last(Filter(....))) type of formula on dataset B.  I get the calculation I need in the new columns (I checked them by adding a label on the gallery) but when I filter on those additional columns or any other columns in the gallery it takes MINUTES to calculate every time.

 

Any ideas on another way I can achieve this?  Or make the AddColumns function more efficient?

 

Thanks!

5 REPLIES 5
GarethPrisk
Resident Rockstar
Resident Rockstar

What you're describing will perform poorly in most scenarios. Basically it's an N+1 data call. Meaning, each row (N) is performing the operation (+1), which is itself relatively slow.

If you can, I recommend collecting the tables into the app, and then functions like this will be basically instant.

This assumes that your data

  • Doesn't exceed delegation limits (or you are willing to overcome them)
  • Will work with "stale data" - meaning the collection was made at X time, and isn't updated unless you specifically update it
Eelman
Super User
Super User

@AppsBuilder310 

A method I'd use would be to create a CompletedSurveyID each time a person completes the survey and store this in both lists. You'd then have a reference you could easily filter on without having to use Last() or AddColumns().

 

I can help further with this if you are unsure what I mean.

Thanks for the recommendation!  Would you achieve this through power automate?  Like when a survey response is added, power automate updates a column on the master employee list ("Last Survey ID" or something like that) of the SharePoint ID of the survey response?  Or is there a way to do this from SharePoint?

 

Thanks!

Thanks for the recommendation!  Do you recommend I do the AddColumns to the App OnStart menu so it's slow to load but then there's that static data set throughout the app?  And maybe add a button so the user can choose to refresh?

 

Thanks!

 

@AppsBuilder310 

It's a little difficult to answer this because I'm unsure of how your system is setup.

 

Are you using a MS Form for your Survey which then adds data to a SP List? If so, how does the Form know who the person is? Do you use an email address for this? If so, you can use the email address in Power Automate to filter the Master Person List and add a LastSurveyID and also add this to the Survey list. You could do this with a variable that concatenates Person_Date_Time ... or ... use a GUID ... or any other relevant string/int that works for you.

 

If people are completing the MS Form anonymously then I cannot see how you'd identify who they are because you'd be relying on them to either complete their name correctly (they never do, haha) or their email address correctly, which can also fail in my experience.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,492)