0

I've been going in circles trying to get my Power Automate flow to work and would like to know if anybody has a fix for my problem. What I'd like my flow to do:

In my excel sheet on SharePoint, there's a column that has "Contract End Date". Everyday in the morning at 9:00am to have an automatic recurrence flow to check if any of the values under "Contract End Date" expire in 30 days-using "addDays(utcNow(), 30, 'yyyy-MM-dd')", and for those contracts that do expire in 30 days to send an Email to a validator with a table that has the name of the contractor expiring and the date it does.

Obstacle: The current issue with my flow is that when it sends the Email, it'll send multiple counts of the email to each person. For example, if 5 people's contracts were to expire in 30 days, my current flow would send 5 of the same email with the table of the 5 people's contracts and names to the validator. I was wondering if anybody had a workaround.

Edit:To clarify my need, I only want to send the list of people expiring to one person, to myself for example. The current problem that I'm encountering is that if the "Send an Email" is inside the "Apply to each" and sent to ME, it'll send me 5 of the same emails if there are 5 people expiring. If the "Send an email" is on the outside the "Apply to each", if the condition is true, it works properly. The problem here is that if the condition is false, it'll still send an email with "Null,Null" repeated . FlowP1

flowP2

2 Answers 2

0

You need a few variables and an IF condition inside the Apply to each. Your data must be sorted by the person's name, so you can determine if a job belongs to the current person or the next person. Then you can do this:

  • before the For Each loop, set a variable to the active person, i.e. the first person in the table, using a filter expression.
  • also before the loop, initiate a counter variable and set it to zero
  • start the loop
  • read the person from the current record
  • if active person and current record's person are the same, increment the counter by 1 and loop to the next record
  • if active person and current record's person are not the same,
    1. send an email to the active person saying "you have 'value of counter' open jobs"
    1. set the active person variable to the current person
    1. set the counter to 1
    1. loop to the next record
2
  • Thank you for your input. To clarify my need, I only want to send the list of people expiring to one person, to myself for example. The current problem that I'm encountering is that if the "Send an Email" is inside the "Apply to each" and sent to ME, it'll send me 5 of the same emails if there are 5 people expiring. If the "Send an email" is on the outside the "Apply to each", if the condition is true, it works properly. The problem here is that if the condition is false, it'll still send an email with "Null,Null" repeated .
    – Schwalbe
    Apr 7, 2021 at 18:05
  • Well, then don't send the email inside the loop. Instead, append the data to a variable or use a Compose action to add the data to an object. Then after the loop has finished, send the email with the output of the Compose.
    – teylyn
    Apr 7, 2021 at 21:34
0

Please find the below screen shot for working exmple for your problem.enter image description here

Please follow steps dipicted in pics.

Accepts the answer if you are satisfied.

Thanks

3
  • Thank you for your input. To clarify my need, I only want to send the list of people expiring to one person, to myself for example. The current problem that I'm encountering is that if the "Send an Email" is inside the "Apply to each" and sent to ME, it'll send me 5 of the same emails if there are 5 people expiring. If the "Send an email" is on the outside the "Apply to each", if the condition is true, it works properly. The problem here is that if the condition is false, it'll still send an email with "Null,Null" repeated .
    – Schwalbe
    Apr 7, 2021 at 18:05
  • for that you need ot put you send email action out of loop and one variable wich will hold the table. Apr 8, 2021 at 7:07
  • if it helps you please accept as answer Apr 9, 2021 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.