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

[Anonymous]

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.

[edguerre]

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]

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

[edguerre]

Hi,

I guess something like this would work for you:

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]

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 

[Anonymous]

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) with property as Number.

[edguerre]

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

Hi,

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

[edguerre]

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

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.

[edguerre]

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

[Anonymous]

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

[edguerre]

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.