cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
livingstonep
Level 8

How to filter items in SP List and count result?

We have an SP List called eBayIT_TrackMySales and in our flow we need to get a count of items in the list that match the trigger record on two columns called T-Code and BoxNo

 

I guess I need to do a GetItems step and filter but I don't know where to go from there?

 

How can I do that please?

1 ACCEPTED SOLUTION

Accepted Solutions
livingstonep
Level 8

Re: How to filter items in SP List and count result?

in the end I resolved this by using quotes correctly in the filter:

BoxNo eq '@{triggerBody()['BoxNo']}' and T_x002d_Code eq '@{triggerBody()['T_x002d_Code']}'

and to get the count I used

length(body('Get_Items')['Value'])

it now works fine...

7 REPLIES 7
Super User
Super User

Re: How to filter items in SP List and count result?

HI @livingstonep,

 

You can use the filter option in the Get items . For more details pleasee see my post on filtering on the get items action.

 

Once you get an array of items back. you can use the length function to count the items.

 

 

 

 

 

 

 

WDRC
Level: Powered On

Re: How to filter items in SP List and count result?

Pieter @Pieter_Veenstra

So your example stated for dates "Created gt ‘2018-11-28'".  If I have colum called "Current Contact Date" and want to filter by only items in the last seven days including the current date I would need the "Current Contact Date lt addDays(utcnow(),-7,'Current_x0020_Contact-x0020_Date') I have a live example I need to get working by this time next week so I can send something out with data from today through to the 15/01/2019.

Regards,

 

Andrew 

Community Support Team
Community Support Team

Re: How to filter items in SP List and count result?

Hi @livingstonep,

 

What is the type of T-Code and BoxNo column in your sharepoint list?

 

Bet regards,

Alice

livingstonep
Level 8

Re: How to filter items in SP List and count result?

hi, T-Code is a string BoxNo is numeric (integer) We keep getting an error when filtering the GetItems ... thanks
livingstonep
Level 8

Re: How to filter items in SP List and count result?

 

in the Get-Items step we're trying to filter on two columns, BoxNo (integer) and T-Code (string)

 

so the GetItems step will find all records from the list where the T-Code and BoxNo in the list match the T-Code and BoxNo of the current record that triggered the flow...

 

here is what we've tried:

GetItems Filtering.PNG

(T-Code column has internal name T_x002d_Code)

 

then we need to run an Apply to each for all records matched,, and set a field called LabelSent to "Yes"

 

but when we test the flow we get an error:

The expression \"BoxNo eq 5 && T_x002d_Code eq A02\" is not valid.\r\nclientRequestId: 4b983a8c-7ba5-4a5f-8702-7ed96c564723\r\nserviceRequestId: f697b49e-c0e0-7000-7d75-d83eaa4973de"

Super User
Super User

Re: How to filter items in SP List and count result?

There seems to be a problem with quotes

Also I would set a variable to the date -7 rather than trying to resolve all in one expression
Then finally use the ticks function to compare dates rather than date fields directly
livingstonep
Level 8

Re: How to filter items in SP List and count result?

in the end I resolved this by using quotes correctly in the filter:

BoxNo eq '@{triggerBody()['BoxNo']}' and T_x002d_Code eq '@{triggerBody()['T_x002d_Code']}'

and to get the count I used

length(body('Get_Items')['Value'])

it now works fine...

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 51 members 4,237 guests
Please welcome our newest community members: