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

Create a Task - Due Date - Input value if it exists, input null otherwise?

Hello,

 

I am trying to create a Flow that reads Planner Cards and moves the data from them into an Excel table.

 

Next, I want to read this table to create cards in the sub-plans.

 

Goes fine, until there is a Card without a due date. If there is no due date, the flow fails on Create a Task.

 

I understand the logic I need - input a value if there is one, if there is no due date then move on and leave the field blank - but I am not finding a good way to write that logic.

 

It seems like once I hit 'Expression' to start building one, my Dynamic Content menu shrinks significantly. If this Dynamic Content would stay in its expanded form, I think I can get it to work. But I struggle to write expressions here.

 

I am following other blogs, but then they use 'generic' examples for their expressions and I can't really understand it.

https://tomriha.com/how-to-format-a-date-that-can-be-empty-null-in-power-automate/

 

I would THINK it would go something like...

 

if(items('Apply_to_each_2')?('''due date')....equals null? - if so, set value to null, if not, set value to items('Apply_to_each_2')?('due date') - right??

 

You can see where my understanding of these expressions breaks down. 

 

The field from the table in question is called: 'due date'

 

Please let me know if any ideas.

-R

1 ACCEPTED SOLUTION

Accepted Solutions
Ankesh_49
Super User
Super User

if(empty(items('Apply_to_each_2')?['due date']),utcnow('MM-dd-yyyy'),items('Apply_to_each_2')?['due date'])

View solution in original post

11 REPLIES 11
Ankesh_49
Super User
Super User

@rnavrot I would recommend you to use compose action to begin with. It will help you to zero in on the output value which you want to use in your expression. For ex. get this "items('Apply_to_each_2')?('''Sub Plan' Title')" into a compose action and try to analyse the output. It will help you in framing your expression easily. one or may be two tries you will nail it.

Hope it Helps!!

Should you require any further assistance, do let me know.

Thanks

Ankesh

rnavrot
Helper I
Helper I

Ankesh - I just realized I gave the wrong fields in question - I am actually looking for 'due date' rather than '''Sub Plan' Title'. Whoops! I will edit the main message to reflect the correct fields.

 

Though I am happy to try out Compose-ing up my 'due date' field to see what comes from it. Thanks for the suggestion.

 

-R

Ankesh_49
Super User
Super User

For reference: Few expressions which I have used

 

if(empty(items('Apply_to_each')?['Cyber Security Target** ']),Null,addDays('1899-12-30',int(items('Apply_to_each')?['Cyber Security Target** ']),'dd/MM/yyyy')


if(empty(items('Apply_to_each')?['Literature']),Null,items('Apply_to_each')?['Literature'])

 

Hope it helps!!

 

Cheers,

Ankesh

--------------------------------

If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.




This does help - my formula looks like below:

 

if(equals(items('Apply_to_each_2')?['due date'],''),'1900-01-01T00:00:00Z',items('Apply_to_each_2')?['due date'])
 
...But my next error is when this default value (Jan 1, 1900) is tried to input I get 'BadRequest.'
 
rnavrot_0-1660679740642.png

 

I believe the error has to do with the blank due date, as this is the only run in my Apply to Each of ~50 that failed. When the due date was not blank and used the value from the table, the remaining runs have succeeded.

 

Any insight on the 'BadRequest'?

 

Thank you!

Ankesh_49
Super User
Super User

try this:

if(empty(items('Apply_to_each_2')?['due date']),formatdatetime(utcnow(),'dd/MM/yyyy'),items('Apply_to_each_2')?['due date'])

rnavrot
Helper I
Helper I

Impressive that you can write expressions for me to paste. 

 

Admittedly, I like this format a lot better. (I am American though, so I will have to switch the day and month, like an American would). I am getting a bit of a different error message now - apparently overcoming the 'BadRequest' - here is what I see now.

 

rnavrot_0-1660683266052.png

 

Same Cards (with blank due dates in my Excel table) are causing the errors, at least. Progress!

 

Thoughts?

Ankesh_49
Super User
Super User

if(empty(items('Apply_to_each_2')?['due date']),utcnow('MM-dd-yyyy'),items('Apply_to_each_2')?['due date'])

Ankesh_49
Super User
Super User

@rnavrot Is the issue resolved?

rnavrot
Helper I
Helper I

My Flow has succeeded! Thanks to you, Ankesh.

Ankesh_49
Super User
Super User

@rnavrot   Awesome!! Glad it helped. 

Thanks

Ankesh

rnavrot
Helper I
Helper I

Alright Ankesh, one more question here for you.

 

if(empty(items('Apply_to_each')?['due date']),utcnow('MM-dd-yyyy'),items('Apply_to_each')?['due date'])

 

Using this formula, how can I modify it so that the due date extends out, say, 100 years?

If the cards are duplicated with utcnow as the due date, the cards will appear overdue almost immediately. I would rather that they appear due years from now, so that when people see the card they do not think it is overdue.

 

Please let me know if you can help, thank you.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (2,803)