Filter Query in Get items-
Within the SharePoint Get items action there is a Filter Query . This Filter Query can be used to select the required items. As a citizen developer, Filter query syntax is not clear and how do we use it?. So filter query syntax is like this-
Column Internal Name Operator Value
SharePoint Column Internal Name: For filter query it is important to use SharePoint Internal column name to fetch required list items. But some times users are not able to figure out what is SharePoint Internal column name?. To get Internal column name, Go to List/Library settings then click on your required column name.
Now see your URL last part which contains your SharePoint Internal column name. Technically it is ‘Column name in SharePoint List Schema’.
Operators: We use operators to compare field value either single or multiple. For multiple field values we use ‘and‘ or ‘or‘ operator.
- eq – Equal to
- ne- Not equal to
- lt- Less than
- gt- Greater than
- le- Less than or Equal to
- ge- Greater than or Equal to
- and – AND operator
- or -OR operator
Sometimes we use functions to get required result from filter query. Some important used to functions are-
- startswith – Example: startswith(InternalColumnName, ‘Gopenly’) => get all items where InternalColumnName starts with Gopenly.
- endswith – Example: endswith (InternalColumnName, ‘Gopenly’) => get all items where InternalColumnName ends with Gopenly.
- substringof – Example: substringof(‘Gopenly’,InternalColumnName) => syntax is different for substingof(). Here Supply your text first then internal column name.
- Contains- Currently, The function operator ‘contains’ is not supported by SharePoint Get items action.
Value : you will need to use single quotes (‘) around the values. Value could be static or dynamic content value.
Order by query:
To order items based on column either in ascending or descending order, we will specify an order by query. For example:
- InternalColumnName desc
- InternalColumnName asc
Note: Spaces in the column name are replaced with _x0020_ in Internal column name.
In my next article, I will cover examples of filter query.