cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WmSWoodson
New Member

I'm trying to send an email by comparing a future date to several date fields in an excel table

Hello All!

 

First, I'd like to give you some background on my task at hand.  As the DOT Compliance Administrator, I have an excel spreadsheet which contains columns for all of our drivers, their credential expiration dates, along with several columns that contain formulas to compare those expiration dates to today's date and auto-fill with "yes" or "no" as to whether they're qualified to drive a vehicle, and if they also have an CDL, that they are also qualified to drive another specific kind of vehicle.  Each date in the column is also conditionally formatted to display a certain color based on how far into the future the date occurs compared to today.  So, there is a "legend" at the top of the page describing what each color means.  The color-coding would be meaningless without it.  Because of this, my column headers do not start in cell A1, more like A9.  As such, I haven't been able to work out a way to use this spreadsheet to extract the data I want to use to send an email reminder to the driver, his or her supervisor, the safety manager, and myself, when their credential expires 30 days and 7 days in the future, for example, from today.

 

WmSWoodson_0-1659762374614.png

 

Thus, it seems that my first task is to replicate this file to a new one containing an excel table that power automate would be able to "read," which would only contain the drivers' names, the expiration dates, and the email addresses, without all of the formatting and formulas.  The caveat being that any updates/additions/subtractions to the original file (i.e. adding a new driver, marking one "Inactive," or changing a date to a new date in the future) would automatically replicate over to the new file without having to open it to "refresh all" or run a macro.  The original file contains a switch, or filter, to make only active drivers visible, but the information for the inactive drivers is still there.

 

Then, I feel my next task would be to use power automate on the new file to compare those dates, so that an email reminder is automatically generated daily (scheduled flow) and sent to each recipient based on the same criteria as the color-coding of the legend in the original file.  I would also like to send a different email with more urgency if their credential dates have already expired (red) or there is no date at all (purple).  Obviously, I don't want the flow to evaluate the dates of drivers that are inactive.

 

If I'm over-simplifying this, and have left out any critical step(s), I'd also appreciate any feedback as to what those steps might be.  My goal is to automate this process as much as possible so as not to need to go physically check the file everyday for credentials that are expiring soon.  

3 REPLIES 3
Rhiassuring
Community Champion
Community Champion

I don't think you're simplifying, you've got it - the biggest issue will be converting dates, but, we'll get that.

 

For your main flow...

 

Step 1) Turn this thing into a table. I don't think you need to create a whole new sheet or anything. Just set it up as a table - highlight it, convert to table. It'll keep your formulas. Formulas are no biggie, Power Automate can handle that - it'll show you the value you need.

Step 2) Create a scheduled daily flow that runs a query on your table using the "List rows present in table", and the Filter Query. You would likely have one of these for each of your required frequencies - ie, today, next week, one month.


➡️For "Expiry is Today!" you could have something like  "ExpiryDate eq @{div(sub(ticks(formatDateTime(utcNow(),'M/dd/yyyy')),ticks('1899-12-30')),864000000000)}" -- where ExpiryDate is the name of your column. This will bring back all entries that have an ExpiryDate of UTC today. 

 

➡️For "Expiry is in a Week!" you could have something like  "ExpiryDate eq @{div(sub(ticks(formatDateTime(addDays(utcNow(),7),'M/dd/yyyy')),ticks('1899-12-30')),864000000000)}" -- This will bring back all entries that have an ExpiryDate of UTC today PLUS 7 days.

Step 3) If you return values, send emails. If you don't, move onto the next frequency. Your flow might look something like this:

Rhiassuring_0-1659767535234.png

 

 

And that's it.   EXCEPT!!!!!!!!!!!!

 

Your purple stuff. Honestly, the fact that they're empty, to me, is a one time email, or, you need a word in there. Ie, "Mailed 2022" or "No Expiration" or something to tell us why it's purple. That way, you can create a flow that says "find all empty expiration dates and tell their owner that they need to send me the expiration date." Then, populate those empty squares with "Mailed 2022" or "Mailed One Time" or SOMETHING so that you don't end up spamming them.  

I'd want to hear more about how you want to handle the purple stuff.

 

R

 




 

Thank you @Rhiassuring for your valuable information.  Would it be better to have the evaluation of the dates in series, or a combination of parallel and series, rather than running them parallel, since in your example, I would need to create a version that looks at today, 7 days, and 30 days for each of the four columns (driver license, MVR, Med Card, and Annual Review)?  I would think that I might exceed the flow's "transaction" limit if I tried to run all of them in parallel branches, because I would end up with 12 branches all on the same level.

 

As for the empty (purple) fields, all four of the columns are validated to only accept a date in the format shown (yyyy-mm-dd), as other users were trying to make long notes in there, and that was causing all kinds of errors in the other columns to the right, that aren't shown in my screenshot, to evaluate a driver's eligibility to drive a company vehicle.  Those columns check to see if all the dates are greater than today's date and returns a "Yes" or "No" in that column based on all four columns satisfying that criteria. 

Rhiassuring
Community Champion
Community Champion

Ah, I see what you're saying. In that case, I'd probably just get all items and then filter the output based on your need. 

So my super simplified example is this:

Rhiassuring_0-1659815715477.png


If you get all items, then filter query per need, you can take that info and put it into an Array - so that way, you can use it on one email at the end. In this example I'm only showing 7 days Med Card. All the stuff between "initialize variable" and "apply to each - send all emails" would be done for each. 

The filter array is the same as the filter query, but, I wrapped it in a "string()" so it can compare. Then I use a Parse JSON on the output of that, so I can grab the values, and put them into building the "arrCards". Inside of the "append to array variable", I have this:

{
"CardType": "Medical",
"Email": "@{items('Apply_to_each_-_for_each_7_day_med_card')['Email]}",
"Days": "7",
"Subject": "URGENT: Your Medical card will expire in 7 days!"
}

Basically just taking what I need to compose the emails, and putting it into a single array. 

 

Basically just taking what I need

Rhiassuring_0-1659833865487.png

 




Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,196)