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

If statement inside Filter people type

Filter(If(Delegated = Blank(),'Application Manager'.Email = User().Email, Delegated.Email = User().Email))

 

I have two columns in SharePoint List Application Manager and Delegated, both are type people.

What I am trying to do is if there is a value in the Delegated column then show delegated user else show Application Manager column user value. I have tried the above filer, but it gives me a delegation warning which I don't want. Is there any alternate solution? 

 

 

7 REPLIES 7
RandyHayes
Super User
Super User

@yashp1196 

You will not be able to delegate that logic to SharePoint. 

However, you really just want to get records where one condition is met or the other.

You can consider this instead:

With({_usr: User()},
    Filter(yourDataSource, 'Application Manager'.Email=_usr.Email || Delegated.Email = _usr.Email)
)

Note, the User() function was separated out only because it drags on performance.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
yashp1196
Frequent Visitor

@RandyHayes thank you for responding. 

I am aware of the logic you gave me but in my PowerApp I have written a logic where an application manager can delegate to someone else. If an Application Manager is delegated to another user, then I want to remove the items from the application manager and show it to delegated person. 

RandyHayes
Super User
Super User

@yashp1196 

So the formula I provided would be delegable.  At that point you can perform non-delegable operations on the results.

As an example.

With({_items:
    With({_usr: User()},
        Filter(yourDataSource, 'Application Manager'.Email=_usr.Email || Delegated.Email = _usr.Email)
    )
    },
    Filter(_items, !IsBlank(Delegated.Email))
)

Normally The !IsBlank is not delegable, but with the above, it doesn't make a difference as the _items contains the table of results that have been delegated already.

 

But your original post made reference to "Showing" columns.  This would be logic in your app, not in the filter statement.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
yashp1196
Frequent Visitor

Below logic is working for me, if there is a better approach please do let me know.

 

If(
IsBlank(LookUp(NUA_online_Test_repository, Delegated.Email = Blank())), Filter(
NUA_online_Test_repository,

Status = "Documented" || Status = "Awaiting Reply",
'Internal Use' = "Application Specific" || 'Internal Use' = Blank(),
Retain = "Retain" || Retain = Blank(),
'Documented on'< DateAdd(Today(),-730,Days),
'Application Manager'.Email = User().Email
) ,

Filter(
NUA_online_Test_repository,

Status = "Documented" || Status = "Awaiting Reply",
'Internal Use' = "Application Specific" || 'Internal Use' = Blank(),
Retain = "Retain" || Retain = Blank(),
'Documented on'< DateAdd(Today(),-730,Days),
Delegated.Email = User().Email
)
)

RandyHayes
Super User
Super User

@yashp1196 

Your formula is not making sense as you are basing this on a LookUp.  The LookUp is going to return the first record where the criteria is true. So, if ANY record in your datasource has the Delegated person set to blank, it will only ever use the first filter statement.

What is your logic there?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

I totally forgot that LoopUp will return the first record, thx for reminding me. 

The following things that I want to do:

- I have two columns: Application Manager and Delegated. And both are people type in SharePoint.

- I want to filter per User, so

 - Check if there is a value in the Delegated column, if yes filter per that user else Application manager. 

EX

    Application Manager                          Delegated                               

1.    Yash Patel                                                                                              

2.    Ward Gass                                          Yash Patel

3.    Ward Gass

-----------------------------------------------

  The result I want (When a user opens the app)

If I am Yash Patel I will be able to see: 1 and 2

If I am Ward Gass I will be able to see: 3

 

RandyHayes
Super User
Super User

@yashp1196 

Yes, the example is what made it clear...and it is what I provided in this formula before with a slight modification:

With({_usr: User()},
    With({_items:
        Filter(yourDataSource, 'Application Manager'.Email=_usr.Email || Delegated.Email = _usr.Email)        
    },
        Filter(_items, Delegated.Email=_usr.Email || (IsBlank(Delegated.Email) && 'Application Manager'.Email = _usr.Email))
    )
)

In this formula it will delegate the filter to find all records where the current user is in either column.

Then, your priority is on the Delegated column...so the first part of the criteria is to return true (include record) if the current user is in Delegated.  Otherwise, Delegated needs to be blank and Application Manager needs to be the current user to be true (included).

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (5,117)