How to filter sharepoint list items and send reminder email before two days of due date

[Anonymous]

The execution of template action 'Filter_array' failed: The result 'dueDate le '2023-03-24' and dueDate ge '2023-03-22'' of the evaluation of 'query' action 'where' expression 'dueDate le '@{formatDateTime(addDays(utcNow(), 2), 'yyyy-MM-dd')}' and dueDate ge '@{formatDateTime(utcNow(), 'yyyy-MM-dd')}'' is not a valid boolean value.

[v-liwei-msft]

Hi @Anonymous 

Could you provide a screenshot of your flow?

Best Regards,

Levi

[Anonymous]

Please find the attached

[Anonymous]

DueDate le '@{formatDateTime(addDays(utcNow(), 2), 'yyyy-MM-dd')}' and DueDate ge '@{formatDateTime(utcNow(), 'yyyy-MM-dd')}'

[v-liwei-msft]

Hi @Anonymous 

Please try:

@and(lessOrEquals(item()?['dueDate'], formatDateTime(addDays(utcNow(), 2), 'yyyy-MM-dd')),greaterOrEquals(item()?['dueDate'], formatDateTime(utcNow(), 'yyyy-MM-dd')))

Best Regards,

Levi

[Anonymous]

Getting this error

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@and(lessOrEquals(item()?['dueDate'], formatDateTime(addDays(utcNow(), 2), 'yyyy-MM-dd')),greaterOrEquals(item()?['dueDate'], formatDateTime(utcNow(), 'yyyy-MM-dd')))' failed: 'The template language function 'lessOrEquals' expects two parameter of matching types. The function was invoked with values of type 'Null' and 'String' that do not match.'.

[v-liwei-msft]

Hi @Anonymous 

@and(not(equals(item()?['dueDate'], null)),lessOrEquals(item()?['dueDate'], formatDateTime(addDays(utcNow(), 2), 'yyyy-MM-dd')),greaterOrEquals(item()?['dueDate'], formatDateTime(utcNow(), 'yyyy-MM-dd')))

Best Regards,

Levi

[Anonymous]

The execution of template action 'Apply_to_each_2' failed: the result of the evaluation of 'foreach' expression '@items('Apply_to_each')' is of type 'Object'. The result must be a valid array

[v-liwei-msft]

Hi @Anonymous 

Your flow screenshot does not show the information of Apply to each2.
Please provide detailed flow screenshots

[Anonymous]

Please find the detailed screenshot

[grantjenkins]

Just confirming. Are you wanting to get items that are due it 2 days from today? What about items that are due in 1 day?

Also, did you want to send an individual email for each item overdue, or a single email with a summary of all the items that are due?

[Anonymous]

single email with a summary of all the items that are due before 2 days of due date

[grantjenkins]

This is how I would build the flow to get what you're looking for.

For this example, I'm using the following list.

See full flow below. I'll go into each of the actions.

Recurrence is set to run daily, the same as what you already have.

Get items retrieves the items from today, tomorrow, and the next day (two days out). It uses the following expression in the Filter Query. It's much more efficient to add the query here rather than return all items, then use a Filter array.

DueDate ge '@{startOfDay(utcNow())}' and DueDate lt '@{startOfDay(addDays(utcNow(), 3))}'

Create HTML table maps the data we want to show in our table. Below is the expression that formats the due date (optional).

formatDateTime(item()?['DueDate'], 'dd MMM yyyy')

Style HTML table is a Compose action (I just renamed it) that contains some CSS for styling the HTML table.

<style>
    table {
        border-collapse: collapse;
    }
    table td,
    table th {
        border: 1px solid #ddd;
        padding: 6px 20px;
        text-align: left;
    }
    table th {
        background-color: #1C6EA4;
        color: white;
    }
</style>

Send an email uses the output from Style HTML table and Create HTML table.

When the flow runs, we would get the following email.

[v-liwei-msft]

Hi @Anonymous 

Please try:

Best Regards,

Levi

[Anonymous]

[Anonymous]

Getting output like this

[Anonymous]

how to send this summary items only related to assignee to him. Right now whole list is sent irrespective of assignee.

[v-liwei-msft]

Hi @Anonymous 

Priority and Status are Choice columns causing this situation

Please try:

Best Regards,

Levi