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.

[svandaTIB]

Thanks for your reply.

Sometimes it works correctly, but on the half of the uses the list shows very weird data. Do you think, that I can find any easier solution if I will be ok only with data from this year - I will use less rows, so the application may will be work better.

Or do you have any other formulas, that I can try?

[Meneghino]

hi @svandaTIB

If you can reduce your data source to 500 rows or less (i.e. only for current year) then you should be ok without special formulas.

[iAm_ManCat]

Hi @svandaTIB,

I see that other below have suggested collections - you should definitely avoid this wherever possible as it will mean every time you refresh the collection it is downloading the entire source data into the collection! We want to have Sharepoint do the work for us before the items are sent to PowerApps.

Your formula references Author.Email (a value within a column) which SharePoint is not able to delegate.

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

A quick solution would be:

1. In SharePoint, create a new Text column, call it AuthorEmailValue.

2. In PowerApps, refresh your data source.

3. Select your gallery, edit the fields to include AuthorEmailValue

4. Set this field to invisible (it is only there for us to search it)

5. Anywhere that your App does Edit/New, add this field, set to invisible, and in the Textbox within that card, set the Default to Text(Author.Email)

6. This will write the value to any new/edits - so you can now go through and edit&save each item in list or copy paste values in the Sharepoint list.

7. Change your Items property to:

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

(You could also fill all the existing columns using a Flow that extracts.Author.Email and sets this as the field in a Text Column)

ManCat

[Lexnnn]

This did help me. (I was using people field and it did not work well with powerapps)
https://wonderlaura.com/2018/10/26/powerapps-filter-by-me/

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.

[Lexnnn · Accepted solution]

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.