Problem when comparing dates

[sbaggio]

Dear All,

Thank you in advance for your help.

My situation is the following:

1. I've set up a folder with a certain number of agreements.
2. I've set a time trigger so that every day the following are checked.
3. There is a column called End Date (date of expiration) and "Notice days" to be given in case I am not willing to proceed any more with the contract.
4. I've set a calculated column as follows End Date - Notice days - 20 days (named "Renewal Date").
5. I want that if today date is equal to "renewal date" then i receive a notification.
6. The flow is working if i set the condition "lessorequal" but I can't make it work when I set the condition as strictly equal.
7. I need to set it as "equal" because otherwise I will receive emails each day for any contract next to be renewed.
8. I am working on Sharepoint Online.
9. I believe the problem relies in the fact that "calculated colums" cannot be set as "date only", instead it always contain time, thus not triggering the condition when it's strictly "equal to".

Any suggestion/workaround?

Thank you again.

[gamoraes]

Hi @sbaggio

First of all, welcome to community

Steps to solve your problem:

 1- Use this expression inside an compose to get the day diference.

  OBS: Don't forget to change the red text to your right column name(if necessary)

sub(int(formatDateTime(utcNow(),'yyyyMMdd')),int(formatDateTime(triggerBody()?['Renewal Date'],'yyyyMMdd')))

2- After this put the output in your condition and you can use any condition 😄

OBS: In expression I'm using utcNow() if you need to use other timezone change to addHours(utcNow(),-1) where -1 is the difference to utc time, this value can be positive or negative.

Best Regards,

Gustavo Moraes

[sbaggio]

Hi @gamoraes, thank you for your reply.

Sorry but i think something is missing.

When i run the flow i get the following error.

[sbaggio]

This is my flow with your code implemented.

[gamoraes]

@sbaggio, in this item the column Renewal Date are null?

[gamoraes]

Looking your flow you need to drag the first Compose inside Apply to each, and change the expression to:

sub(int(formatDateTime(utcNow(),'yyyyMMdd')),int(formatDateTime(item()?['Renewal Date'],'yyyyMMdd')))

[sbaggio]

Some of them. Not all.

------

I'll explain better:

The list "document" is made by folders, i.e. Agency, Consultancies, insurances, etc. those do not have values in the columns.

Then you enter in the folders where the agreements are and there there are columns filled with informations.

Hope it helps.

[sbaggio]

[gamoraes]

Can you change your compose expression to 

item()

and post your result(compose output) here please?

Best Regards

[sbaggio]

I've removed users and company name for privacy purposes.

The structure is the same.

{
  "@odata.etag": "\"3\"",
  "ItemInternalId": "1",
  "ID": 1,
  "Renewal_x0020_date": "3799-12-09T23:00:00Z",
  "Modified": "2018-07-25T17:00:38Z",
  "Editor": {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
    "Claims": "i:0#.f|membership|UserName@company.com",
    "DisplayName": "User Name",
    "Email": "UserName@company.com",
    "Picture": "https://companyspa.sharepoint.com/agreements/_layouts/15/UserPhoto.aspx?Size=L&AccountName=UserName@...",
    "Department": "",
    "JobTitle": ""
  },
  "Editor#Claims": "i:0#.f|membership|UserName@company.com",
  "Today": "2018-07-26T07:00:00Z",
  "Deadline_x0020_for_x0020_Renewal": "3799-12-29T23:00:00Z",
  "Created": "2017-09-20T14:10:34Z",
  "Author": {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
    "Claims": "i:0#.f|membership|UserName@company.com",
    "DisplayName": "User Name",
    "Email": "UserName@company.com",
    "Picture": "https://companyspa.sharepoint.com/agreements/_layouts/15/UserPhoto.aspx?Size=L&AccountName=UserName@...",
    "Department": null,
    "JobTitle": null
  },
  "Author#Claims": "i:0#.f|membership|UserName@company.com",
  "{Identifier}": "Documents%252fAGENCY",
  "{Link}": "https://companyspa.sharepoint.com/agreements/Documents/AGENCY",
  "{Name}": "AGENCY",
  "{FilenameWithExtension}": "AGENCY",
  "{Path}": "Documents/",
  "{ContentType}": {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedContentType",
    "Id": "0x0120001B6A841DE0818842A8A53704F1154133",
    "Name": "Folder"
  },
  "{ContentType}#Id": "0x0120001B6A841DE0818842A8A53704F1154133"
}

Thank you and Best Regards

[gamoraes]

Aha!

Now we can do it!

New compose text:

sub(int(formatDateTime(utcNow(),'yyyyMMdd')),int(formatDateTime(item()?['Renewal_x0020_date'],'yyyyMMdd')))

[sbaggio]

Dear @gamoraes, Sorry for the late reply.

Unfortunatelly, it's still not working.

Now i get the following error in the condition action.

InvalidTemplate. Unable to process template language expressions for action 'Condizione' at line '1' and column '2529': 'The template language function 'lessOrEquals' expects two parameter of matching types. The function was invoked with values of type 'Integer' and 'String' that do not match.'.

The condition is actually set as: Compose output is equal to UtcNow('yyyyMMdd').

What am i missing? Thank you and best Regards

SB

[gamoraes]

Hi @sbaggio

I am here to help 🙂

your condition need to be the result of compose and the days, I remeber you said 20.

The expression that you have in compose return an interger with the difference in days(DateNow - RenewDate), you are comparing with a string when you put UtcNow()

In short this print compare if it's been 20 days of Renew_Date

[sbaggio]

Thank you for your explaination.

Now it's much clearer.

Yet i believe something is wrong.

The value i get in return from the compose (referring to the last row) it's 70 , while should be 0.

Of course i've double checked the format date structure which is yyyyMMdd.

Any guess?

Thank you.

[gamoraes]

Hi @sbaggio

Waht value you are getting in Flow for RenewDate you this last item?

the correct value in Flow may be yyyy-MM-dd or MM/dd/yyyy

One way to 'debug' is break the whole expression in multiple composes, to see the result of each part

[sbaggio]

hi,

The output of "get item" for the column Renewal date is the following:

"Renewal_x0020_date": "2018-07-30T22:00:00Z",

The corrispondent output in the compose reported is 71.

The formula of the compose is the one you posted before:

sub(int(formatDateTime(utcNow(),'yyyyMMdd')),int(formatDateTime(item()?['Renewal_x0020_date'],'yyyyMMdd')))

Can't figure out what the problem is.

Best Regards.

SB

[gamoraes · Accepted solution]

Hi @sbaggio

I change the strategy, i do a new expression for you

use this new function and the result will be correct:

div(sub(ticks(utcNow()),ticks(item()?['Renewal_x0020_date'])),864000000000)

Sorry for the long resolution, we are growing together

[sbaggio]

Hi!

Seems working now!

Thank you very much!!!!