Using Query Builder

Intended audience: USERS DEVELOPERS ADMINISTRATORS

AO Platform: 4.3

Overview

The Query Builder can be used to create simple or complex criteria-based configurations. It’s available in many parts of the AO Platform as well as in Solutions where applicable. The entry point for the Query Builder is the “funnel” icon typically found next to a field, like in this example:

When clicked, a dialog opens up allowing the user to enter any number of Rules and/or Groups of Rules as seen in the following example.

The first line: Match records for any rules in the group identifies a Group.
The lines following the Group header are referred to as Rules, consisting of…
- a dropdown to select a Field Property.
- a dropdown to select an Operator.
- a UI widget to allow the user to enter one or more Values to be evaluated. The Value field is followed by an Expression icon to facilitate dynamic expressions to be entered instead of absolute values. See Using Expressions for details. A select few Operators do not require a Value to be entered, such as Is Null, Is Not Null, Is True, Is False.

Click the trashcan icon to delete a Rule no longer needed.

Primary MSOs only or Connected MSOs

When selecting an MSO Property (the first dropdown in the rule configuration), the default is to only show Direct MSOs in the dropdown. If an MSO Property is required from a Connected MSO, then the on/off toggle for Show Connected MSOs in the top-right corner will need to be enabled. This is done as a performance consideration as it may take longer time to retrieve MSO Properties from all Connected MSOs in large Ontologies. The user will be prompted with a confirmation dialog each time the Show Connected MSOs toggle is changed.

Any / All Groups

The initial state of the Query Builder is that any Rule entered will be assessed independent of each other. This is due to the default Group using the “Any” keyword. In the above example, the Rules will be evaluated as:

“Show/select data records where (Asset Type Equals Meter) OR (Status Equals OFF)”

If on the other hand user changes the “Any” keyword for the Group to “All”, the Rules will be evaluated as:

“Show/select data records where (Asset Type Equals Meter) AND (Status Equals OFF)”

Additional Any / All Groups can be created allowing for increasingly complex evaluation of multiple Rules. In cases where multiple Groups are nested, the evaluation of Groups starts with the inner most Group, eg. in this example, Any (All (Rules), All (Rules)),

Operators

Operators are used to test for a “true” outcome in a criteria, eg. City Equals London means that if the Value (London) exists for the Property (City), then the result is “true” and whatever the criteria is configured to do will result in that data record being used. Some Operators can only be used with certain Data Types, eg. City is of type String, therefore the Operator Equals in the example can be used. Only Operators that can be used with a given Data Type will be shown in the Operator dropdown.

Operators	Used with Data of Type	Descriptions
Is Null	Date, Time, Numbers, String, Object, Geometry, Boolean	`NULL` is a reserved keyword used to represent missing or unknown values. Null is a state, rather than an actual value; it does not represent zero or an empty string. Use the `Is Null` operator to test whether a given value expression is Null, ie property’s value is empty.
Is Not Null	Date, Time, Numbers, String, Object, Geometry, Boolean	Tests for the opposite of `Is Null`, ie the property’s value is populated.
Equals	Date, Time, Numbers, String, Object, Geometry	Evaluates an exact match for the value provided for the property.
Not Equals	String, Object, Geometry	Tests for the opposite of `Equals`.
Equals Ignore Case	String	Evaluates a match for the value provided for the property not considering whether the string value is entered in upper/lower or mixed case.
Not Equals Ignore Case	String	Tests for the opposite of `Equals Ignore Case`.
Starts With	String	Evaluates an exact match for a string value specific to the start of the word/sentence.
Not Starts With	String	Tests for the opposite of `Starts With`.
Starts With Ignore Case	String	Evaluates a match for a string value specific to the start of the word/sentence whether the string value is entered in upper/lower or mixed case.
Not Starts With Ignore Case	String	Tests for the opposite of `Starts With Ignore Case`.
Ends With	String	Evaluates an exact match for a string value specific to the end of the word/sentence.
Not Ends With	String	Tests for the opposite of `Ends With`.
Ends With Ignore Case	String	Evaluates a match for a string value specific to the end of the word/sentence whether the string value is entered in upper/lower or mixed case.
Not Ends With Ignore Case	String	Tests for the opposite of `Ends With Ignore Case`.
Contains	String, Object, Numbers
Not Contains	String	Tests for the opposite of `Contains`.
Contains Ignore Case	String, Object, Numbers
Not Contains Ignore Case	String	Tests for the opposite of `Contains Ignore Case`.
Like	String	The `Like` operator is a logical operator that checks whether or not a string contains a specified pattern. Two wildcard characters can be used in the value: use _ to replace a specific character in the String use % to replace any number of characters in the String
Not Like	String	Tests for the opposite of `Like`.
Like Ignore Case	String
Not Like Ignore Case	String	Tests for the opposite of `Like Ignore Case`.
In	Date, Time, Numbers, String, Object, Geometry	When using the `In` operator the input format for the value is typically a list of values separated by the comma delimiter. If a value is expected to contain a comma, use double quotation marks around the value.
Not In	Date, Time, Numbers, String, Object, Geometry	Tests for the opposite of `In`.
Greater Than	Date, Time, Numbers	For a “true” response, only data records with value `Greater Than` the value provided will be returned, eg. Age 61 and above, if using the example.
Not Greater Than	Date, Time, Numbers	Tests for the opposite of `Greater Than`.
Greater Than Equals	Date, Time, Numbers	For a “true” response, data with value `Greater Than Equals` the value provided will be returned, eg. Age 60 and above, if using the example.
Not Greater Than Equals	Date, Time, Numbers	Tests for the opposite of `Greater Than Equals`.
Less Than	Date, Time, Numbers	For a “true” response, only data records with value `Less Than` the value provided will be returned, eg. Age 59 and below, if using the example.
Not Less Than	Date, Time, Numbers	Tests for the opposite of `Less Than`.
Less Than Equals	Date, Time, Numbers	For a “true” response, only data records with value `Less Than Equals` the value provided will be returned, eg. Age 60 and below, if using the example.
Not Less Than Equals	Date, Time, Numbers	Tests for the opposite of `Less Than Equals`.
Between	Date, Time, Numbers	For a “true” response, only data records with value `Between` the values provided will be returned, eg. Age 61 thru 69, if using the example.
Not Between	Date, Time, Numbers	Tests for the opposite of `Between`.
Between Inclusive	Date, Time, Numbers	For a “true” response, only data records with value `Between Inclusive` the values provided will be returned, eg. Age 60 thru 70, if using the example.
Not Between Inclusive	Date, Time, Numbers	Tests for the opposite of `Between Inclusive`.
Disjoint	Geometry
Not Disjoint	Geometry	Tests for the opposite of `Disjoint`.
Intersects	Geometry
Not Intersects	Geometry	Tests for the opposite of `Intersects`.
Touches	Geometry
Not Touches	Geometry	Tests for the opposite of `Touches`.
Crosses	Geometry
Not Crosses	Geometry	Tests for the opposite of `Crosses`.
Within	Geometry
Not Within	Geometry	Tests for the opposite of `Within`.
Overlaps	Geometry
Not Overlaps	Geometry	Tests for the opposite of `Overlaps`.
Is True	Boolean
Is False	Boolean
Matches	String
Matches Ignore Case	String

Contact App Orchid | Disclaimer