Answered

Filter on partial dates

(0) Share

Report

Posted on by JamesSnaps

187

I have a birthday checker running on our employee list at the moment, but it takes close to an hour to run as it has run a date checker on every item / employee on the list, checking their birthday which is in a DD/MM/YYYY format.

Is it possible to filter the 'get items' based on a partial date, i.e. just the DD/MM part?

I've tried setting up a calculated column that reads their birthday as DD/MM, but you can't filter on a calculated column, so that option failed.

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Sort by

v-yamao-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @JamesSnaps ,

Do you want to send a notification when today is employees’ Birthday?

Please take a try with the function formatDateTime to format the date to “dd/MM”.

I have made the following test for your reference.

In the list, there is a date column of type “yyyy-MM-dd”.

Trigger the flow with a button, then get items from the list.

Add a Condition with the following formulas:

formatDateTime(items('Apply_to_each')?['Date'],'dd/MM') is equal to utcNow('dd/MM')

Under If yes branch, send a mobile notification.

Image for your reference.

Best regards,

Mabel

Was this reply helpful? Yes No
JamesSnaps 187 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Mabel, I actually already have the flow doing exactly as you suggest. However I’m looking for a way to filter the initial ‘get items’ query. At the moment it has to get every item on the list before running the date check as a condition. So is there a way to use a filter query to only get the items that match the current day and month?

Was this reply helpful? Yes No
Verified answer

v-yamao-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @JamesSnaps ,

Thanks for updating.

I have made some research and test, I am afraid that it might be not possible to format the column you want to use in the Filter query field.

I also tried using a calculated column instead, however, calculated column is not supported in Filter query.

Further, you could also try with action Filter array instead of a Condition. Please check the following flow for a reference:

The function used in Filter array:

@equals(formatDateTime(item()?['Date'], 'dd/MM'), utcNow('dd/MM'))

Function used in Compose action is:

item()?['Title']

Best regards,

Mabel

Was this reply helpful? Yes No
JamesSnaps 187 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks Mabel, bit of tinkering and I've got this working, although I ended up filtering on my calculated column through the filter array action. The whole Flow now runs in under a minute instead of 45 of them.

Was this reply helpful? Yes No