cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
svandaTIB
Level: Powered On

Problem with filter formula

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Lexnnn
Level: Powered On

Re: Problem with filter formula

This did help me to filter on User = email from people field: (I was using people field and it did not work well with powerapps) (3200+ items)
https://wonderlaura.com/2018/10/26/powerapps-filter-by-me/

Solution in short: use variable "on start" + "OnVisisble" refresh of the list and use same variable in filter +  single line of text field instead of poeple field.

SortByColumns(Filter('Sandwich shop',(Salesrepemail = CurrentUser.Email) And ((StartsWith(Account_x0020_name, TextSearchBox1.Text)) Or (StartsWith(Street, TextSearchBox1.Text)) Or (StartsWith(City, TextSearchBox1.Text))Or (StartsWith(Postal_x0020_Code, TextSearchBox1.Text)))), "Account_x0020_name", If(SortDescending1, Descending, Ascending))

 

To let the users still be able to use the people field, I use flow to auto populate the new single line of text field with a copy of email adress from people field.

View solution in original post

14 REPLIES 14
PowerApps Staff SinanE
PowerApps Staff

Re: Problem with filter formula

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

svandaTIB
Level: Powered On

Re: Problem with filter formula

Hi,

I use connection to businness sharepoint.

 

Thank you.

Highlighted
Meneghino
Level 10

Re: Problem with filter formula

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
Level: Powered On

Re: Problem with filter formula

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
Level 10

Re: Problem with filter formula

hi @svandaTIB

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

svandaTIB
Level: Powered On

Re: Problem with filter formula

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
Level 10

Re: Problem with filter formula

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
Level: Powered On

Re: Problem with filter formula

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
Level 10

Re: Problem with filter formula

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 ;;

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,737)