How to compare two dates by month in a "get items" filter query

(0) Share

Report

Posted on by wiredupjax

195

I have been trying to solve the following simple problem:

- send an email with the current month employee's anniversaries

The filter needs to isolate the month (MM) only for each variable

I have settled on "Get items" with a "Filter query" but have been unsuccessful. Anytime I try and set MM only, it fails with the following error:

InvalidTemplate. Unable to process template language expressions in action 'Get_items' inputs at line '1' and column '2528': 'In function 'formatDateTime', the value provided for date time string 'OData__x0077_q99' was not valid. The datetime string must match ISO 8601 format.'.

It works when I do not try and format the variables (instead of "ge" it should be "eq" but I was playing with it)

Any ideas on how to make this work?

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

v-yuazh-msft on at

Like (1)

Report
Hi @wiredupjax,

Could you please share more details about the date column in your sharepoint list with expression "OData__x0077_q99" in flow?

You could take a try tp add a "Filter Array" action to filter the items meet your requirement.

I have made a test on my side to create a "OData 99" column in the sahrepoint list as screenshot below:

You could refer to screenshot below to create the flow:

The expression in the initial variable as below:

formatDateTime(utcNow(),'MM')

The expression in the Filter Array as below:

@equals(formatDateTime(item()?['OData_x0020_q99'], 'MM'), variables('Today_Date'))

The flow would run successfully as below:

Best regards,

Alice

Was this reply helpful? Yes No
wiredupjax 195 on at

Like (0)

Report

Alice, looks good - how do I take the filter array data send it in a single email? I only need a few columns from the record.

I've seen some other solutions on the board: here and here

However, when I begin to select certain fields, the "Apply to items" is automatically inserted, it drops the *body and switches items (which I switch back).

I cannot take the HTML table BODY field outside the Apply to items loop and insert it into an email. If I place the email inside the loop, then I get 10 emails instead of 1 with 10 items.

Was this reply helpful? Yes No
Verified answer

v-yuazh-msft on at

Like (1)

Report

Hi @wiredupjax,

Please refer to screenshot below:

Best regards,

Alice

Was this reply helpful? Yes No
wiredupjax 195 on at

Like (0)

Report

Correct! I missed that you need to chose fields as item()?['FIELDNAME']
Thank you so much!

Was this reply helpful? Yes No