Power Automate SharePoint Get Items Filter Query + 12 Examples

In this Power Automate tutorial, we will discuss the Power Automate SharePoint get items filter query. And also we will discuss the below points:

  • Power automate SharePoint get items filter query syntax
  • Power automate SharePoint get items filter query contains
  • Power automate SharePoint get items filter query and
  • Power automate SharePoint get items filter query date
  • Power automate SharePoint get items filter query or
  • Power automate SharePoint get items filter query not equal
  • Power automate SharePoint get items filter query lookup
  • Power automate SharePoint get items filter query choice
  • Power automate SharePoint get items filter query multiple conditions
  • Power automate SharePoint get items filter query yes/no
  • Power automate SharePoint get items filter query boolean
  • Power automate SharePoint get items filter query substring

Power automate SharePoint get items filter query syntax

Here we will see the general syntax of filter query we can use in SharePoint Get items action in Power Automate.

The general syntax for the Filter Query is

columnname operation value

Whereas column names that are used are the internal field names by SharePoint. The internal column name can be found within the column setting in SharePoint.

The operation can be listed below

  • Equal to (eq)
  • Less than (lt)
  • Greater than (gt)
  • Greater than or equal to( ge)
  • Less than or equal to(le)
  • No equal to (ne)

The above operation makes it easy to compare column value with the actual value. And sometimes we need to query multiple fields, so, we can use And or Or operations.

At last, the value is the value that you are comparing with the Field name. Make sure that you will need to use a single quote around a value.

Read Power Automate send email to SharePoint group

Power automate SharePoint get items filter query contains

Here we will see how to use substringof() in SharePoint get items filter query in Power Automate.

We will filter the data using filter query whether the field value contains that value or not, so for this, we will use the substringof().

So, for this example, I have created a SharePoint list called Event list and I will use the title column and the column type is a single line of text.

power automate sharepoint get items filter query contains
power automate SharePoint get items filter query contains

We will filter the items based on the Title column, whether the column contains the value ‘Meeting’ or not by using the substringof function.

The syntax for substringof function is

substringof  ('value', column)

In Power automate select Manually triggered flow, then click on Next step.

power automate sharepoint get items filter query contains
power automate SharePoint get items filter query contains

Select the Get items action, then provide the site address and list name. Then click on the Advanced option, in the Filter query field write the below query to get the items which title contains ‘Meeting’.

substringof('Meeting',Title)
power automate sharepoint get items filter query contains
power automate SharePoint get items filter query contains

To see the result we will create an HTML table, so click on Next step and select Create Html table action.

In the From Field, select the value of get items from the dynamic content. And then click on Advanced options, in the column field change the automatic to customs. After that map the field value to see the result.

Microsoft Flow sharepoint get items filter query contains
Microsoft Flow SharePoint get items filter query contains

Now click on Save and run the flow manually you can see the result in the output of Create HTML table action.

Microsoft Flow sharepoint get items filter query contains
Microsoft Flow SharePoint get items filter query contains

This is an example of power Automate SharePoint get items filter query contains.

Read Power Automate remove characters from a string

Power automate SharePoint get items filter query and

Here we will see how to use the and operation in SharePoint get items filter query using Power Automate.

Here we will use the SharePoint Event list to get the data i.e. title column contains ‘Meeting’ and Event date which is greater than today’s date.

power automate sharepoint get items filter query and
power automate SharePoint get items filter query and

In Power automate, select the Manually triggered Flow. Then click on the Next step.

power automate sharepoint get items filter query and
power automate SharePoint get items filter query and

Select the Get items action, and then provide the site address and list name. Then click on Show advanced options, In the Filter query field, write the below expression

substringof('Meeting',Title) and EventDate gt ' utcNow('yyyy-MM-ddTHH:mm:ssZ') '
power automate sharepoint get items filter query and
power automate SharePoint get items filter query and

To see the result of the filter query we will create an Html table, so click on the Next step and then select the Create Html table action.

Next, In the From Field select the value of get items from the dynamic content. And then click on Advanced options, in the column field change the automatic to customs. After that map the field value to see the result.

power automate sharepoint get items filter query and
power automate sharepoint get items filter query and

Now click on Save and run the flow manually, you can see the result in the output of the create HTML table.

Microsoft Flow sharepoint get items filter query and
Microsoft Flow SharePoint get items filter query and

This is an example of Power Automate SharePoint get items filter query and operation.

See also  SharePoint Rest API Microsoft.SharePoint.Client.InvalidClientQueryException The expression is not valid. Bad Request

Read Power Automate Array Variable + 15 Examples

Power automate SharePoint get items filter query date

Here we will see how to use the date column in SharePoint get items filter query using Power Automate.

We will use the Project management list and from this list, we want to fetch the projects that are in progress for the last 30 days.

