Keyword Query Language
EasyAP365 Search is based on SharePoint Search, which uses Keyword Query Language (KQL) for constructing and executing search queries. A KQL query consists of one or more of the following elements:
- Free-text keywords (search terms) – words or phrases
- Property restrictions
- Operators
Free-text keywords
Free-text KQL queries are case-insensitive. You can construct KQL queries by using one or more of the following as free-text expressions:
- A word (includes one or more characters without spaces or punctuation)
- A phrase (includes two or more words together, separated by spaces; however, the words must be enclosed in double quotation marks)
When you use words in a free-text KQL query, Search in SharePoint returns results based on exact matches of your words with the terms stored in the full-text index. You can use just a part of a word, from the beginning of the word, by using the wildcard operator (*) to enable prefix matching. In prefix matching, Search in SharePoint matches results with terms that contain the word followed by zero or more characters.
Example – return all items that contain the word:
fabrikam
Example – return all items that contain words starting with:
serv*
When you use phrases in a free-text KQL query, Search in SharePoint returns only the items in which the words in your phrase are located next to each other. To specify a phrase in a KQL query, you must use double quotation marks.
Example – return all items that contain the phrase:
“image makers”
To construct complex queries, you can combine multiple free-text expressions with KQL query operators. If there are multiple free-text expressions without any operators in between them, the query behavior is the same as using the AND operator. Note that, KQL queries are case-insensitive but the operators are case-sensitive (uppercase).
Example – return all items that contain both words image and makers (words order is irrelevant):
image makers
is equivalent to
image AND makers
Using of operators will be explained in more details in further section.
Property restrictions
Using KQL, you can construct queries that use property restrictions to narrow the focus of the query to match only results based on a specified condition. Currently EasyAP365 Search supports only two properties: title and author. Title can be used to focus the query to match results by vendor ID or invoice number. Author focuses the query to match results by the creator of the list item. A basic property restriction consists of the following:
<Property Name><Property Operator><Property Value>
The property restriction must not include white space between the property name, property operator, and the property value, or the property restriction is treated as a free-text query. The length of a property restriction is limited to 2048 characters.
Valid property operators for property of type Text are : and <>, where the meaning of the operators is equal and not equal, respectively:
title:inv202301-0010
title<>imagemak0001
Example – return all items for vendor with ID ADVANCED0001:
title:advanced0001
Example – return all items with invoice number starting with INV202301:
title:inv202301*
Example – return all items created by John Smith:
author:”john smith”
Example – return all items except these created by Jane Smith:
author<>”jane smith”
Search in SharePoint supports the use of multiple property restrictions within the same KQL query. You can use either the same property for more than one property restriction, or a different property for each property restriction.
When you use multiple instances of the same property restriction, matches are based on the union of the property restrictions in the KQL query. Matches would include content items authored by John Smith or Jane Smith, as follows:
author:”john smith” author:”jane smith”
This functionally is the same as using the OR Boolean operator, as follows:
author:”john smith” OR author:”jane smith”
When you use different property restrictions, matches are based on an intersection of the property restrictions in the KQL query, as follows:
title:imagemak0001 author:”john smith”
This is the same as using the AND Boolean operator, as follows:
title:imagemak0001 AND author:”john smith”
You may use parenthesis () to group multiple property restrictions related to a specific property of type Text with the following format:
<Property Name>:(<Expression>)
More advanced queries might benefit from using the () notation to construct more condensed and readable query expressions.
The query:
author:”john smith” AND author:”jane smith”
can be rewritten as:
author:(“john smith” “jane smith”)
The query:
title:advanced0001 title:imagemak0001 NOT title:apogee001
can be rewritten as:
title:((advanced0001 OR imagemak0001) -apogee001)
Operators
You use Boolean operators to broaden or narrow your search. You can use Boolean operators with free-text expressions and property restrictions in KQL queries.
- AND – returns search results that include all of the free-text expressions, or property restrictions specified with the AND You must specify a valid free-text expression and/or a valid property restriction both preceding and following the AND operator. This is the same as using the plus (+) character.
- OR – returns search results that include one or more of the specified free-text expressions or property restrictions. You must specify a valid free-text expression and/or a valid property restriction both preceding and following the OR
- NOT – returns search results that don’t include the specified free-text expressions or property restrictions. You must specify a valid free-text expression and/or a valid property restriction following the NOT This is the same as using the minus (-) character.
Examples:
fabrikam AND “rent expense” equivalent with fabrikam+”rent expense”
“rent expense” OR “interest expense”
NOT “rent expense” equivalent with -“rent expense”
-contoso equivalent with NOT contoso
-fabrikam AND (“advertising expense” OR “rent expense”)
-(interest OR rent) AND “image make*”
When using plus (+) character or minus (-) character, white space is not allowed between the operator and the expression.
Example:
-fabrikam+”rent expense”
You use the wildcard operator – the asterisk character (*) – to enable prefix matching. You can specify part of a word, from the beginning of the word, followed by the wildcard operator, in your query, as follows. This query would match results that include terms beginning with “serv”, followed by zero or more characters, such as serve, server, service, and so on:
serv*
KQL queries don’t support prefix matching with the wildcard operator (*) as prefix. The following query is not valid:
*pense
Examples:
fabric* “adv* expense”
contoso OR “adv* expen*”
“advanced office sys*”
“advanced *ice systems” [this query is not valid]
You can combine different parts of a keyword query by using the opening parenthesis character ( and closing parenthesis character ). Each opening parenthesis ( must have a matching closing parenthesis ). A white space before or after a parenthesis does not affect the query.
Examples:
author:(“john smith” “jane smith”)
title:((advanced0001 OR imagemak0001) -apogee001)
-fabrikam AND (“advertising expense” OR “rent expense”)
-(interest OR rent) AND “image make*”
EasyAP365 Search Query Extension
When you search using common used terms, the returned results can cover a large range of items that are not relevant for your need. One way to narrow down the results is by using property restrictions.
Currently EasyAP365 Search supports only two properties: title and author. These properties are internally provided by SharePoint. Configuring and using other properties requires manual setup in the client environment, while the goal of the current implementation of the EasyAP365 Search functionality is to be ready for use with a minimal user interaction. Another inconvenience is that sometime SharePoint map author property to the person who last modified the list item, which may not be the person who created it, and also, if the invoice is auto-created, the author property points to the system account. So, searches by author will not return the desired results.
To overcome these limitations, some additional keywords and phrases are implemented to enable narrowing down the returned results. For example, to return all invoices created by John Smith, use the following free-text phrase:
“contributor John Smith”
KQL queries are case-insensitive, so the above query and the following query will produce the same results:
“contributor john smith”
All of the rules of the KQL, explained in the above sections, applies also for the queries which use the EasyAP365 Search additional keywords and phrases.
Example – narrows down the results by invoice type:
“type invoice”
“type matchshipment”
“type matchpo”
“type notdefined”
Example – using operators:
“type invoice” “type notdefined”
is equivalent to
“type invoice” AND “type notdefined”
Example – return all invoices of type either Shipment Match or PO Match:
“type match*”
is equivalent to
“type matchshipment” OR “type matchpo”
Example – narrows down the results by company(-ies):
“company <company_name>” [common pattern, replace <company_name> with actual name]
“company cronus usa”
“company cron*”
“company fabrikam” OR “company contoso”
Example – narrows down the results by currency(-ies):
“currency <currency_code>” [common pattern, replace <currency_code> with actual code]
“currency usd”
“currency usd” OR “currency eur”
In general, all other additional keywords and phrases follow the same pattern. Here are some more examples:
“vendor image makers”
“vendor fabrik*”
“status not submitted”
“company cronus usa” “vendor advanced” (“currency usd” OR “currency eur”)
“company fabrikam” -(“vendor image” OR “vendor first”) “status under review”
“contributoremail jsmith@contoso.com”
“vendoraddress primary” OR “vendoraddress warehouse”
“vendoraddress ware*”
If you do not know the vendor address ID and want to search by other parts of the vendor address, omit the enclosing double quotation marks:
vendoraddress sherwood
vendoraddress “black river”
vendoraddress sher* arlington
vendoraddress “park s*”
Omitting the enclosing double quotation marks is necessary whenever you are not sure for the first words in the sentence in which you expect to find a match. In general, this is the case for searches in text fields like header and item descriptions, notes, audits, and so on.
headertext <search_term>
itemtext <search_term>
notetext <search_term>
audittext <search_term>
jobtext <search_term>
jobtasktext <search_term>
categoryvalue <search_term>
More examples of additional keywords and phrases:
“template <template_name>” [either In App or Auto-Create]
“template conto-1010”
“template conto*”
“template ac-fab-101”
“template ac-fab*”
“category <category_name>”
“categoryvalue <category_value>”
“category department”
“categoryvalue sales”
“category expense type”
“categoryvalue rent expense” OR “categoryvalue interest expense”
categoryvalue expense [omitting the enclosing double quotation marks for all expense types]
“memparent <mem_value>”
“memparentvalue <mem_integration_value>”
“memchild <mem_value>”
“memchildvalue <mem_integration_value>”
“workflow <workflow_value>”
“workflowvalue <workflow_integration_value>”
“udf <field_name>”
“udfvalue <field_value>”
“file <file_name>” [match invoices with <file_name> of either file attachment or support document, or both]
“note john smith”
“audit jane smith”
“itemid bw-sx-28”
“itemtext bike wheel”
“account 000-6530-10”
“account 000-65*”
“receiptnumber <receipt_number>”
“ponumber <po_number>”
“job <job_number>”
“jobtext <job_description>”
“jobtask <jobtask_number>”
“jobtasktext <jobtask_description>”