Sending alerts to a specific user with an updated holiday allowance

[darrenfloyd666]

Morning

I have two lists. List one is 'Staff_absence_details' which contains staff names and their holiday allocation. The second list is called 'Absence_request' and is where staff request a leave of absence.

I have an approval flow which upon approval should take the number of days requested (in the second list) away from the holiday allocation (in the first list). The flow is looping through all the staff names and sending out alerts showing how many days remain for all the staff.

How do I make the Flow only subtract from allocation of the member of staff who has requested the leave and alert them to how much holiday they have left? Do I need to build in a condition? The actions which do the maths is below, all help appreciated@

Staff_absence_details

[darrenfloyd666]

Hi @Jcook  I don't suppose you have an idea how I could resolve this?

Thanks

[Jcook]

Hi @darrenfloyd666 

Thanks for reaching out!

I might need more information on your flow.

But from what I can see, this might help fix the issue.

In your SP list are you storing the email of the user? I am asking because you could filter the SP List on the user who is requesting the time off.

That way the loop is only looping through the one user who is requesting the time

[darrenfloyd666]

Hi @Jcook 

Thanks for the reply. Yes I'm storing the email in a field called 'Name' and there's also the SP ID field. I have tried to filter in the Get actions but I still get the multiple emails?

[Jcook]

@darrenfloyd666 

The Odata Filter needs to specify the column name than the value, like this:

Name eq '  '

Put your dynamic content in the single quotes.

You might need to look in the previous run to get the exact name of the Name column

[darrenfloyd666]

Thanks @Jcook   I think I'm nearly there!

I'm getting the following error: The expression "Name equ Darren.floyd@xxx.co.uk" is not valid. Creating query failed." so it's finding the email address. I checked what the field was called in the URL and it said: "&Field=Title". I've now also got an email field for each staff member.
Here's how I've got it configured:

[darrenfloyd666]

Hi @Jcook 

Can I use the ID to filter the results returned just so I get the person who made the request?

[Jcook]

Hi @darrenfloyd666 

In your run where is this error being shown?

To validate the exact column name, I usually like to run the Get items with no Filter and see what the output is in the run. These will be the column names to use.

[Jcook]

@darrenfloyd666 

So once you find the column name to filter on. Lets say it is Title.

Than you want to put the dynamic content of the persons Emails address of the requestor. So you would put the Requestor from the approval

[darrenfloyd666]

Thanks for your help @Jcook .

I've got a list called Staff Absence Details which has five staff members and their holiday allocation. There's some math functions later in the Flow. When I run the Flow it subtracts the requested leave from all five members and sends five email alerts out where I just want it subtract and send it to the person who has requested it. This is what I currently have but it doesn't seem to be filtering the results. I only want to return the person who requested the leave of absence. 

[Jcook]

@darrenfloyd666 

What is your trigger? How Does the approval kick off?

[darrenfloyd666]

The Flow is triggered when a person adds an item to the Absence Request list:

[darrenfloyd666]

Am I missing a filter somewhere else I could apply @Jcook 

[Jcook]

@darrenfloyd666 

In your Get items filter use:

Title eq ' '

Inside the single quotes try putting the dynamic value Modified By Email. Make sure when selecting this value that it is the one from your trigger. Than use an Apply Apply to each loop with value from Get Items. Put the approval inside there.

When you try the above. And run the flow, what exactly is happening? 

[darrenfloyd666]

@JcookThe Flow doesn't get past the Get Items - it throws the error The expression "Title eq 'Darren.floyd@xxx.co.uk" is not valid. Creating query failed.

[Jcook · Accepted solution]

@darrenfloyd666 

Okay, I can fix your filter. But I will need your Get items output without your filter.

Remove your filter in Get items, and set Top count to 1.

This is so we only get one record.

Please send me the output.

[darrenfloyd666]

Great, thanks @Jcook 

Here it is:

{"statusCode":200,"headers":{"Transfer-Encoding":"chunked","Vary":"Accept-Encoding","X-SharePointHealthScore":"2","X-MS-SPConnector":"1","X-SP-SERVERSTATE":"ReadOnly=0","DATASERVICEVERSION":"3.0","SPClientServiceRequestDuration":"51","SPRequestGuid":"477291be-3201-4601-b849-b8efbdb40b8c","request-id":"xxx-3201-4601-b849-b8efbdb40b8c","MS-CV":"vpFyRwEyAUa4SbjvvbQLjA.0","Strict-Transport-Security":"max-age=31536000","X-FRAME-OPTIONS":"SAMEORIGIN","MicrosoftSharePointTeamServices":"16.0.0.20106","X-Content-Type-Options":"nosniff","X-MS-InvokeApp":"1; RequireReadOnly","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"true","Cache-Control":"max-age=0, private","Date":"Sat, 16 May 2020 13:16:24 GMT","P3P":"CP=\"ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI\"","X-AspNet-Version":"4.0 ","X-Powered-By":"ASP.NET","Content-Type":"application/json; charset=utf-8","Expires":"Fri, 01 May 2020 13:16:24 GMT","Last-Modified":"Sat, 16 May 2020 13:16:24 GMT","Content-Length":"2544"},"body":{"@odata.nextLink":"https://uk-001.azure-apim.net/apim/sharepointonline/shared-sharepointonl- 0e-00a2-4038-a506-6a59ff49a142/datasets/https%253A%252F%252Ficotechservicescouk.sharepoint.com%252F/tables/1afdcd87-4814-49de-a6d6-ceee06eff40b/items?$top=1&$skiptoken=Paged%3dTRUE%26p_ID%3d1%26RootFolder%3d%252fLists%252fStaff%255fabsence%255fdetails","value":[{"@odata.etag":"\"3\"","ItemInternalId":"1","ID":1,"Title":"Geriant Lewis","Email":{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser","Claims":"i:0#.f|membership|geraint.lewis@ .co.uk","DisplayName":"Geraint Lewis","Email":"Geraint.lewis@

.co.uk","Picture":"https:// .sharepoint.com/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Geraint.lewis .co.uk","Department":null,"JobTitle":" Manager"},"Email#Claims":"i:0#.f|membership|geraint.lewis@ .co.uk","Holiday_allocation":25.0,"Modified":"2020-05-15T13:23:57Z","Created":"2020-05-06T09:43:59Z","Author":{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser","Claims":"i:0#.f|membership|darren.floyd@icotechservices.co.uk","DisplayName":"Darren Floyd","Email":"Darren.floyd co.uk","Picture":"https:// .sharepoint.com/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Darren.floyd@ .co.uk","Department":null,"JobTitle":"O365 & SharePoint Administrator"},"Author#Claims":"i:0#.f|membership|darren.floyd@icotechservices.co.uk","Editor":{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser","Claims":"i:0#.f|membership|darren.floyd .co.uk","DisplayName":"Darren Floyd","Email":"Darren.floyd@ ","Picture":"https:// .sharepoint.com/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Darren.floyd@ .co.uk","Department":null,"JobTitle":"O365 & SharePoint Administrator"},"Editor#Claims":"i:0#.f|membership|darren.floyd@icotechservices.co.uk","{Identifier}":"Lists%252fStaff_absence_details%252f1_.000","{IsFolder}":false,"{Thumbnail}":{"Large":null,"Medium":null,"Small":null},"{Link}":"https:// .sharepoint.com/_layouts/15/listform.aspx?PageType=4&ListId=1afdcd87-4814-49de-a6d6-ceee06eff40b&ID=1&ContentTypeID=0x01009C9AE2D9F6E82341ADCD97B0493FE0D2","{Name}":"Geriant Lewis","{FilenameWithExtension}":"Geriant Lewis","{Path}":"Lists/Staff_absence_details/","{FullPath}":"Lists/Staff_absence_details/1_.000","{HasAttachments}":false,"{VersionNumber}":"1.0"}]}}

[darrenfloyd666]

Hi @Jcook 

I solved the problem by approaching it from a different angle and splitting it into two Flows but wanted to tank you for your help.

[Jcook]

@darrenfloyd666 

Glad to help.

That's awesome you were able to get it working!