Power automate SharePoint get items filter query date
Power automate SharePoint get items filter query date

In Power Automate, select the Manually triggered flow, then click on Next step.

Power automate SharePoint get items filter query date
Power automate SharePoint get items filter query date

Select the Get items action, and provide site address and list name from you want to fetch items. Then select the Show Advanced options, in the filter query field write the below expression.

StartDate ge 'fx(formatDateTime(addDays(utcnow(),-30),'yyyy-MM-ddThh:mm:ssZ'))' and StartDate lt 'fx( utcNow('yyyy-MM-ddTHH:mm:ssZ'))'
Power automate SharePoint get items filter query date
Power automate SharePoint get items filter query date

Now to see the filtered result we will create an HTML table, click on Next step and then select Create Html table action.

Next, in the From Field select the value of get items from the dynamic content. And then click on Advanced options, in the column field change the automatic to customs. After that map the field value to see the result

Power automate SharePoint get items filter query date
Power automate SharePoint get items filter query date

Now, click on Save and run the flow manually. We can see the results in the output of Create HTML table, that the project are in progress for the last 30 days.

Power automate SharePoint get items filter query date
Power automate SharePoint get items filter query date

This is an example of Power Automate SharePoint get items filter query date column.

Read Power Automate send email based on form response

Power automate SharePoint get items filter query or

Here we will see how to use or operation in SharePoint get items filter query in Microsoft Flow.

For this example, we will use the below Project Management list. So here we will filter based on person column type, that it will filter the projects that are assigned to user 3 and user 4, for this we will use or operation.

Power automate SharePoint get items filter query or
Power automate SharePoint get items filter query or

In Power Automate, select the Manually triggered flow, then click on Next step.

Power automate SharePoint get items filter query or
Power automate SharePoint get items filter query or

Select Get items action, then provide the site address and list name. Next click on Show advanced options, then in Filter query write the below query:

Assignedto/EMail eq '[email protected]' or Assignedto/EMail eq '[email protected]'
Power automate SharePoint get items filter query or
Power automate SharePoint get items filter query or

Now to see the result, we will create an Html table, so click on the Next step and select Create Html table action.

Then in the From Field select the value of get items from the dynamic content. And then click on Advanced options, in the column field change the automatic to customs. After that map the field value to see the result like below.

Power automate SharePoint get items filter query or
Power automate SharePoint get items filter query or

Now click on Save and run the flow manually. We can see the result in the output of the create HTML table.

Power automate SharePoint get items filter query or
Power automate SharePoint get items filter query or

This is an example of Power Automate SharePoint get items filter query or operation.

Read Power automate split string into an array with examples

Power automate SharePoint get items filter query not equal

Here we will see how to use the not equal(ne) operation in SharePoint get items filter query in Microsoft Flow.

Here we will use the below Project Management list, and we will filter the project those are assigned to other people except user 4. Whereas assigned to is a person column type.

Power automate SharePoint get items filter query not equal
Power automate SharePoint get items filter query not equal

In Power Automate select the Manually triggered Flow. Then click on the Next step.

Power automate SharePoint get items filter query not equal
Power automate SharePoint get items filter query not equal

Select the Get items-SharePoint action and provide the SharePoint site address and list name. And then click on Show advanced options, in the filter query field write the below expression

Assignedto/EMail ne '[email protected]' 
Power automate SharePoint get items filter query not equal
Power automate SharePoint get items filter query not equal

Now to see the result we will create an Html table, so click on the Next step and then select the Create Html table action.

Then in the From Field select the value of get items from the dynamic content. And then click on Advanced options, in the column field change the automatic to customs. After that map the field value to get the results accordingly in Html table.

Power automate SharePoint get items filter query not equal
Power automate SharePoint get items filter query not equal

Now click on Save and run the flow manually. We can see the result in the output of create HTML table action.

Power automate SharePoint get items filter query not equal
Power automate SharePoint get items filter query not equal

This is an example of Power Automate SharePoint get items filter query not equal operation.

Read Power Automate Rename File

Power automate SharePoint get items filter query lookup

Here we will see how to use the lookup column in SharePoint get items filter query in Microsoft flow.

I have created an Instructor Details list, in this list technology column is the lookup column, which is the lookup with the Technology list Title column. So here we will filter the SharePoint list items based on the lookup column i.e. Technology column.

Power automate SharePoint get items filter query lookup
Power automate SharePoint get items filter query lookup

And the Filter Query syntax for the lookup column is

InternalFieldName/Title operation 'value'

OR

InternalFieldName/Id operation 'value'

Internal Field name is the name of the lookup column in the list and Title or id is the column of the Targeted list.

See also  failed to create connection for connection id in Power Automate

Let’s see the steps to filter query SharePoint get items based on lookup column.

In Power Automate, select the Manually triggered Flow. Then click on the Next step.

Power automate SharePoint get items filter query lookup
Power automate SharePoint get items filter query lookup

Select Get items-SharePoint action, and provide site address and list name. Then click on Show Advanced Options, And in the Filter Query field write the below query to get the instructor name for technology Java and Python.

Technology/Title eq 'Java' or Technology/Title eq 'Python'
Power automate SharePoint get items filter query lookup
Power automate SharePoint get items filter query lookup

To see the results in Html table, click on the next step and then select Create Html table action.

Then in the From Field select the value of get items from the dynamic content. And then click on Advanced options, in the column field change the automatic to customs. After that map the field value to get the results accordingly in Html table.

Power automate SharePoint get items filter query lookup
Power automate SharePoint get items filter query lookup

Now click on Save and run the Flow Manually, we can see the results in the output of Create an Html table action.

Power automate SharePoint get items filter query lookup
Power automate SharePoint get items filter query lookup

This is an example of Power Automate SharePoint get items filter query lookup column.

Read Power Automate String Functions + 10 Examples

Power automate SharePoint get items filter query choice

Here we will see how to use the choice column in SharePoint get items filter query in Microsoft Flow.

I have a Project Management SharePoint list, from this list I will use the choice column i.e. Status column.

Here we will filter the projects those are Inprogress in the Project Management list using Microsoft Flow.

Power automate SharePoint get items filter query choice
Power automate SharePoint get items filter query choice

In Power Automate Select the Manually triggered Flow. Then click on the Next step.

Power automate SharePoint get items filter query choice
Power automate SharePoint get items filter query choice

Select Get items action, then provide the site address and list name where your list is present.

Then click on Show Advanced options, then in Filter Query field write the below query:

Status eq 'In Progress'
Power automate SharePoint get items filter query choice
Power automate SharePoint get items filter query choice

Next, to see the result we will create an HTML table, so click on Next step and then select Create Html table action.

In the From Field select the value of get items from the dynamic content. And then click on Advanced options, in the column field change the automatic to customs. After that map the field value to get the results accordingly in Html table.

Power automate SharePoint get items filter query choice
Power automate SharePoint get items filter query choice

Now click on Save and run the Flow manually. We can see the result in the output of the compose action.

Power automate SharePoint get items filter query choice
Power automate SharePoint get items filter query choice

This is an example of Power Automate SharePoint get items filter query choice column.

Read Power Automate send email from shared mailbox

Power Automate SharePoint get items filter query multiple conditions

Here we will see how to use multiple conditions in the Filter query to get items from the SharePoint list.

We will use the Project Management list, in this list we will use the Assigned to column and Status column to filter the items. And the column type of Assigned to and Status column is Person column type and Choice column type respectively.

To use multiple conditions in the Filter query we will use AND and OR operations. if we use the ‘and’ operation in between the condition, it will return true when both or more than one conditions are true.

And if we use ‘or’ operation, one of the condition is true then it will return true.

Power automate SharePoint get items filter query multiple conditions
Power automate SharePoint get items filter query multiple conditions

In Power Automate, select the Manually triggered Flow. Then click on the Next step.

Power automate SharePoint get items filter query multiple conditions
Power automate SharePoint get items filter query multiple conditions

Select the Get items-SharePoint action, then provide the site address and List name.

Next click on the Show Advanced options, then in Filter query write the below expression.

Status eq 'In Progress' and  Assignedto/EMail eq '[email protected]'
Power automate SharePoint get items filter query multiple conditions
Power automate SharePoint get items filter query multiple conditions

Now to see the result, we will create an Html table, so, click on the Next step, and then select Create an Html table action. Then, in the From Field select the value of get items from the dynamic content.

And then click on Show advanced options, in the column field change the automatic to customs. After that map the field value to get the results accordingly in Html table.

Power automate SharePoint get items filter query multiple conditions
Power automate SharePoint get items filter query multiple conditions

Now click on Save and run the Flow manually. We can see the result in the output of Create Html table.

Power automate SharePoint get items filter query multiple conditions
Power automate SharePoint get items filter query multiple conditions

This is an example of Power Automate SharePoint get items filter query multiple conditions.

Read Power Automate Parse Email Body

Power Automate SharePoint get items filter query yes/no

Here we will see how to use the Yes/No column in the SharePoint get items Filter query.

We have created a project management list in SharePoint, from this list we will use the Yes/No(boolean) column i.e. Delivered column to filter the items from the list. SharePoint Yes/No column is worked as true or false.

To make a working Filter query on the Yes/No(boolean) column in Power Automate, we have to use numbers i.e. for yes = 1 and no = 0.

Power automate SharePoint get items filter query yes/no
Power automate SharePoint get items filter query yes/no

In Power Automate, select Manually triggered flow, and then click on the Next step.

Power automate SharePoint get items filter query yes/no
Power automate SharePoint get items filter query yes/no

Select the Get items – SharePoint action, and then provide the site address and list name. Then click on the Show advanced option. In Filter query field write the below query to filter the items based on yes/no column i.e. ‘Delivered?’

Delivered_x003f_ eq '1'
Power automate SharePoint get items filter query yes/no
Power automate SharePoint get items filter query yes/no

To see the result we will create Html table, so click on the Next step and then select Create Html table action.

See also  Save site as template in SharePoint 2013/2016/2019/Online

Then in the From Field select the value of get items from the dynamic content.

And then click on Show advanced options, in column field change the automatic to customs. After that map the field value to get the results accordingly in Html table.

Power automate SharePoint get items filter query yes/no
Power automate SharePoint get items filter query yes/no

Now click on Save and run the Flow manually. We can see the result in the output of create Html table. As Yes/No column is a boolean type so it will return true.

Power automate SharePoint get items filter query yes/no
Power automate SharePoint get items filter query yes/no

This is an example of Power Automate SharePoint get items filter query yes/no column.

Read Power Automate Date Functions – 9 Examples

Power Automate SharePoint get items filter query boolean

Here we will see how to use the boolean column(yes/no) in Power Automate SharePoint get items filter query.

We will use the below Project management list, from this list we will filter the items that are not delivered.

So, we will use the Yes/ No column i.e. Delivered?. Once we get the not-delivered project we can send an email to the Manager.

Power automate SharePoint get items filter query boolean
Power automate SharePoint get items filter query boolean

In Power Automate select the Manually triggered Flow. Then click on the Next step.

Power automate SharePoint get items filter query boolean
Power automate SharePoint get items filter query boolean

Select Get items-SharePoint action, then provide the site address and list name. Next click on Show advanced options and then in Filter query Field write the below query to get the inprogress project.

Delivered_x003f_ eq '0'
Power automate SharePoint get items filter query boolean
Power automate SharePoint get items filter query boolean

Next, we will create a html table to send an email of inprogress or not delivered project.

So, click on the Next step, select Create Html table action. Then, in the From Field select the value of get items from the dynamic content.

And then click on Advanced options, in column field change the automatic to customs. After that map the field value to get the results accordingly in Html table.

Power automate SharePoint get items filter query boolean
Power automate SharePoint get items filter query boolean

Now, click on the Next step and select Send an Email(V2) action, and then set the To, and Subject Field. In the Body set the output of the create Html table action from the dynamic content.

Power automate SharePoint get items filter query boolean
Power automate SharePoint get items filter query boolean

Now click on Save and run the Flow manually. Once your flow ran successfully you can check your email.

Power automate SharePoint get items filter query boolean
Power automate SharePoint get items filter query boolean

This is an example of Power Automate SharePoint get items filter query boolean column.

Read Power Automate Get Data from Excel on SharePoint

Power Automate SharePoint get items filter query substring

Here we will see how to use the substring function in Power Automate SharePoint get items.

We will use the below Project Management list, in this list we will use the choice column i.e. Status column. So we will use the substring function to filter the Status column which contains completed string.

Power automate SharePoint get items filter query substring
Power automate SharePoint get items filter query substring

In Power Automate, select the Manually triggered Flow. Then click on the Next step.

Power automate SharePoint get items filter query substring
Power automate SharePoint get items filter query substring

Select Get items action, and then set the site address and list name. Next click on the Show advanced options, then in Filter query write the below query:

substringof('Completed',Status)
Power automate SharePoint get items filter query substring
Power automate SharePoint get items filter query substring

To see the result we will create an Html table, so click on the Next step and then select Create Html table action. Then in the From Field select the value of get items from the dynamic content.

And then click on Advanced options, in column field change the automatic to customs. After that map the field value to get the results accordingly in Html table.

Power automate SharePoint get items filter query substring
Power automate SharePoint get items filter query substring

Now click on Save and run the Flow manually. We can see the result in the output of the create an Html table action.

Power automate SharePoint get items filter query substring
Power automate SharePoint get items filter query substring

This is an example of power automate SharePoint get items filter query substring.

Related Power Automate tutorials:

In this Power Automate tutorial, we learned power automate SharePoint get items filter query. And also we discuss the below points:

  • Power automate SharePoint get items filter query syntax
  • Power automate SharePoint get items filter query contains
  • Power automate SharePoint get items filter query and
  • Power automate SharePoint get items filter query date
  • Power automate SharePoint get items filter query not equal
  • Power automate SharePoint get items filter query or
  • Power automate SharePoint get items filter query lookup
  • Power automate SharePoint get items filter query choice
  • Power automate SharePoint get items filter query multiple conditions
  • Power automate SharePoint get items filter query yes/no
  • Power automate SharePoint get items filter query boolean
  • Power automate SharePoint get items filter query substring
>