If statement inside Filter people type

(0) Share

Report

Posted on by yashp1196

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
yashp1196 27 on at

Like (0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
yashp1196 27 on at

Like (0)

Report
Copy link

Link copied!

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
)
)

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

@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?

Was this reply helpful? Yes No
yashp1196 27 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@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).

Was this reply helpful? Yes No