cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rickfear
Level: Powered On

filter with multiple criteria

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"

1 ACCEPTED SOLUTION

Accepted Solutions

Re: filter with multiple criteria

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.

View solution in original post

10 REPLIES 10
Super User
Super User

Re: filter with multiple criteria

Hi @rickfear,

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"

 

Administrator
Administrator

Re: filter with multiple criteria

Thanks for your help @Drrickryp

 

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

 

@TopShelf-MSFT

rickfear
Level: Powered On

Re: filter with multiple criteria

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

panand99
Level 10

Re: filter with multiple criteria

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.

rickfear
Level: Powered On

Re: filter with multiple criteria

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

 

 

 

panand99
Level 10

Re: filter with multiple criteria

Super User
Super User

Re: filter with multiple criteria

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.

Re: filter with multiple criteria

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.

View solution in original post

rickfear
Level: Powered On

Re: filter with multiple criteria

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 357 members 6,047 guests
Please welcome our newest community members: