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

Automate does not flow

i have developed a flow using automate to send a monthly email.  within my sharepoint site i have recently changed on of the columns names from 'Expiry of Inspection' to 'Expiry Date'.  However when i test the flow it says that expiry date does not exist.

Flow below in pic.

cheers1903_2-1653505643800.png

cheers1903_3-1653505663045.png

 

 

cheers1903_0-1653505512894.png

 

I have used an expression for the expiry date and this seems to be the problem.

formatDateTime(item()?['ExpiryDate'],'dd/MM/yyyy')

 

this expression has worked for every other process flow i have done. 

Pic of sharepoint site below with column header

cheers1903_1-1653505542841.png

 

any help would be great

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @cheers1903 ,

 

@vivekpande18 has a valid point.  A date might be empty?  To prove this, change your expression from formatDateTime(item()?['ExpiryDateofInspection'],'dd/MM/yyyy') to item()?['ExpiryDateofInspection'] and then see if you have no dates (in which case wrong expression) or a column with a missing date i.e. null.  You then need to decide what logic you require if the date is null.

 

return today's date if null:

formatDateTime(coalesce(item()?['ExpiryDateofInspection'],utcnow()),'dd/MM/yyyy')

 

OR if date null return "no date" else return date formatted

if(equals(item()?['ExpiryDateofInspection'],null),'No Date',formatDateTime(item()?['ExpiryDateofInspection'],'dd/MM/yyyy'))
 
Damien

View solution in original post

7 REPLIES 7
DamoBird365
Super User
Super User

Hi @cheers1903 

 

For a filter query, you need to use the internal name of the column.  To find this, go to SharePoint, click on the cog (top right) list settings, select your column name and then look at the address bar.

 

DamoBird365_0-1653507736314.png

 


Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts. Thanks

I have updated the names as to how you said but when i use the expression 

 

formatDateTime(item()?['ExpiryDateofInspection'],'dd/MM/yyyy')

 

cheers1903_0-1653568747522.png

i get the error above.  
 
if i only put in dynamic content for Expiry Date of Inspection, the process works fine, no problem.  It seems that the expression is not correct. 
 
cheers1903_1-1653568855626.png

 

Hi @cheers1903 

 

The error you described before appeared to relate to the filter of get items as you had renamed a column and the Odata query uses internal names.  The internal names are for your filter.  Where you had expirydate eq 'formatdatetime(etc)'. 

 

You probably had your expressions correct for the create html table.  These are based on the key names that you will see in the output history from get items.  You should be able to show raw outputs in the flow history and see the names you need.  Alternatively you can use parse JSON, but there is value in understanding how to contruct an expression.

 

Damien

i have changed the filter query in get items to below:

cheers1903_0-1653573407910.png

and the html table to below:

cheers1903_1-1653573446061.png

with the expression:

formatDateTime(item()?['ExpiryDateofInspection'],'dd/MM/yyyy')
 
but i still get the failed 
cheers1903_2-1653573490514.png

 

i have kept the column name as 

 

i have also renamed the sharepoint column name as original

cheers1903_3-1653573562179.png

 

no idea what i need to do to make it work

 

vivekpande18
Solution Supplier
Solution Supplier

Hi @cheers1903 , 

 

There are 2 reasons why your flow is failing, 

 

1- your ExpiryDateofInspection column might not have any value for the item on which your flow is running. 

 

2- Colum  internal name might be wrong. 

 

Please check above 2 points to tackle the issue. 

 

Thanks

Vivek Pande

Hi @cheers1903 ,

 

@vivekpande18 has a valid point.  A date might be empty?  To prove this, change your expression from formatDateTime(item()?['ExpiryDateofInspection'],'dd/MM/yyyy') to item()?['ExpiryDateofInspection'] and then see if you have no dates (in which case wrong expression) or a column with a missing date i.e. null.  You then need to decide what logic you require if the date is null.

 

return today's date if null:

formatDateTime(coalesce(item()?['ExpiryDateofInspection'],utcnow()),'dd/MM/yyyy')

 

OR if date null return "no date" else return date formatted

if(equals(item()?['ExpiryDateofInspection'],null),'No Date',formatDateTime(item()?['ExpiryDateofInspection'],'dd/MM/yyyy'))
 
Damien

thats great thanks the following expression worked

 

return today's date if null:

formatDateTime(coalesce(item()?['ExpiryDateofInspection'],utcnow()),'dd/MM/yyyy')

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.

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.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Users online (1,666)