Unanswered

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

(0) Share

Report

Posted on by WmSWoodson

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.

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.

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (0)

Rhiassuring 8,690 Moderator on at

Like (0)

Report

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:

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

Was this reply helpful? Yes No
WmSWoodson 10 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Rhiassuring 8,690 Moderator on at

Like (0)

Report

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:

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

Was this reply helpful? Yes No
WmSWoodson 10 on at

Like (0)

Report

@Rhiassuring Thank you, but I can't get your examples to work. I think it's because I forgot to mention, in my original post, that the file I'm working with is stored in a Sharepoint library. So, getting the rows from the table has been impossible, as the only option for me to use to get the rows is Excel Online. I don't have the ability to use that with the security protocols currently in place within our organization. I tried using getting file content under the Sharepoint actions, but there isn't an option for getting the rows, at least not in the version of PA my company allows me to use.

Was this reply helpful? Yes No
Rhiassuring 8,690 Moderator on at

Like (0)

Report

Yes, Excel Online is the only option.

Your Excel action will just look like this instead:

Your SharePoint site should appear in the dropdown. You navigate to it the same way.

Was this reply helpful? Yes No
WmSWoodson 10 on at

Like (0)

Report

Hi, @Rhiassuring! I finally got the access to Excel Online resolved, and following your script for the 'TODAY' filter query, I ran my first test flow. Unfortunately, it failed with a description I'm not quite sure how to decipher:

{"statusCode":400,"headers":{"Pragma":"no-cache","x-ms-request-id":"0b0e6861-00d6-40bf-8b98-ddf2854af020","Strict-Transport-Security":"max-age=31536000; includeSubDomains","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"false","x-ms-apihub-obo":"true","Cache-Control":"no-store, no-cache","Date":"Tue, 23 Aug 2022 22:41:29 GMT","Content-Length":"480","Content-Type":"application/json","Expires":"-1"},"body":{"status":400,"message":"Syntax error at position 14 in 'DRIVER LICENSE eq 44796'.\r\n inner exception: Syntax error at position 14 in 'DRIVER LICENSE eq 44796'.\r\nclientRequestId: 0b0e6861-00d6-40bf-8b98-ddf2854af020","error":{"message":"Syntax error at position 14 in 'DRIVER LICENSE eq 44796'.\r\n inner exception: Syntax error at position 14 in 'DRIVER LICENSE eq 44796'."},"source":"excelonline-ne.azconn-ne-002.p.azurewebsites.net"}}

Here's my flow step:

Was this reply helpful? Yes No