Problem with filter formula

[svandaTIB]

Hi,

I have problem with filtering formula. I would like to display in Gallery only records from signed user.

My formula is:

SortByColumns(Filter(Výkazy;Author.Email = User().Email);"Datum";Descending)

Problem is, that this formula shows only part of the records. Specifically, because I have in my table column with Date (Datum), it shows only records with Date before this year.

Thanks for help.

[SinanE]

Hi, could you give some more details on what type of connection (SharePoint, CDM, SQL Server, Excel etc.) are you using here? Thanks!

[svandaTIB]

Hi,

I use connection to businness sharepoint.

Thank you.

[Meneghino]

Hi @svandaTIB

A couple of comments:

1) In order to improve performance it is better to store User().Email in a text box or as a context variable, otherwise the function gets called for every line of the table you are trying to filter

2) If 1 does not solve the issue, then you may be running into delegation related problems.  You will need to experiment to see how to resolve this.

[svandaTIB]

I am a beginner with powerapps - can you give me please more info about the first note or send me some link with more details?

[Meneghino]

hi @svandaTIB

please see this thread, one of my posts contains more details.  please come back if not clear

[svandaTIB]

Thanks for your reply.

Unfortunately, I have not made it work. I have created a TextBox named mail and put inside a text User().Email.

Also I have changed the Items formula (I skipped the sorting formula for better testing):

Filter(Výkazy;Author.Email=mail.Text)

Part of the formula (.Email) is blue underlined.

The problem have been staying same. And the system show blue alert saying: Part of this formula Filter cannot be evalulationed remotely because limitations of service. Local evalulation can make non-optimalize or partial results. If it can be simplifed, we recommend do it. Other info you can find in docs for Filter function. (freely translated)

[Meneghino]

You are doing the right thing.  The blue underlining indicates that delegation is not happening, so we move to the second problem indicated in my original response.  Basically, if the search cannot be delegated to the server, then only the first 500 items in your data source will be queried.  In other words only a subset of these first 500 will be returned.

The reason why delegation does not happen is not always obvious, and in your case it is probably because the Author field does not (yet) support delegation in filter operations.  But some experimentation needs to be done to see what can solve your issue.

You could try to first sort by date descending (which should almost definitely be delegated) and then filter by author, and although this will not be delegated, you may at least be able to fiilter from a more interesting subset of 500 records.  So basiclaly something like this:

Filter(SortByColumns(Výkazy;"Datum";Descending);Author.Email = User().Email)

However, this may not work if it means that delegation is not done at all.

If this does not work, then you should consider cacheing your Vykazy list in a collection and then using the collection as a basis for filtering.  How may rows does vykazy have?  I have a number of blog posts on chacheing to point you to if needed.

You can read here about delegation:

https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/

[svandaTIB]

Thanks for your reply.

I have tried put there your formula, but still I see data only from years 2016 and 2015 (on 2015 the table started). The data are descanding sorted, but 2017 is missing.

The table has about 700 rows.

If you send me some articles about caching I will be grateful.

[Meneghino]

Ok, there is no issue with 700 rows.  What I normally do is to have a welcome screen which includes the user email in a text box (which you use later for the filtering) and a proceed button with the following OnSelect property to cache and navigate to your proper screen:

ClearCollect(CachedVykazy, Sort(Výkazy, ID, Ascending));
UpdateContext({MaxID: Max(CachedVykazy, ID)});
If(CountRows(CachedVykazy)=500,
	Collect(CachedVykazy, Filter(Sort(Výkazy, ID, Ascending), ID > MaxID))
;
UpdateContext({MaxID: Max(CachedVykazy, ID)});
If(CountRows(CachedVykazy)=1000,
	Collect(CachedVykazy, Filter(Sort(Výkazy, ID, Ascending), ID > MaxID))
;
UpdateContext({MaxID: Max(CachedVykazy, ID)});
If(CountRows(CachedVykazy)=1500,
	Collect(CachedVykazy, Filter(Sort(Výkazy, ID, Ascending), ID > MaxID))
)));
Navigate(MyNextScreen, None)

This will collect up to 2000 rows so you will have no problems in the near future

PS ID may need to be Id in the above depeding on your SharePoint list

PPS Because of the locale you will need to substitute most , wtih ; and most ; with ;;