Response delay from Sharepoint List causes wrong items to be displayed

[LeoGS]

Dear community,

I just moved my static from Excel imported tables to a dynamic SharePoint List.

Now I am experiencing a behaviour that unpleases me extremely. 

I will give the examples using Football.

Let's call TABLE_A (dynamic) and TABLE_B (dynamic). TABLE_B depends on the values of TABLE_A.

TABLE_A

Row | Country | PrimaryKey

1 | Brazil | br

2 | Spain | es

3 | Italy | it

TABLE_B

Row | Association | FK_association_country

1 | Corinthians | br

2 | Palmeiras | br

3 | Flamengo | br

4 | Real Madrid | es

5 | Barcelona | es

6 | Juventus | it

7 | AC Milan | it

8 | Inter Milan | it

I display each table as a Gallery in a separated screen. 

What I see:

If I click "Spain" in the first screen, the app will go to the second, delay a bit (because the data is being fetched from the SharePoint List) and then Shows "Real Madrid" and "Barcelona". So far so good.

Now, if I go back to the first screen and select "Italy", the app goes to the second screen and shows AT LEAST FOR A LITTLE WHILE "Real Madrid" and "Barcelona", instead of showing directly "Juventus", "AC Milan" and "Inter Milan". After a while, the correct items are shown. But this delay bothers me a lot. Sometimes this delay is so HUGE, let's say, 4-5 seconds, that an user would be able to click for example "Juventus" to be redirected to the third screen using "SPAIN" and "JUVENTUS" as variables, what is TOTALLY IMPLAUSIBLE.

How do I overcome this behaviour? I have tried "RESET" but it doesn't clean up the items of the Gallery, so no success. Does anybody has any idea?

Thanks!

[AJ_Z]

Just wondering the following questions:
How many rows in the sharepoint list?

what is the filter on the second gallery?
Are you passing the selected item from the first gallery as a variable to the seconf gallery?

[LeoGS]

Thanks for the response @AJ_Z :

How many rows in the sharepoint list?

First list: 12 (until now)

Second list: 22 (until now)

What is the filter on the second gallery?

With(
    { _lookUp: LookUp(dbo_BusinessUnits, BusinessUnit = varSelectedBusinessUnit.BusinessUnit, PrimaryKey) },
    Filter( dbo_BusinessUnitAreas, FK_area_businessUnit = _lookUp )
)

Are you passing the selected item from the first gallery as a variable to the seconf Gallery?

Oh yes, definitely! 

Set(varSelectedBusinessUnit, Gallery_businessUnits.Selected);
Navigate(SelectAreaScreen,ScreenTransition.UnCover);

As you see, it is a VERY SMALL list. It will escalate terribly once both lists grow.

[AJ_Z]

How is the delay when you change the second gallery filter to: - so there is no extra with and lookup?
Filter( dbo_BusinessUnitAreas, FK_area_businessUnit = varSelectedBusinessUnit.PrimaryKey)

[RandyHayes · Accepted solution]

@LeoGS 

You will always experience this type of delay and then update because the formula in the Gallery is still evaluating.

There are times to address this and times that you don't care (i.e. the delay is not a problem).

IF it is a problem, then you need to have the Items of the Gallery populated BEFORE you navigate to the screen.

Keeping in mind that there has to be a point where data is loaded...you've got to take the hit somewhere.

Many times I opt for taking the hit on the initial load - but this is highly dependent on the amounts of data you're dealing with.

So, here is approach one - taking the hit on the initial gallery load (first Gallery).

The Items property would be set to (using your sample data):

AddColumns(
   TABLE_A,
   "_data", Filter(TABLE_B, FK_association_country = PrimaryKey)
)

Then on the second Gallery, your Items property would be:

Gallery1.Selected._data

Now you will see NO delay.

Approach two is to utilize a variable and take the hit on selection of the gallery.

The Items property of Gallery 1 would be:

TABLE_A

The OnSelect of Gallery1 would be:

Set(glbRecords, Filter(TABLE_B, FK_association_country=ThisItem.PrimaryKey))

And the Items property of Gallery2 would be:

glbRecords

And again...no delay.

I hope this is helpful for you.

[AJ_Z]

I really like this suggestion from @RandyHayes 🙂

[RandyHayes]

@AJ_Z 

I jumped in on this as it was referenced by @LeoGS in another post I was on.

Yes...the solution provided removes the delay, but, as with many things in PowerApps, you always have to balance data loads against design.

[AJ_Z]

It's always good to have input from others in threads/posts and I like where you suggested the necessary delay could be instead as it seems more user friendly. Great suggestion as usual 😊 @RandyHayes 

[LeoGS]

@RandyHayes, here too, I huge "thank you". This is exactly what I was looking for.

Although I find the suggestions of @AJ_Z good (having tried them myself), it would not fix the problem in its essence but rather only mitigate them.

I implemented the solution of @RandyHayes and decided that Gallery2 would take the hit. I also built a loading spinner and put it on front of the first gallery with a transparent rectangle and the spinner itself, to tell the user that something is going on in the background and also to avoid the user to click the same or other options of the first gallery. Now I have EXACTLY how I imagined it to work.

Thank you people!