Send an email reminder 5 days before date of calculated date

[Susan1313]

Hello, 

I am pretty new to Powerautomate, so sry if there has been a similar post already.

I am trying to create a reminder 5 days before a calculated date "Repeat Date" in a SharePoint list.

It is based on a two columns "days until repeat" and "Seminar Date" 

"Seminar Date" + "days until repeat"

As a template I used the already existing flow and changed the Filter from:

concat(concat('Seminar Date',' ge ', '''',outputs('Today_+_x_days')),'''',' and ', concat('Seminar Date',' lt ','''', outputs('x_Days_+_1'),''''))

to

concat(concat(Repeat Date',' ge ', '''',outputs('Today_+_x_days')),'''',' and ', concat('Repeate Date',' lt ','''', outputs('x_Days_+_1'),''''))

However I get the error, that calculated values can not be used in that filter.

To avoid this I tried to calculate the values inside the filter:

concat(concat('Seminar Date',' ge ', '''',DateAdd(outputs('Today_+_x_days'),-'days until repeat',"yyyy-MM-dd"),'''',' and ', concat('Seminar Date',' lt ','''', DateAdd(outputs('x_Days_+_1'),-'days until repeat',"yyyy-MM-dd"),''''))

But it just tells me that there is an error.

Can you help me with this?

[ScottShearer · Accepted solution]

@Susan1313 

I suggest re-writing your expression without the concat() expressions as they are not necessary and make the expression needlessly complicated.  You can have any number of individual expressions in the filter query window.  

Also, you need to use the internal names of SharePoint columns in your expression - internal column names do not have spaces. Seminar Date is not the internal name. 

It appears as though Days until repeat is a column in the list that you are trying to filter.  You can't use a column in the list that you are filtering in an expression.

One technique that you might consider is to break out your expressions into individual Compose actions before the Get items action.  Then reference the Compose actions in your filter query - that will make it easier to see what is going on...

I suggest reviewing this blog post and composing a new filter from scratch.  Post here if you have any issues.

[Susan1313]

Hallo Scott,

thank you I will try that.

However what do you mean by internal name?

Is there another name besides the column name? Some of the column names in my SharePoint list do have spaces.

So I am not sure what you mean when you say they don't have spaces.

Susan

[ScottShearer]

@Susan1313 

Every column in a SharePoint list has two names - the internal name and the display name.  The Display name is the name you see.  An easy way to find the internal name that usually works is to go to the list settings in SharePoint and lick on the column.  The internal name will be at the end of the URL after Field= .