Today's date in a column as a trigger of the flow

[Jakub732]

Hi all, I'm struggling to make a usable flow, please help.

I have a column with calculated date. When the time comes and that date is today, I would like Sharepoint to send an email to a person assigned to that row, email to that person is provided in another column in the same row.

But the thing is that I need it to be the only trigger for that flow. No change in any other column should cause another email to be sent.

Moreover I would like that (or another) flow to send an email two working days in advance, so if the due date is on monday reminder email should arrive on thursday.

At first I've tried to set a rule, but for some reason that (calculated) column does not appear there to choose. Also I've been looking for a solution on this forum but can't find anything 😞

Please help, thanks in advance 🙂

[StretchFredrik]

Hello,

Can't you just do a flow that runs every day and checks the column with a IF statement to check if the date is today? 🙂 

[Jakub732]

I don't know, I've tried everything I know. Maybe I just don't know how to do that flow. That is why I need some help with how to create that flow.

[RobElliott]

@Jakub732 a very simple example is shown below. 2 items in my list are due to expire today, Bedar and Corvera:

Start your flow from blank and select Recurrence as the trigger:

Set it to run every 1 day, select your timezone and the time you want the flow to run:

Add a SharePoint get items action and select your site and list. We don't want to bring back every item, it is more efficient to add a filter query of Expiry eq  '' and in the Expression tab of the dynamic content box add the expression

formatDateTime(utcNow(),'yyyy-MM-dd')

Eq in the filter query means equals. Date comparisons are always done with the date format yyyy-MM-dd. Make sure the expression is inside single quotes.

Then add the email action. It will wrap itself inside an apply to each which is correct:

An email will be sent for each item, for example:

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[Jakub732]

Hi @RobElliott 

Thank you for your help. Unfortunately it didn't work either. 

At first I thought it is because of my regional settings, dates in column are displayed in UK style like "dd/MM/yyyy" so I've tried different variables in "expression" with no luck.

When my column name was "Data final bida" I would still get error at Get item, like this:

Than when I've changed column name to "Expiry" error was like this:

So I guess the fact that it is calculated column is the big problem, as it was with setting rule 😞

Perhaps you have any other solution to that problem?
I also tried to modify "reminder" flow but I couldn't figure out how to set it, to get only notifications from certain rows. That ones where my email is given in a "contact email column"?

Thanks in advance 🙂

[RobElliott]

As I said in my post "Date comparisons are always done with the date format yyyy-MM-dd". It doesn't matter what format you have in your list (and mine are also dd/MM/yyyy in the list), in the flow you must do a date comparison with yyyy-MM-dd.

And a calculated column can never be used in a filter query.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[Jakub732]

So is there any solution to my problem at all?

I know when I set a reminder in automate it works even with calculated column.

Only problem is that rows I'm interested in are like 5% of all, so getting a reminder for all doesn't satisfy me 😞

Can you help me with that?

[Mandica]

Hei,

Maybe Im bit late with it but im facing the same problem and found the solution in sharepoint.

After that you will have a templated flow that you can modify.

[Jakub732]

Hi Mandica,

Thanks, it is better late than never 🙂

That is actually how I managed to to resolve my problem.