Create flow with sharepoint list

[manojkulkarni]

Hi Team,

I am new to the power automate environment. Request the experts suggestions on how the below scenario can be achieved.

Requirement:

I have sharepoint list with some activities having planned & forecast dates. I need to create a flow and trigger an email when we have current date greater than the forecast with status still in progress or not closed.

Any suggestions are much appreciated. 

[Nived_Nambiar]

Hi @manojkulkarni 

see the sample flow design to get you understand the things 

I have created two columns in a sharepoint list - Forecast date and Status like below

Now coming to flow design 

1. You have to design a recurrance flow which runs at specific interval of time. 

see the link for getting details about recurrance flows :- https://www.serverlessnotes.com/docs/understanding-the-power-automate-recurrence-schedule-trigger

2. Select flow type as Scheduled flow and create it by setting the flow run interval, 

3. Use get items action to get items from SharePoint List 

Here we have written a filter query which filters the item based on status column, if status column is inprogress or not closed.

4. Next use an apply to each loop which loop through the value array (dynamic content from get items )

Next steps are explained under loop 

4.1-  use a condition with expression which calculates the difference b/w forecast date and current date, if difference >0 it means current date has passed forecast date

Expression used:- 

int(split(dateDifference(formatDateTime(items('Apply_to_each')?['ForecastDate'],'yyyy-MM-dd'),formatDateTime(utcNow(),'yyyy-MM-dd')),'.')?[0])

once this condition is satisifed, use send email in yes branch to send email 

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[manojkulkarni]

Thank @Nived_Nambiar for your quick response and the complete steps.

I started following your steps, but i am facing connection issue to Sharepoint, pls could you help me with any specific documents or steps to follow for the connections.

I am getting error like below 

The dynamic operation request to API 'sharepointonline' operation 'GetTable' failed with status code 'Unauthorized'

[Nived_Nambiar]

Hi @manojkulkarni 

check this 

https://powerusers.microsoft.com/t5/Building-Flows/The-dynamic-operation-request-to-API-sharepointon...

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[manojkulkarni]

@Nived_Nambiar , do you have any videos on how to create this flow, i am not able to follow the conditions. 

I am not getting the "value" option under dynamic content, instead i am getting body/value, is it same or different

[Nived_Nambiar]

it is same only 🙂

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[manojkulkarni]

Thanks for your solution, it is working. 

I have one more use case, if the forecast date is coming in next 30days also i need to trigger  how this can be accommodated in the same condition or do we need to add separately ?

[Nived_Nambiar]

Hi @manojkulkarni 

You can add that condition in same condition action, one thing to know there - should it or condition  or and condition?

Also next days days means it should be equal to 30 days or less than or equal to 30 days ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[manojkulkarni]

Hi @Nived_Nambiar , In both the cases i will have different mail content depending upon the condition, so my question her is how this can be achieved ? 

Do i need to write 2 separate conditions with it's own email condition or can this be accommodated in same condition

[Nived_Nambiar]

Hi @manojkulkarni 

If you have two mail contents depending upon condition, Then i would suggest this approach. This approach should be extended from what i have discussed in my main reply for your use case above.

in the condition action, we are providing currently the below condition 

This checks whether current date has passed forecast date or not. if the condition is not satified, it will go to no branch which means current date has n't passed forecast date , it means current date <=forecast date.

So in no branch put a condition action and write an expression like below which checks whether difference b/w current date and forecast date <=30(I hope this is expected), if yes send  (I hope this is what you want to acheive) an email should be sent. See below flow part 

Expression used- 

int(split(dateDifference(formatDateTime(utcNow(),'yyyy-MM-dd'),formatDateTime(items('Apply_to_each')?['ForecastDate'],'yyyy-MM-dd')),'.')?[0])

if yes-> send an email with desired mail content.

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[manojkulkarni]

Thanks for your suggestions. it worked.

[Nived_Nambiar · Accepted solution]

Hi @manojkulkarni 

Kindly mark it as solution if it helps 🙂

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