CODEWITHSUNDEEP
sharepointpower-automate
Nikole
Nikole

Reputation: 25

How do I filter a Get Items query?

I have a SharePoint List that I would like to get items from and put into an e-mail. I only need to include items created within the day (for example, all items created on June 6, 2019; 00:00 - 23:59). I have been able to get the items and put them into an e-mail (with the use of an HTML table).

DateCreated is a calculated column that uses the Created date and formats it to be MM-dd-yy

I've been trying to filter a get items from SharePoint query and it always results to a "BadRequest" with Status Code 400. The outputted Body always says the expression is not available.

Whenever I use the Filter Query, no Dynamic Content pops up. I just use an expression to get the current date (timezone: Singapore) with utcNow().

When I run the flow without the Filter Query, it runs fine. All items are gotten and put into the HTML table and sent in the e-mail.

I just can't seem to get that darn Filter Query to work.

I've checked the column name in the URL, it comes out as Date_x0020_Created. I tried using that as a space and I tried a space too in the Filter Query, same results.

I tried using Filter Array but it either sends separate e-mails for each entry or sends all entries in one e-mail, both unfiltered.

I also tried initializing an array and then filtering the array. Didn't work either.

Date Created eq formatDateTime(convertFromUtc(utcNow(), 'Singapore Standard Time'), 'MM-dd-yy')

OUTPUTS

Body

{
  "status": 400,
  "message": "The expression \"Date Created eq '06-06-19'\" is not valid.\r\nclientRequestId: 19aa1a11-6c9a-4b1b-9644-b72fb54f3fa2\r\nserviceRequestId: 3ce6e39e-805f-8000-c610-ee1aa0245b80"
}

DateCreated Column Settings:
DateCreated Column Settings

My flow:
Flow

Upvotes: 0

Views: 3381

Answers (1)

Nikole
Nikole

Reputation: 25

I kinda figured it out. I had a feeling that Flow didn't want to retrieve the information from my DateCreated calculated column so I just made a single line of text column (DateMade) which is blank by default.

I made a Flow that gets triggered when an item is created, it uses the expression to get the current local date and updates the item to populate the DateMade column.

For the OData Filter Query, I typed in

DateMade eq formatDateTime(convertFromUtc(utcNow(), 'Singapore Standard Time'), 'MM-dd-yy')

It worked perfectly. I hope this helps someone in the future!

Upvotes: 0

Related Questions