Generate Reminder Email in Excel and Power Automate

Thank you again for the help.

I tried following the instructions you sent and I am getting a fail on the Select step.

For the SO key you added in the map (SO_x0023) - I'm not sure how you got that as an item?

When I run the test, I get the below failure message. I did try to map in the Select step and just tried copying/pasting the code lines you provided, but that gives me the message "Enter a valid json".

The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{ "BranchName": "@item()?['Branch Name']", "ReminderDate": "@formatdatetime(item()?['Reminder Date'],'MM/dd/yyyy')", "InstallDate": "@formatdatetime(item()?['Install Date'],'MM/dd/yyyy')", "CloseoutDate": "@formatdatetime(item()?['Closeout Date'],'MM/dd/yyyy')" }' failed: 'The template language function 'formatdatetime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'.

Dear Brian,

Thanks for providing the file in the private message.

The Excel Date in the example file is Date format.

So you can directly use the expression @greaterOrEquals(item()?['Reminder Date'], utcNow()). I use the condition Reminder Date is greater than or equal to Now.

Then the HTML table Map can be like the following.

{

"SO": @{item()?['SO_x0023_']},

"BranchName": @{item()?['Branch Name']},

"ReminderDate": @{formatDateTime(item()?['Reminder Date'],'MM/dd/yyyy')},

"ClosoutDate": @{formatDateTime(item()?['CLOSEOUT DATE'],'MM/dd/yyyy')},

"InstallDate": @{formatDateTime(item()?['INSTALL DATE'],'MM/dd/yyyy')}

}

The flow can be like the following.

Result:

Welcome to share any updates at your convenience.

Your efforts and time are greatly appreciated!

Sincerely

Cliff | Microsoft Community Moderator

Dear Brian,

Thanks for your updates and kindness.

From the screenshot, the flow fails in the Filter Array step.

From your screenshot in your last reply, the date of the Input which is the original source in the Filter Array action is showed correctly.

If it is convenient, could you expand the action in the flow result and provide a screenshot for us to confirm whether the date in the output is correct as well?

Note: Remove nay private information before uploading the screenshots.

Meanwhile, I'd like you to provide the file you are using for us to do a test. You can remove the private information and then share it with us. I have sent a private message to you to collect the file.

You can access the private message by the following way.

Your efforts and time are greatly appreciated!

Sincerely

Cliff | Microsoft Community Moderator

This is what I get when I run the test flow:

The Filter Array function fails and just for clarity, here are the two expressions in the Filter Array:

Hopefully this helps, but let me know if you need me to provide the original files and i can sanitize and provide (just let me know the best email address to send it to).

Dear Brian,

The Filter Array action in my example is to filter "Reminder Date" is equal to "Now". You can customize the condition as you like.

From the screenshot of the Excel table you provided, it seems that no Reminder Date is matched.

May I know whether the Filter Array action runs successfully and just gets the blank output in your environment?

From the raw input you provided, the date format is correct.

If there is some error appears in the output, could you provide the output in your environment for our reference?

If the issue persists, the next step may be to collect the Excel file you are using for us to do a test in our environment.

Your efforts and time are greatly appreciated!

Sincerely

Cliff | Microsoft Community Moderator