cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidstone
Kudo Kingpin
Kudo Kingpin

Efficiently query one column from SharePoint List with many columns and circa 350 rows

 

I have a flat list of 35 'services', but since each service has 10 'applications', there are 350 rows of unique applications but repeated service names.

 

In my app I just want to query those 35 services and display them in a dropdown.

 

The logical solution would be to use Distinct(), however it's not delegable and therefore I'm still having to load all 350 rows. Since the list contains a large number of other columns I also don't need, the load time is pretty awful.

 

First I tried loading them into a collection on startup, using ShowColumns() and Distinct(),  but that just brings the delay to the loading of the app instead. My users will come in and out of the app a few times a day, rather than leave the app running, so a long load time on startup isn't a good experience, even though it's performant once loaded.

 

Currently I've got the dropdown Items property pointed to "Sort(Distinct(Systems, SystemName), Result)", which loads the data reasonably quickly, but it's still not ideal. I've also changed the layout of my form so that the dropdown is no longer the first field, meaning as a user you might be occupied with a few preceding fields while the dropdown data is still loading.

 

So again, little workarounds and tweaks can optimise the experience, but I'd much rather optimise the loading of the data instead.

 

Best case scenario, we could query from a SharePoint view (to get just the column(s) we need), and Distinct() would be delegable. But since we don't have either of these options, can anyone identify any further improvements to the solution I've come to so far?

 

Thanks.

6 REPLIES 6
BitLord69
Memorable Member
Memorable Member

Can you break out the services into a separate table/list and use as a Lookup? That way you would only have to load the 35 services, and then you can easily load the applications based on the selected service. 

 

Downside to this, more work if the services change a lot.

An unfortunate (and fortunate) factor is that the list is outside of my control.

 

Until recently there was no master list of services and applications, and so this new list is now a shared 'one truth' administered by a number of different individuals and departments.

 

I considered binding the Services column as a lookup to a shorter list of services, but I think this would affect the way in which some of the stakeholders query the data. Additionally we don't have the most SharePoint-literate of users, and I might find myself in a support role if I change it in this way!

Can you at least create another, separate list? If you can do that, you can still put the services into that separate list, but keep the master list as it is. You then load your list of services to the list box to select the service to Filter on.

 

Is there a lot of change regarding the services, especially new services being added? If there is, maybe you could use a Flow to copy the data into your excerpt whenever there is a new service added.

That's a possibility, and the services shouldn't change too frequently. For a couple of reasons I'd rather avoid it. For one, they've just been through a big exercise to rationalise multiple lists of services into this one single location. Further, ideally I want to 'walk away' once this is done, rather than have users come to me saying things like, "I'm trying to add an application but I can't find the service".

I see where you're coming from and can understand that. Unfortunately I'm drawing a blank now, I haven't really used Sharepoint so don't know so much about it.

 

I noticed you'd posted in the delegation of distinct-thread, and they have changed the status to Under Review, so hopefully it won't be a problem for you once they implement that feature.

Many thanks for trying!

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (3,126)