How to Convert Date into String with Ticks (including null)

[PowerBI87]

Hi All,

I am trying to evaluate and compare between 2 dates. 

1. Today's Date

2. Specified Date in SharePoint Column

I have set up the flow as a start:

I have converted today's date into ticks in my third step. My issue/question is how can I convert the date in my SharePoint list into ticks in order to evaluate?

The variable is currently set to MilestoneProposed (These are the dates in my list that i want converted into ticks)

How can I write an expression that converts my SharePoint Field MIlestonProposed (Date Field) into ticks?

I have tried

ticks(triggerBody()?['RatingType'])

('RatingType' is internal column name for MileStoneProposed)

but this has failed b/c there are NULLS in the list.

Any ideas?

Much appreciated,

Kris

[CFernandes]

Hey @PowerBI87 ,

Can you please review the article https://sharepains.com/2018/11/02/microsoft-flow-compare-dates-using-conditions-in-the-sharepoint-co...

 I think it should help you achieve your requirement. 

[PowerBI87]

Thank for your reply. I have tried that but my issue is my date values are null until entered in by the user of the list.

When trying to ticks function this is the error i get:

[Jronash]

You could use the coalesce function to return a default value if your trigger value is null.

Something like this:

coalesce(ticks(triggerBody()['RatingType']),0)

That would return a value of 0 if the trigger value was null, but of course you could set it to whatever you want.

[PowerBI87]

just tried that and received the same error!

[Jronash]

Oops, two things.

First, you need to use the question mark when referring to your trigger value.

Second, we need to move ticks outside the coalesce function.  This also means that we need to set a default value that's a date, not a simple zero.  Here's an example that would return a ticked version of today's date if your trigger date is null.

ticks(coalesce(triggerBody()?['RatingType'],utcNow()))

You'll have to figure out what you want the default date to be, and put that in where I have utcNow().

[PowerBI87]

Thank you very much for this. The flow is actually completing now however, in my actual SharePoint list I tested by setting one date in the past, and one in the future.

I created a condition in the flow to see if i can do a less than condition. (Today is less than milestone Proposed by SFS date)

I put my default date if null to 01/01/1901 with the code below:

ticks(coalesce(triggerBody()?['RatingType'],formatdatetime('01/01/1901','dd/MM/yyyy')))

But in my output the tick value is the same for all steps. (even for dates I had inputted manually 08/19/2020 & 08/22/2020)

The tick value is '599581440000000000' for all rows, and not calculating the value for the non null values.

[Jronash · Accepted solution]

I'm a little confused - if you're using this expression inside a loop and expecting it to be different with each iteration, than you're pointing it at the wrong place.

triggerBody() is set by the trigger, so it's going to be the same every time you loop.

If you're wanting to refer to a value that is part of an array that you're looping through, you need to use item() instead of triggerBody().

I can't see enough of your flow to be able to tell exactly what you want to be referring to.  Is it a column from a SharePoint list?  If you're looping through a SharePoint list, you can refer to a column in an expression like so:

item()?['columnName']

Keep in mind that if your column name has a space in it, it might look more like this:

item()?['column_x0020_name']

If this doesn't make sense to you, let me know and I can try to walk you through.

[PowerBI87]

@Jronash Thank you so much for your explanation. I am new to flow and am not a software engineer by any means. Changing triggerbody ()t o item() has gotten the flow to do exactly what I intended it it to do.

Cheers,

Kris