"Get items" filter two columns at once

(0) Share

Report

Posted on by Community Power Pla...

Hello!

I want to filter my SP list based on two conditions:

Machine Value = "ZT1"
Date = current day (in this case: 3/23/2023)

In the Filter Query of the "Get Items" action, I inserted the following code:
Machine Value eq 'ZT1' and Date eq '@{convertTimeZone(utcNow(),'UTC', 'E. South America Standard Time')}'

But the following error appears for me when I run:

The expression "Machine Value eq 'ZT1' and Date eq '23/03/2023'" is not valid. Creating query failed.

How can I make this Filter Query filter these two columns at the same time correctly?

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (0)

Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report

Your column name (Machine Value) has to be escaped.
Probably to Machine_x0020_Value. Just check the output of the action.

Was this reply helpful? Yes No
David_MA 12,966 Super User 2025 Season 2 on at

Like (0)

Report
In addition to what @Chriddle said, you will have some issues with your date filter. Dates need to be in ISO8601 format, which would be yyyy-MM-dd as opposed to your dd/MM/yyyy format. The next issue is that it is unlikely that your Date field is going to equal (eq) the current date UTC datetime converted to the time zone specified.

What you are most likely looking for are items where the date is the current date. To do that, you will want to use for the date portion of your filter query:

Date ge convertTimeZone(startOfDay(utcNow()),'UTC','E. South America Standard Time') and Date lt addDays(convertTimeZone(startOfDay(utcNow()),'UTC','E. South America Standard Time'),1)

This way it is looking for all items created between midnight of the current day and before midnight of the following day. When you pass you Date field in the filter query, it will include a date and time, so that makes it nearly impossible to ever use eq (equals).

As long as your list item is a SharePoint date field, you really don't need to worry about using formatDateTime as by default it will pass the value in ISO8601 format. You'll only need to be concerned about it if you pass a text string formatted as you illustrated (23/03/2023).

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
So my Filter Query should look like this?

Machine_x0020_Value eq 'ZT1' and '@{Date ge convertTimeZone(startOfDay(utcNow()),'UTC','E. South America Standard Time') and Date lt addDays(convertTimeZone(startOfDay(utcNow()),'UTC' ,'E. South America Standard Time'),1)}'

Was this reply helpful? Yes No
David_MA 12,966 Super User 2025 Season 2 on at

Like (0)

Report

That looks correct to me... Of course, the only way to know will be to test it. 🙂

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Automate does not even let me create this expression:
Date ge convertTimeZone(startOfDay(utcNow()),'UTC','E. South America Standard Time') and Date lt addDays(convertTimeZone(startOfDay(utcNow()),'UTC','E. South America Standard Time'),1)

Could you please share the entire code that you created in a visual way?

Was this reply helpful? Yes No
David_MA 12,966 Super User 2025 Season 2 on at

Like (0)

Report

Here is an example form one of my flows:

If your Filter query does not show the expressions in pink and you have the values you listed above in the filter query, that is your problem. You need to use the expression builder to build the expression and insert it into the Filter query.

If that doesn't work, you will need to show what you are doing in your workflow.

I tested the expressions I provided earlier and they work correctly:

Was this reply helpful? Yes No