Issue with filter function and sharepoint list

[Casper_Nguyễn]

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.

[Drrickryp]

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. 

[MrSchu02 · Accepted solution]

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.

[Casper_Nguyễn]

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.

[Casper_Nguyễn]

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.