cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
llrudin
Level: Powered On

Filter Array not working using Ticks

I have a Sharepoint list that tracks the expiration date of insurance certificates. In this list I have a field called 'Expiration' which is defined as a date/time field that is formatted to just show the date. My goal is to create a daily digest email that lists the certificates that will expire within the next 40 days. My flow steps are:


1. Create the trigger. Run every day at 1 am.
2. Calculate the timestamp for 40 days from today (action is called Get_Future_Date)
3. Get items. This gets all records from my Sharepoint list where Expiration is greater than today as I only care about upcoming expirations.
4. Filter the array. This should give me a set of records that expire within the next 40 days.
5. Apply to each. This puts the output into HTML format.
6. Send an email. This puts the HTML table into an email that contains all the certificates that will expire within the next 40 days.

 

Everything works correctly in my Flow except for step #4 above. This is the query filter I am using:
@lessOrEquals(ticks(item()?['Expiration']), formatDateTime(ticks(body('Get_Future_Date'), 'yyyy-MM-dd')))

 

I get the following message: The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@lessOrEquals(ticks(item()?['Expiration']), formatDateTime(ticks(body('Get_Future_Date'), 'yyyy-MM-dd')))' failed: 'The template language function 'ticks' expects a single parameter that is a timestamp. The function was invoked with '2' parameters.

 

Does anyone know what is wrong with my query filter? From other posts I believe that to compare two dates you should use the Ticks function.   

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter Array not working using Ticks

Hi @llrudin ,

 

According to your description, I guess that you would like to filter out items that the expiration date is greater than Today and less than 40 days from Today.

 

 I have made the following flow for your scenario. The function used in the Filter array is:

@and(greaterorequals(formatdatetime(item()?['DueDate'],'yyyy-MM-dd'),utcnow('yyyy-MM-dd')),lessorequals(formatdatetime(item()?['DueDate'],'yyyy-MM-dd'),formatdatetime(body('Get_future_time'),'yyyy-MM-dd')))

In the action Create HTML table, select value from the Filter array, then input functions likes below for corresponding field:

 

title: item()?['title']

dueDate: item()?['duedate']

 

Images for your reference:

1.PNG

 

By the way, function Ticks is usually used to count differences between two days, here is an example on how it is used:

https://powerusers.microsoft.com/t5/Building-Flows/Find-length-of-time-between-two-date-time-values/...

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Filter Array not working using Ticks

Hi @llrudin ... Thank you for posting in the Flow community.

 

I think a filter would be a more suitable way for you to access "Expiration" when over 40 days.

Please see the below list:Capture-567.png

And the below Flow:Capture-568.png

addDays(formatDateTime(utcNow(), 'yyyy-MM-dd'), 40)

When I run this Flow it runs successfuly and returns 1 value below:Capture-569.png

I have configured this Flow the way you have - "In this list I have a field called 'Expiration' which is defined as a date/time field that is formatted to just show the date"

 

My format is 2019-06-22 hence why it is formatted above with formatDateTime(utcNow(), 'yyyy-MM-dd')

 

Please either rebuild this small test Flow like for like so you can test, then and/or apply the same logic to your Flow and I expect this will be what you are needing.

 

If you have found my post helpful, please mark thumbs up.

 

Any other questions, just ask.

 

Thanks, Alan

 


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Community Support Team
Community Support Team

Re: Filter Array not working using Ticks

Hi @llrudin ,

 

According to your description, I guess that you would like to filter out items that the expiration date is greater than Today and less than 40 days from Today.

 

 I have made the following flow for your scenario. The function used in the Filter array is:

@and(greaterorequals(formatdatetime(item()?['DueDate'],'yyyy-MM-dd'),utcnow('yyyy-MM-dd')),lessorequals(formatdatetime(item()?['DueDate'],'yyyy-MM-dd'),formatdatetime(body('Get_future_time'),'yyyy-MM-dd')))

In the action Create HTML table, select value from the Filter array, then input functions likes below for corresponding field:

 

title: item()?['title']

dueDate: item()?['duedate']

 

Images for your reference:

1.PNG

 

By the way, function Ticks is usually used to count differences between two days, here is an example on how it is used:

https://powerusers.microsoft.com/t5/Building-Flows/Find-length-of-time-between-two-date-time-values/...

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (6,047)