cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBI87
Helper I
Helper I

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

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:

 

PowerBI87_0-1597939384981.png

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jronash
Impactful Individual
Impactful Individual

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.

 

View solution in original post

8 REPLIES 8
CFernandes
Super User
Super User

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. 

 

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:

 

PowerBI87_0-1597948579229.png

 

Jronash
Impactful Individual
Impactful Individual

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.

just tried that and received the same error!

Jronash
Impactful Individual
Impactful Individual

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().

 

 

 

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.

 

PowerBI87_0-1597956619718.png

 

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)

 

PowerBI87_1-1597956671626.png

 

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.

 

PowerBI87_2-1597957033261.png

 

 

 

Jronash
Impactful Individual
Impactful Individual

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.

 

@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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Users online (4,112)