Filtering a gallery using collections

[CorbnDallas]

Edit: after reading a few similar posts, perhaps this is a "context" issue? Perhaps I need to leverage UpdateContext is some capacity?

We have an application that lets users make requests to work shifts.

In order to improve the performance (users are complaining of slow loading times), I thought I would leverage collections.

We have two lists in the application, Shifts and Requests. I was hoping to be able to make a single call to SharePoint, OnVisible, and load both lists into corresponding collections.

Then, as the user is filtering the data, I would be able to filter the collections, as opposed to making subsequent calls to SharePoint to pull back the filtered requests. 

Here are the two collections I create:

ClearCollect(varListOfRequests, Filter('Shift Manager DEV Requests', Status = "Open"));

ClearCollect(varListOfShifts, Filter('Shift Manager DEV Shifts', Status = "Open"));

On the page, users can filter results based on a number of options and the result of the filter is stored in a variable. The gallery on page has its Items property set to that variable that is created on search (varGalleryList).

Here's an example filter when a user tries to search based on a specific date:

Set(varGalleryList, Filter(varListOfRequests, Status = "Open" And LookUp(varListOfShifts, Date = ddDate.Selected.Result And ID = ShiftID, Date)))

Clicking on the filter button, which in turn makes that query above, is not causing my gallery to display the results. I can inspect the query and see values in both collections correctly (varListOfRequests, varListOfShifts) though.

If I instead replace the collections with direct calls back to the SharePoint lists, the gallery displays accordingly:

Set(varGalleryList, Filter('Shift Manager DEV Requests', Status = "Open" And LookUp('Shift Manager DEV Shifts', Date = ddDate.Selected.Result And ID = ShiftID, Date)))

Am I doing something incorrectly here, or is filtering content via a collection not working as I expected?

[Drrickryp]

Hi @CorbnDallas 

You should be able to view your collections here: They will show the collections by name and the first 5 items retrieved.  If the collection is empty then the formula that creates it must either be incorrect. or result in a null set. 

[eka24]

Looking at your formulas, since both the direct call and the collections are the same, With the OnVisible, did you move to another screen for the OnVisible formula to take effect?

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[CorbnDallas]

Thanks @Drrickryp ,

Yes, I have confirmed the collections are being populated correctly. Even as I inspect the filter on the page in edit more, I can highlight either collection and see they hold all the necessary information.

[CorbnDallas]

Thanks @eka24 ,

Yes, I have made sure to always start my tests from another location in the app, and then load the page in question, to make sure the collections are populated OnVisible.

[Drrickryp]

@CorbnDallas 

If you make the collection the items property of a datatable, does it still show no items?  Please post a screenshot of the Collections page as shown in my second picture.

[CorbnDallas]

No, if I add (for example) just the varListOfRequests collection as the Items value in my gallery, I do get a list of results.

However, any attempt to filter that value yields no results.

Edit: adding a screenshot of one of the test collections @Drrickryp 

[Drrickryp]

@CorbnDallas 

AHA!  I think we are closer to the solution.  The problem is clearly how you are applying the filter.  There must be something wrong with this:  

Set(varGalleryList, Filter('Shift Manager DEV Requests', Status = "Open" And LookUp('Shift Manager DEV Shifts', Date = ddDate.Selected.Result And ID = ShiftID, Date)))

It looks like you are recreating the collection with your filter.  You don't want to do that.  Instead, break down the logic and start by filtering the collection. according to each condition.  Something like

Filter(varGalleryList, SomeColumnInTheList=Dropdown1.Selected.Status) and see if you still have data.  If you do, keep adding conditions to the filter. There should not be any need to recreate the collection.  The working concept is that you are replacing the original dataset with the collection.  Then you can filter it, reshape it, sort it, etc.  Since you are working with a local, virtual version of the original dataset, all of PowerApps functions will be available and the execution of the functions should be lightning fast.

[CorbnDallas]

Thanks @Drrickryp ,

I'm afraid you referenced the code snippet that does work however. 🙂

If in my filter, I specifically call the SharePoint lists ('Shift Manager DEV Requests', 'Shift Manager DEV Shifts'), the gallery does produce results:

Set(varGalleryList, Filter('Shift Manager DEV Requests', Status = "Open" And LookUp('Shift Manager DEV Shifts', Date = ddDate.Selected.Result And ID = ShiftID, Date)))

If however, I call the collections, that's when I am not getting the results. And absolutely, that is my goal to just have a local, virtual version of the large data set, that I can then let my users dice up as needed. 😞

Set(varGalleryList, Filter(varListOfRequests, Status = "Open" And LookUp(varListOfShifts, Date = ddDate.Selected.Result And ID = ShiftID, Date)))

[Drrickryp]

@CorbnDallas 

I guess I'm a bit puzzled.  Do you have much of a delay when you use the original lists in your formula?  As the  items property of the gallery is still a collection, it should run pretty fast.  As long as the elements in the filter are delegatable, they should not slow down the creation of the collection.  Sometimes, the worst enemy of good is better.