Filtering list by currrent user not working

[Borja]

Hi,

I want to filter a linked sharepoint list to only show items created by current user. I read other posts about this but none of these solutions works for me.

I have this filter on my gallery:

SortByColumns(Filter('Time Report'; User().Email = Author.Email); "Title"; If(SortDescending1; Descending; Ascending));

But the list does not show items. Author is a person type? How can i refer to the list item creator?

Thanks.

[Meneghino]

Hi @Borja

I just tested your code and it works for me. I see nothing wrong with it (except an extra ; at the end)

A couple of points:

1) I think this filter cannot be delegated so you will be limited to querying the first 500 records of Time Report.  If you have more records, there are work-arounds

2) I am not sure if this has been fixed yet, but the User() function gets called every time it is needed, the value is not cached in memory.  This means that performance is slow as it looks up the function for every row of your data source!  A work-around is to create a text box (let's call it TextBoxMyEmail) with Text = User().Email.  You can make this text box invisible if you like.  Then use the following:

SortByColumns(Filter('Time Report'; Author.Email = TextBoxMyEmail.Text); "Title"; If(SortDescending1; Descending; Ascending))

3) I assume that SortDescending1 exists

Please let me know how you get on

[Borja]

Hi @Meneghino

Thanks for your reply,

1) I only have 3 records of two different users on my list to test the app. 

I created two records with my user (administrator), but when i test the app with my user the list is empty.  Is there a posibility that in my app Author not exists and i have to refer to the listitem creator with other name?

2) Thanks for the work-around. Good to know this!

3) SortDescending1 is created by default in my app. i didnt change it.

Thanks!

[Meneghino]

You may know this already, but if not, try this trick.

Create a button and set its OnSelect property to:

ClearCollect(LocalTimeReport; 'Time Report')

Then run the app in play mode, press the button to load the collection to memory and go back to edit mode.

Then click the Content tab and then Collections, you should see something like the screenshot below.

Then you can click any table symbol in the Author column and you can inspect the contents of that column to see if all is as you would expect:

• the Author column exists and is called 'Author' and not something else
• an Email column exists for the Author record and is not called something else
• correct author email is indicated and it matches what is returned by the User().Email function

Please let me know if this helps.

[Borja]

Hi

Thanks!

I check this and everything is fine. Author, Author.Email...

But app is not showing items... i'm lost...

Maybe is related to semicolons? I saw other examples with commas. But my app creates the function by deafult with semicolons.

[Borja · Accepted solution]

I changed the semicolons and its working! I think maybe the problem is related to regional settings.

Thank you very much for your support!

[pattysicks]

Hi, I am having the issue where Author.Email is giving me a blue line stating: "cannot be evaluated remotely due to service limitations... if possible simplify the formula". The list is over 500, but we are only interested in viewing the top few anyways. I've searched the forums and others have got this working, but I am still stuck.

Any suggestions?

[Meneghino]

Hi, yes, cache the list in memory with a ClearCollect action.

I have posted many times about this, but here it is:

Hi all.

The most efficient way I have found so far to cache all of a data source into PowerApps is the following:

ClearCollect(CachedInstruments, Sort('[Access].[Instruments]', ID, Ascending));
UpdateContext({MaxID: Max(CachedInstruments, ID)});
If(CountRows(CachedInstruments)=500,
	Collect(CachedInstruments, Filter(Sort('[Access].[Instruments]', ID, Ascending), ID > MaxID))
;
UpdateContext({MaxID: Max(CachedInstruments, ID)});
If(CountRows(CachedInstruments)=1000,
	Collect(CachedInstruments, Filter(Sort('[Access].[Instruments]', ID, Ascending), ID > MaxID))
;
UpdateContext({MaxID: Max(CachedInstruments, ID)});
If(CountRows(CachedInstruments)=1500,
	Collect(CachedInstruments, Filter(Sort('[Access].[Instruments]', ID, Ascending), ID > MaxID))
)))

Please note that the If statements are nested, so that it only advances if more records are present, if not then it stops.  It does not matter how many loops you copy-paste since perfomance will not be affected.

Normally I have the landing page just welcome the user and have the above code in a "Proceed" button.  You can show progress to the user with a text box next to the button to show CountRows(CachedDataSource) so at least the user know that something is going on.

I have used the above for up to 5,000 records and it was quite fast.  I also have a 20,000 row table, but normally I load the piece I need (using the above code but filtering the data source for the portion I need with input from the user once he selects the group for which he wants analysis)

[Meneghino]

Hi all.

The most efficient way I have found so far to cache all of a data source into PowerApps is the following:

ClearCollect(CachedInstruments, Sort('[Access].[Instruments]', ID, Ascending));
UpdateContext({MaxID: Max(CachedInstruments, ID)});
If(CountRows(CachedInstruments)=500,
	Collect(CachedInstruments, Filter(Sort('[Access].[Instruments]', ID, Ascending), ID > MaxID))
;
UpdateContext({MaxID: Max(CachedInstruments, ID)});
If(CountRows(CachedInstruments)=1000,
	Collect(CachedInstruments, Filter(Sort('[Access].[Instruments]', ID, Ascending), ID > MaxID))
;
UpdateContext({MaxID: Max(CachedInstruments, ID)});
If(CountRows(CachedInstruments)=1500,
	Collect(CachedInstruments, Filter(Sort('[Access].[Instruments]', ID, Ascending), ID > MaxID))
)))

Please note that the If statements are nested, so that it only advances if more records are present, if not then it stops.  It does not matter how many loops you copy-paste since perfomance will not be affected.

Normally I have the landing page just welcome the user and have the above code in a "Proceed" button.  You can show progress to the user with a text box next to the button to show CountRows(CachedDataSource) so at least the user know that something is going on.

I have used the above for up to 5,000 records and it was quite fast.  I also have a 20,000 row table, but normally I load the piece I need (using the above code but filtering the data source for the portion I need with input from the user once he selects the group for which he wants analysis)

[Meneghino]

PS If you only need the top few, then just do one caching of the first 500 lines

[pattysicks]

Thanks. Do I put the code on the OnSelect for a button and then just add Navigation to the BrowseScreen?

Also, not sure what is needed in my case based on your example for: '[Access].[Instruments]' 

What I need my BrowseScreen items to do is: SortByColumns(Filter('KM Log',Employee.Email=User().Email),"Date",If(SortDescending1,Descending,Ascending))

Apologize for my lack of knowledge on this. I have a multiple lists that would be useful PowerApps and to be able to filter by user (when list is large) is holding us back.