Please help with a sharepoint data query which is driving me mad!

[Dhymeri10]

Hi,

I have a flow which I'm using to interrogate a sharepoint list.  The sharepoint list lists a number of entries to do with contract dates, and there are columns such as:

- Supplier name

- Contract renewal date (date format) e.g. 1/12/2022 (uk format)

- Notice period (number format) e.g. 90 days

I want to do something fairly simple.  I want to calculate a 'notice period date' which is the date before which we are able to cancel the renewal of a contract.  The 'notice period date' would be:

Contract renewal date minus Notice period e.g. 1/12/2022 minus 90 days = 2/9/2022

I then want the flow to send me an email notification 20 days before the notice period date to alert me that I may want to cancel the contract.

Can anyone help me work out how to do this pls?

[takolota]

@Dhymeri10 

It helps if you can share a picture of the flow you set up so-far.

But from your description you will very likely need an expression like

AddDays(InputDate, -90)

Or if the -90 is dynamic based on different contacts or something then you’ll want to do something like…

adddays(InputDate, sub(0, InputNoticePeriod))

[Dhymeri10]

Thanks for the quick response!  I haven't even started the flow yet because I can't get my head round how to set this up.  I'm guessing I need to use a Sharepoint 'get items' action, and then use a filter query to select the records where:

Contract renewal date - (notice period + 20 days) = today

1/12/2022 - (90 days + 20 days) = today

So for example I'd want to be sent an email notification on 2nd Sept 2022, which is 110 days before 1/12/2022

I've attached a screenshot of my sharepoint list in case it helps.

[Dhymeri10]

Just to add the notice period won't always be 90 days, depending on the supplier it could be 30, 60 or 90 days.

[takolota]

@Dhymeri10 

Okay for that I would probably create a SharePoint Calculated column for Contract Date + Notice Period

Then do a Scheduled flow to run once a day & do a Get items with a Filter Query set to 

CalculatedDateColumn lt ‘@adddays(utcnow(), 20)’

But if I remember correctly, then you may not be able to use a Calculated column in a query, so you may have to load all your possible data in the Get items & use a Filter array action on it afterwards.

[Dhymeri10]

Yes good idea.  I have actually been down this path already.  I have a calculated column in my sharepoint list which calculates the notice period date, and notification date.  However, as you've pointed out, calculated date columns seem to be an absolute nightmare to deal with in Power Automate, I've been trawling forums for a few weeks trying to get the notifications to work with the calculated date column.  That's why I'm wondering if there is another way to do it based on a normal date column.  The other option of course is to manually set the 'Notice period date' in the sharepoint list, but as we build up our list of suppliers that's going to be a pain to manually keep up-to-date.

[Dhymeri10]

OK I've just tried setting this up again, see below:

However, it's not working correctly, as one of the email notifications I've been sent has this data:

Vendor: Supplier 1

Notice period date: 2022-08-01T23:00:00Z
renewal date: 2022-10-31

The condition doesn't seem to be working properly, is it due to the date format do you think?

[takolota]

Is Notice period… dynamic content the same as contract date + notice period?

And you may want to remove the negative sign in adddays( )

[Dhymeri10]

Good spot, I've removed the - sign now in the expression.  This is how the 'Notice Period date' column in set in the Sharepoint list:

Sharepoint list showing calculated Notice Period date column:

[takolota]

Oh woops, I think I completely misunderstood that part. If the day you want the notice to go out is

contract date - renewal period 

not…

contract date + renewal period

Then to get the notification to trigger on the right date, wouldn’t you just need that

contract date - renewal date to equal utcnow()

??

[Dhymeri10]

No problem, yes I want to be notified 20 days before the Notice period date (contract date - renewal period). 

For example, if the vendor has the following details:

Contract renewal date: 1/12/2022

Notice period:  30 days

Notice period date: 1/11/2022

I would want to receive an email notification on 12/10/2022.  If that makes sense?

[takolota]

So in the example, assuming 2021 for that last date, it looks like you want Notice period date - Notice period equal to today

Or…

adddays(NoticePeriodDate, sub(0, NoticePeriod), ‘yyyy-MM-dd’) equal to utcnow(‘yyyy-MM-dd’)

???

[Dhymeri10]

Thanks for your continued help with this, I think we are getting there.  I think it's rather Notice period date - 20 days to equal today I need, what would be the odata filter query for that?

[takolota]

If Notice period date is date-time with hours & minutes and not just a plain date, then you should still be able to work around that with..,

Notice period date gt ‘@adddays(utcnow(), 19)’ and Notice period date lt ‘@adddays(utcnow(), 21)’

[Dhymeri10]

OK thanks, I tried entering that in the Filter Array action but got an error message - am I doing something wrong?

[Dhymeri10]

also this if I try to switch to basic editing mode:

[takolota]

I thought you might be doing that. Sorry for the confusion, but the Odata filter is what you use in the SharePoint action, that is different from the condition filter in a Filter array action.

The same logic in a Filter array would be…

@And(greater(InsertNoticePeriodDate, adddays(utcnow(), 19)), less(InsertNoticePeriodDate, adddays(utcnow(), 21)))

And you will need to replace those InsertNotice… bits with the actual references to your data.

It may be easier for you to just use the basic editor to put a true expression on one side and then write the and, greater, & less expressions in one big formula on the other side.

[Dhymeri10]

OK, I amended that code for my sharepoint fields:

@And(greater(Notice_x0020_period_x0020_date, adddays(utcnow(), 19)), less(Notice_x0020_period_x0020_date, adddays(utcnow(), 21)))

However, it still isn't accepting it:

I'm wondering if it's because you can't have a query using both gt and lt in a filter array, as it doesn't let me add two conditions when using the basic editor, I can only add one:

Sorry!

[Dhymeri10]

I really wish Microsoft could make an enhancement so that calculated sharepoint date fields are just treated like normal date fields, it's crazy that this should be so complex to do!

[takolota]

No, once you switch to advanced mode it’s ultimately just looking for a true or a false, it doesn’t really matter how you get there or how complicated it is.

There’s probably just some small syntax error.