AddColumns Slowing Down Gallery Filter

[Anonymous]

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!

[GarethPrisk]

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

[Anonymous]

@Anonymous 

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.

[Anonymous]

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!

[Anonymous]

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!

[Anonymous]

@Anonymous 

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.