Daily Recurrence, Check SharePoint List for upcoming anniversaries, for each, Send email to supervisor w/attachments from SP List.

[Anonymous]

Hello helpful community experts,

I'm looking to build a flow that will check a SP List daily for employees with work anniversaries that are 30-days out, and for each employee that meets that criteria, send an email to that employee's supervisor with 3 unique documents attached which are specific to the employee (these 3 documents are saved in the SP List in columns formatted as hyperlinks).  

Additional Info: 
a)  The SP List is secured to HR access only due to its inclusion of confidential info, so sending supervisors a direct link to the SP List itself is not allowed.

b)  I'd prefer that the email send the 3 documents as links rather than actual attachments, but if that's not a possibility I can save the 3 documents in the "attachments" column of the SP List.

Here is an image of my SP List:

I've attempted a few variations flows, but none have worked thus far.  The most recent trial included using the Initialize Variable steps, but when there was a 2nd employee which had an anniversary 30-days out, the email to the supervisor included attachments from the 1st employee in addition to its own.

[PrasadAthalye]

Can you post a screen shot of your flow? 

[Anonymous]

Here's an overview of the current flow, which is using the Initialize Variable step, but is lumping ALL attachments for subsequent employees instead of just the current employee's. 

[v-litu-msft]

Hi @Anonymous,

The reason why you will get the attachments of all the qualified item should be Get items have obtained the previous item. Can you expand the action of Get items?

If you want to get item that 30DayNotice equals to today, you could format 30DayNotice and today to "yyyy-MM-dd" before compare them.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Here is the expansion of my Get Items:

I can't use the formula you suggest to get the items with the 30-day notice due to the YYYY.  My 30DayNotice column is calculating just the MM-dd since it's using Hire Date which could have any hire year associated with it.  I haven't found a way to use the Filter Query in Get Items that works to find the 30-day items.  (Please share if you know - for now I have a Condition that is working to get these items.

Here is an expanded view of my two Apply To Each's, including the 30-day condition that's currently working:

[v-litu-msft]

Hi @Anonymous,

OK, if the 30-day column is "MM-dd", you could format utc time to "MM-dd" and compare, for example:

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I attempted your suggestion for the Filter Query within the Get Items step, but I don't have the Dynamic option of selecting my 30DayNotice column, so I ended up just typing it in there.  It didn't work.  The error details state:

[v-litu-msft]

Hi @Anonymous,

OK, how about use filter array action to filter out these items:

item()?['30DayNotice']

utcNow('MM-dd')

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

We're getting closer, I can feel it!  I guessed that the Filter array step should be inserted just after Get Items, hopefully that was the right spot, but tell me if I guessed wrong. 

I'm also guessing I no longer need my original Condition to get the 30DayNotice employees since we've added the Filter Array step to get these?... but that condition is where I had my Send an Email step...  I did a test run with leaving that Condition in, and I got 2 emails for my 2 employees, which is great, however the linked attachments in both emails were for the same (1st) employee, so there's something not working correctly still.

Here's the updated flow image below.  The Condition with Send an Email was at the very end.  Where should the Send an Email step be inserted so that each email gets its own attachments?

[PrasadAthalye]

I think it should be inside loop

[v-litu-msft]

Hi @Anonymous,

In the Apply to each 2 action body field, it should be the output of the Filter Array action:

And the dynamic content couldn't see, but you could use the expression to get these properties, for example:

item()?['ColumnName']

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I made the changes, and the flow said it ran successfully, but I didn't get any emails.

Here's what the "successful run" looks like - the whole Apply to each 2 is all grayed out as if it did nothing.  

Here's the expansion of the changes I made in the Apply to each 2.  I used your suggestion of entering the items formula with the column names in the Compose steps, which when I clicked Save, automatically changed to what you see below.

[v-litu-msft]

Hi @Anonymous,

That's caused by the Filter array not find items met the condition.

Please make sure there are items met the condition that 30DayNotice equals to 'date value'.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

That was my initial fear, so I inserted a (bogus) Send an Email to myself that it was "working for [employee name]" (shown below as an Apply to each 3), and I got emails for both employees.  Something in the Apply to each 2 isn't setup right that it's not progressing.

Here's a screenshot of my test with the bogus Send an Email that worked, demonstrating that the Filter Array is finding the employees correctly.  

[Anonymous]

Update:  The flow somehow picked up employees whose 30-day notice was yesterday, not today...?

I received my "bogus" emails this morning that I'd setup to test that the Filter Array was working properly, except no emails should've been generated this morning based on the 30DayNotice field...  

[v-litu-msft]

Hi @Anonymous,

You could check the run history to get the column return from Sharepoint item whether equals to 08-12

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

The flow ran again this morning and sent emails for employees whose 30daynotice was yesterday.  Here's the run history info from the Get Items and also the output of the Filter Array.  Should the Filter Array have shown something in the Output?

[v-litu-msft]

Hi @Anonymous,

OK, I think this issue is associated with the time zone. utcNow() function is getting current universal time.

As a tweak, you could add one day based on the utcNow() time at the right side of Filter array action:

addDays(utcNow(),1,'MM-dd')

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.