cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filter user and count number of days applied and update a column

Hi Scott, @ScottShearer , I have a leave management SharePoint list and want to count the number of leaves applied by a user from beginning to till date so that the annual allotted leave will be deducted from YTD leave and balance is shown in another column. I want flow to filter or look for user who has submitted the leave and count and give the total of the other column where number of days leave will be there pertaining to this user. The number of days leave is a calculated column which is result of End date minus Start date.

Please help in filtering or queering the submitter name and count the number days applied by the person.

12 REPLIES 12
edguerre
Microsoft
Microsoft

Hi,

 

Would you want a flow that triggers every time a new entry is added on an SPO list?

What you're asking seems possible, but I need more information here. You can always get the user who submitted the entry, and filter records based on User Email = Email that submitted the entry. But please provice more details here if possible!

Thanks in advance.

Anonymous
Not applicable

Hi,

Thank you for the response, let me explain the requirement in details. 1. Users A, B, C, D will be submitting request for leave in using the form connected to SharePoint List 2. The SharePoint list will have columns like start date, end date and no of days leave applied, in this start and end date columns are date columns and no of days column is calculated field which will calculate the no of days (end date minus start date) and put the result 3. I want an action in the flow when A submits a leave request, look for A’s other leave entries (which is nothing but filtering all the leave entries of A) and then look for corresponding entries of A’s no of days leaves applied column, sum of all leave would then to be updated to a column YTD leave Hope this clarifies

Hi,

 

I guess something like this would work for you:

 

edguerre_0-1594368897989.png

 

 

The flow triggers when a new item is submitted. After this, you get all items submitted by that user email, by filtering.

Then you initialize an empty variable and add an apply to each where, for each item retrieved from the list, it will get the Total Leave Days from that item and add it to the variable. After the loop ends, you should have the total of days, and then you can update the item you with with that info.

 

Hope this helps!

Anonymous
Not applicable

Hi, Thanks for the quick respsonse, i have tried to get info as per the screenshot but the flow ended with error in the action apply to each,

Could you please tell me

1. What items to be selected in the apply to each

2. What needs to be selected as Value field under action Increment variable LeaveRequestQuerry3.jpg

Anonymous
Not applicable

Hi,

I am getting the below error, as the column that flow counts in not an integer, as mentioned to you the No of days column is a calculated column  (leave end date - leave start date) LeaveRequestQuerry4.jpgwith property as Number.

@Anonymous 

 

1- When you put your mouse over 'value', it must come from the Get items connector.

2- Change the value to a string, as the values coming from the calculated field aren't integers. Should work like that.

Anonymous
Not applicable

Hi,

Thanks for the response, i tried by changing to String but still it is not working. Attached is the error screenshot.FlowError.jpg

@Anonymous 

 

Can you go to the Sharepoint list, look at that column and confirm what type of variable it contains? It needs to be integer on the flow and integer on Sharepoint!

Anonymous
Not applicable

Hi,

The type of the column is number and it is the column which is a calculated column with the result of other 2 columns (leave end date minus (-) leave start date.FlowError2.jpg

@Anonymous Change it to Single line of text please, and keep the flow with the variable as a string.

Anonymous
Not applicable

Hi,

It's still not working even after changing the column to single line of text, now the error is different as below

 

The inputs of workflow run action 'Increment_variable_2' of type 'IncrementVariable' are not valid. The specified variable 'Total Leave Days' of type 'String' is not supported. The supported variable types are 'Integer, Float'.

This is pretty strange. As a last test, I would change the column to Number, the variable to Float on the flow and try again. There is a mismatch of variable types here.

If this does not resolve your issue, I recommend to open a support ticket with Microsoft - I believe this would be quickly resolved over a call.

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,227)