cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Issue with filter function and sharepoint list

I have sharePoint List name: Capex 

Columns: Created By, Modified By, Approved By,...

Variable: Set(_myProfile, Office365Users.MyProfile())

I want to filter out items only created by a specific user when the Search box is blank.

 

This is what I have:

If(IsBlank(BoxTextSearch.Text), Filter(Capex, _myProfile.DisplayName in 'Created By'))

 

The result is blank.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

I was able to make a quick app attached to a SharePoint list and got the results I think you are looking for. In the items for the gallery I modified it to:

If(!IsBlank(TextSearchBox1), SortByColumns(Filter(test1, StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending)),Filter(test1,Author.DisplayName = myprofile.DisplayName))

I was not able to use created by or modified by. Instead it is Author and Editor. I will point out shows a delegation warning, The filter part of this formula might not work correctly on large data sets.

View solution in original post

4 REPLIES 4
Super User II
Super User II

Hi @Casper_Nguyễn,

If I understand correctly, you wish to filter your data source so that the only items shown are those not created by the current user. If this is incorrect, please let me know. Your question implies that you have a Sharepoint list as one data source and an Office365Users as a second datasource. If this is incorrect, let me know.  Also, you do not specify how you wish to display your results, I am assuming a Datatable control or a Gallery.  Also, your If statement doesn't include an else statement to show what to do if the condition is false.  Assuming a Gallery, the Items property of the gallery should be

If(IsBlank(BoxTextSearch.Text), Filter(Capex, Author.DisplayName<>User().FullName),else).  Where the else is another filter or just the unfiltered data source.

The formula will show a delegation error but this can be ignored if you have less that 500 authors (2000 if you set the Advanced Settings of the app to the Max).  

  See https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-user for more information on the User() function. 

 

 

Frequent Visitor

I was able to make a quick app attached to a SharePoint list and got the results I think you are looking for. In the items for the gallery I modified it to:

If(!IsBlank(TextSearchBox1), SortByColumns(Filter(test1, StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending)),Filter(test1,Author.DisplayName = myprofile.DisplayName))

I was not able to use created by or modified by. Instead it is Author and Editor. I will point out shows a delegation warning, The filter part of this formula might not work correctly on large data sets.

View solution in original post

Thanks for your answer. The function to compare is Capex,Author.DisplayName = _myProfile.DisplayName

 

Set(_userPayment,Filter(Capex,Author.DisplayName = _myProfile.DisplayName))

 

My final is:

SortByColumns(If(IsBlank(BoxTextSearch.Text), _userPayment, Filter(_userPayment, Or(StartsWith(Status.Value, BoxTextSearch.Text),StartsWith(Number, BoxTextSearch.Text)))),"Number",If(SortDescending1, Descending, Ascending))

 

This works perfectly with refresh and sort alphabetical (and reverse).

 

Next: Manager who login to the app can see all DirectReport employee's items. If you have any idea on this, please help ! Please note: _myProfile currently is the Manager profile because we have the same app for both manager and employee.

Hi @Drrickryp

 

You can read my response to @MrSchu02 to understand more. About the datasource, Office365Users is used to verify  the identity while signing to the app. Main datasource is the Sharepoint List.

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Check out the Power Apps Community Call for January 2021

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (9,097)