Invokeing Multiple Gallery Item Filters concurrently or a suggestion on alternative

[JimF0]

Hi all,

I'm looking for some wisdom, whilst the app i've created works and does exactly what i want it to do It has a nasty load time (30 -50secs between first load or changing months) on the one page i need to use the most.

So the page is part of a Rota app, the page has a gallery for each team member (15) and shows a 30 day view for each, this was a requirement so a manager can see a whole moth and everyone was doing.

I have considered a static view but at the same time the manager needs to be able to add/ modify cells (this is achieved by clicking on any square and a form is unhidden to make changes and re-submit).

What I was to try and workaround is the data load time, currently it appears when it loads first time or on changing the month it resolves each gallery Item fitler one after the other. Is there a way to make it resolve them concurrently or a tidier way achieve the filtering?

Item code been resolved on each galley (filtering each gallery to display the record for an individual colleague for that date: 

Filter(Rota,Date = DateAdd(_firstDayInView, ThisItem.Value, Days), ColleagueName = DropdownName1.SelectedText.Value)

Any suggestions to resolve or hints/tips on a redesign if you believe there may be a nicer way to get to the result!

Thanks

[Drrickryp]

HI @JimF0 

I suspect that one of the main reasons for the delay is that you are having to write back to the datasource to get the name of the colleague.  If you could cache that table as a collection when the app opens and then use that collection in your app it should run much faster.  

[JimF0]

Thanks for the feedback.

I've not used collections yet and would appriciate a little guidance as non of the tutorials i can find seem to answer to the use of them in a filter.

I've added: Collect(ColleagueNames, Colleagues) to the OnVisable of the first screen as I believe that should capture the list and allow its use within any other screen.

On my Rota screen the filter function I cant get to use the collection, I woudl have though it would be just a case of replacing the original name item beening called with the colleaction name, but this dosnt return any values and the = operator is invalid. What step am i missing in the use of the colleaction in a filter?

Filter(Rota,Date = DateAdd(_firstDayInView, ThisItem.Value, Days), ColleagueNames = Dropdown7_29.SelectedText.Value)

[Drrickryp]

@JimF0 

I may have misunderstood.  Does your Rota list have a Single line of Text column called Colleages?  Also how many Items are in your Rota list?

[JimF0]

Apologies, The table Rota has the colleague

name column ( single line text) and date column amongst other detail, effectivly the gallery filter is looking for both to being back the record for that date.

The table currently has around 2600 records for all colleagues up to the end of the year.

[Drrickryp · Accepted solution]

@JimF0 

If it is unlikely that you will go over 4000 records, then you can create a combined collection as follow in the OnVisible property of the screen containing your gallery.

Concurrent(            //Collects the Rota list ascending and descending simultaneously
    Collect(
        colA,
        Sort(
            Rota,
            ID,
            Ascending
        )
    ),
    Collect(
        colD,
        Sort(
            Rota,
            ID,
            Descending
        )
    )
);
Collect(                         //Combines the two collections (ascending and descendint)
    colRota,                     // into colRota and eliminates duplicates.
    colA,
    Filter(
        colD,
        !ID in colA.ID
    )
);
Clear(colA);                       //Clears the two collections 
Clear(colD)

then use colRota where ever you are currently using Rota. To test if it works put a label on the screen and make its text property 

CountRows(colRota)

and it should give  you an accurate count of the number of items in the rota list.  In addition, you will be able to use Search() on the list to look up colleagues names.  Once more than 4000 items are in the list, this formula will no longer be accurate.  There should be a major performance improvement if you use the collection colRota instead of the list "Rota" in your app. 

[JimF0]

Thanks for the code! I can see what its doing with the concurrent and collection (tho I cant even start to explain why collecting it both ways then filtering it does what it does!), updated my Gallery to use the colRota. But the list has 2897 items currently but the page seems to only want to pull some of the information 1000 records, which is what my non-delegatable value is set too.

On upping it to 2000 it returns more information, but I cant go above that value so the 4000 seems out of reach

I'm going to keep playing! But initial loading its significantly quicker for the 5 colleagues it is able to find.

[JimF0]

Update:

If I remove the Filter in the collect for colRota: 

!ID in colA.ID

And just collect the colA and colD the returned value is now 4000 (which i expect includes a few duplicates but with it in i only seem to get 1/2 the results)

[Drrickryp]

Hi @JimF0 

There is a mistake in my formula.  It should be

ClearCollect(                         //Combines the two collections (ascending and descendint)
    colRota,                     // into colRota and eliminates duplicates.
    colA,
    Filter(
        colD,
        !ID in colA.ID

 not simply Collect( .  Without the Clear part the collection will keep increasing every time the screen is made visible. 

[JimF0]

Thanks for the update, I had just added a clear command to the onselect of the home button when i noticed the increment, but clear collect is much better!

For some reason the collection is still only picking up the first 2000 records, anything after that that would be part of colD is not been merged into the collection with the filter part.

ClearCollect( colRota, colA, Filter( colD, !ID in colA.ID));

Only way to include all records is to just go with:

ClearCollect(colRota, colA, colD));