Answered

filter with multiple criteria

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have this current formula, which works fine.

Sum(Filter('Approved Requests RF',Employee = User().FullName),'Total Number of Hours')&" hours"

I need to change the source to 'Leave Requests RF', change Employee to Employee Name and add in a new criteria of Status = "Approved". I have tried this, but it doesnt work. Any help?

Sum(Filter('Leave Requests RF',Employee Name = User().FullName && Status = "Approved"),'Total Number of Hours')&" hours"

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous,

if Status is a lookup or choice column in SharePoint, then you need to add .Value to it

Sum(Filter('Leave Requests RF',Employee Name = User().FullName && Status.Value = "Approved"),'Total Number of Hours')&" hours"

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for your help @Drrickryp

@Anonymous can you let us know if this solves your issue?

@Anonymous

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, Unfortunately this is still not working.

I did also need to change a column header, and I have done so but it does not work.

This is what I now have, and i get a white cross in a red circle.

Sum(Filter('Leave Requests RF',Employee Name = User().FullName && Status.Value = "Approved"),'Total Number of Hours Requested')&" hours"

If I put apostrophes around Employee Name, it then changes to a yellow triangle.

Apologies for the delay, this is a bit of a side prohect for me, and workload is high at the moment.

Rick

Was this reply helpful? Yes No
panand99 641 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

1) Yellow triangle is coming for delegation warning, you have to select collection instead of direct list name to solve this.
2) User.().FullName returns user ful name in Capital letters, please check your Employee name from the list.
Use of Proper() will solve this type of issues.

Please let us know whether these changes will solve your issue.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

HI,

I have another text box with User().FullName and this doesn't return the result in capitals.

I'm not sure what you mean by selecting the collection. Please can you advise.

Rick

Was this reply helpful? Yes No
panand99 641 on at

Like
a
(1)

Report
Copy link

Link copied!

Collection details : https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-clear-collect-clearcollect

Blue line warning issue is delegate issue: https://community.dynamics.com/365/b/sachinbansalmicrosoft/archive/2018/06/04/powerapps-delegation-with-example-500-records-limit-app-s-performance

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Rick, panand99 is telling you that if you create a collection from your datasource, ie. ClearCollect(leaverequests, 'Leave Requests RF') you can substitute the name of the collection leaverequests where ever you are using the datasource and you will be able to use all functions, not just delegatable ones in your app. You can put the ClearCollect(leaverequests, 'Leave Requests RF') in the OnVisible property of the screen so that the collection will be up to date and available if you make changes in your datasource.

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hey Rickfear,

So with the yellow triangle around the user name (delegation warning) this is because doing username=User().Email uses the User() function and I think the connectors have to process something or other and you get the delegation warning.

Anyhow.

If you store the current user in a variable (probably a global one called gloCurrentUserEmail - Set(gloCurrentUser,User().Eamil) this will store the users email for you for use anywhere in your app. Then simply send the vairable to sharepoint in your filter so Username=gloCurrentUserEmail.

The other factor is having the user email address stored in a text field in sharepoint as the person field has a whole bunch of delegation issues.

Hope this helps.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the advice everyone, but I still don't really understand what I am supposed to be doing.
I think I need to spend a bit of time on it, and at the moment I don't have the time neccessary.
Rick

Was this reply helpful? Yes No
PytByt 943 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous

have you solved your problem?
if you have, what is the solution?
i have similiar problem with yours, and i can really get some help.

Regards,
pytbyt

Was this reply helpful? Yes No