Filter Query question

(2) Share

Report

Posted on by Newbee321

Hi,

Can someone pls help me with below two items?

Problem statement - Find how many times a product has been already ordered in a calendar year and let warehouse person know the number so they can approve or deny.

I'm not sure how to write formula in Filter query but below is a SQL query I could write.

select count(*) from Orders
where ProductName = 'Bag' and year(OrderDate) = year(getdate())

1) can you please help me come up with the formula to write in Filter Query?

In attempt to create a simple Filter query - I tried : ProductName eq 'Bag'

Flow kicks off when a new item is created in the SharePoint list.
I’m using Get items -> Filter Query -> fx-> Dynamic content -> When an item is created – picked ‘ProductName’ column

Then receiving below error :
Action 'Get_items' failed: The expression "Makeup eq 'Bag'" is not valid. Creating query failed. clientRequestId: 209xxxxxxxx serviceRequestId: f9f7xxxxxxxxx

2) Makeup is the productname I tried to enter while testing the flow. What am I doing wrong here? Is that Get items need any particular action items before and after?

Categories:

Process Advisor

All responses (5)

Answers (0)

Newbee321 16 on at

Like (1)

Report

Filter Query question

venturemavenwil - I was able to Fetch Current year but not able to fetch year from the 'Date' column is something I'm stuck at.

I tried saving both years (Date field's and current year in separate variables and use them like this DateYear eq CurrentYear - getting an error.

When I type it out in Filter query itself then getting below error. Writing same functions in fx window also errors out. What am I missing here?

I really appreciate all your help!
Suggested answer

venturemavenwil 212 on at

Like (1)

Report
Filter Query question

@Newbee321 apologies: I don't literally mean yyyy-01-01. yyyy is supposed to be the year you are filtering. This can be a dynamic value you saved somewhere, or you can enter it manually. For example, if you want to get the current year, then use a dynamic value with the expression
formatDateTime(utcNow(),'yyyy')
Newbee321 16 on at

Like (1)

Report

Filter Query question

venturemavenwil Thanks for suggestions.

I'm still getting an error while trying to use Date column.

In SharePoint column type is Date and time, Include time -no, Friendly format - No, Default value -Today's date, data is stored in MM/DD/YYYY format.

Any idea what am I missing here?
Suggested answer

venturemavenwil 212 on at

Like (2)

Report
Filter Query question

So I would need to see the list setting to advice you on the proper Odata query to write, but based on your description, it sounds like you are selecting the dynamic content "ProdutName". This is false, and you would be pulling the "ProductName" value from your trigger, instead of performing a filter in the ProductName column

Instead, you should simply write

ProductName eq 'Bag'

as plain text in the Filter Query field

If you want to include the date filters, you should try the following

(ProductName eq 'Bag') and (OrderDate ge yyyy-01-01) and (OrderDate le yyyy-12-31)

Let me know if this helps with your issue.
Suggested answer

FLMike 29,105 on at

Like (2)

Report

Filter Query question

Hi,

You cannot do what you were doing in SQL, but you are close.

So first, you need to know the actual names of the Columns IN your table, NOT the Labels. What's the difference. The label is the string you see on the SharePoint list UI.

However, depending on how the list was created, it may NOT be called that or spelled the same.

Second, is your OrderDate a Date ONLY column or a DateTime, its important to know.

To do what you want.

1. You add your Get Items action

By default the action name is called Get Items, unless you already have one. The name is whats in the Header

-Set the List for this action

Let's pretend its Order Items since an order can have multiple things, I am NOT going to create the order stuff, I am skipping right to Order Items

2. In the Filter you would do essentially I would do what you asked (mostly)

And the stuff inside the '' is an expression NOT TEXT I simply type

OrderDate eq 'formatDateTime(utcNow(), 'dd-MM-yyyy')'

With the assumption that your Order Date as a Date formatted as dd-MM-YYYY

Now let's assume this filter works, cause it would :-)

Right afterwords you want to get the COUNT of how many matched. Unfortunately this is another action

Add a Compose

for the expression put

length(INSIDE_HERE_ADD_THE_DYNAMIC_PROPERTY_BODY/VALUE_FROM_THE_GET_ITEMS)

And it will return the number of rows. The number of Rows is what you want.

Now you can use an Email to send the information.

Now so what's missing???

Well what I showed you is a TOTAL number of things sold. If you want to find the number PER ITEM.. then its a whole other extra set of steps.