Query Rules

A query rule is a defined query against a single data store entity. Parameters can be used to filter or page the array of data returned.

Query rules can be called from any expression, so they can be reused across multiple objects throughout the system.

Each time you modify and save a query rule, a new version is created. All expressions that use the rule will use the latest version. All versions are accessible to designers who can view the rule, and a rule can be reverted back to a previous version at any time.

Designers may also use the a!queryentity() and a!queryrecord() system functions to perform similar queries. See here to learn more about deciding which to use.

Create

New query rules are created from the context of an application. Use the New menu within an application to create a new query rule and open it in the query rule designer.

Defining Rule Inputs

A rule input requires that an additional value be provided when the rule is executed.

  • You must create a rule input if you want to use conditions in your query rule.

Rule inputs for query rules differ from those used by expression rules in that they are not prefaced with ri!. They also support fewer data types.

To define a query rule input, complete the following:

  1. When creating or editing your query rule, click Add Rule Input. The New Rule Input group box is displayed.
  2. Enter a Name for the rule input in the Name field. Names cannot contain spaces or special characters, and must contain at least one letter. Names also cannot be changed after creation.
  3. (Optional) Enter the purpose of the rule input in the Description field.
  4. Select the data type of the input from the Types list. The following data types are available for query rule inputs:
    • Boolean
    • Date
    • Date and Time
    • Number (Decimal)
    • Number (Integer) - default
    • Text
    • Time
  5. (Optional) Select the Allow multiple values checkbox to accept more than one value in the input.

NOTE: Null values are not allowed as the value of a rule input when the query rule is evaluated.

Defining an Entity to Query

  1. In the entity to query field, select the text field or the Browse button.
    • The Choose an Entity dialog box is displayed listing published data stores that you have been assigned the right to view.
    • Each query rule is associated with a single entity in a data store.
    • When the query rule is executed, the type of the results matches the type of the entity selected.
  2. Select a data store, then select an entity, and click OK.

Defining Query Conditions

By default, a query rule retrieves all rows from a data store entity.

You can add conditions to mine the data store for information that meets various criteria.

  1. Multiple conditions can be defined.
  2. All conditions must be met for any results to be returned.
  3. Conditions can be combined using AND logic.

NOTE: Case-sensitivity or insensitivity for text comparisons is determined by your RDBMS settings - not a setting configured in Appian.

To define a query condition, complete the following:

  1. When creating a query rule, click Add Condition. A group box with three fields is displayed.
  2. In the first field, select a node in the Data Store entity where you want to conditionally retrieve data. The nodes displayed are the same as the nodes in the custom data type used to create the entity. Only the lowest-level child of a node can be selected, and only when the node holds single values.
    • NOTE: You cannot browse through individual records in the node and multiple-value entities are not displayed.
  3. In the second field, select an operator to use in filtering the data. The operators that display vary according to the data type of your rule input, as listed in the right column of following table.
  4. In the third field, select a rule input from the ones you have defined for this rule. This is the comparison value that is checked against values in the data store.
  5. Click Create Rule.
Operator Description Supported Data Types
= The case-insensitive condition returns True if the field value exactly matches the compared value.
  • If your rule does not require case-sensitivity, consider using the exact() function to improve performance.
  • Boolean, Date, Date and Time, Time, Numbers
    < The condition returns True if the field value is less than the compared value. Boolean, Date, Date and Time, Time, Numbers, Text
    <= The condition returns True if the field value is less than or equal to the compared value. Boolean, Date, Date and Time, Time, Numbers, Text
    > The condition returns True if the field value is greater than the compared value. Boolean, Date, Date and Time, Time, Numbers, Text
    >= The condition returns True if the field value field value is greater than or equal to the compared value. Boolean, Date, Date and Time, Time, Numbers, Text
    <> The condition returns True if the field value does not match the compared value. Boolean, Date, Date and Time, Time, Numbers, Text
    starts with The condition returns True if the field value begins with the text string listed as your comparison value.
  • The match must be performed based on the literal string provided.

  • Special characters do not have special meaning as wildcards.
  • For example, [starts with: "apple*"] searches for the word apple and the asterisk character (*) rather than any character.

  • If the field value is null, the item will not be returned regardless of the rule input value.
    Text
    ends with The condition returns True if the field value ends with the text string specified as your comparison value.
    If the field value is null, the item will not be returned regardless of the rule input value.
    Text
    includes The condition returns True if the field value contains the string specified as the comparison value.
    If the field value is null, the item will not be returned regardless of the rule input value.
    Text
    in The condition returns True if the field value exactly matches any one of the values in the compared list of values. Boolean and list of Boolean, Date and list of Date, Date and Time and list of Date and Time, Time and list of Time, Numbers and list of Numbers, Text and list of Text
    not in The condition returns True if the field value does not match any one of the values in the compared list of values. Boolean and list of Boolean, Date and list of Date, Date and Time and list of Date and Time, Time and list of Time, Numbers and list of Numbers, Text and list of Text

    Paging Parameter

    You can limit the number of results returned by your query rule by passing an optional paging parameter of type PagingInfo. This allows you to specify how many items should be returned and the sort of those items.

    When a paging parameter is specified, a value of type DataSubset is returned. Use the a!pagingInfo() function to construct the paging parameter.

    Edit

    When you save a new version of a query rule, the latest version will be available immediately. This means that any object that uses this rule will immediately use the new version. It is therefore important to carefully consider the impact on running processes when changing query rule definitions. Appian recommends that you follow these best practices to facilitate the change management of query rules:

    • When calling rules in process nodes, pass rule inputs by keyword.
    • Take advantage of entity-backed records and design short-lived processes. See the Record Design page for more information.
    • If the version of your query rule must remain in sync with the version of your process, create a new rule and call it from the new version of your process model.

    Versions

    Each time you modify and save a query rule, a new version is created. All objects that use the query rule will use the latest version. All versions are accessible to designers who can view the query rule, and a query rule can be reverted back to a previous version at any time.

    For information on how to manage object versions, see Managing Object Versions.

    Delete

    Deleting a query rule prevents users from further viewing or editing it. However, the last version of the rule is still available to be used in processes, record views, and reports.

    Query rules can be deleted by users with Administrator rights to it. Appian does not recommend deleting query rules that are in use because the query rule can no longer be exported.

    To delete a query rule:

    1. Go to an application that contains the query rule.
    2. Select it in the grid and then click the Delete button in the grid toolbar.

    System Administrators have the ability to delete query rules (and other objects) in bulk by selecting them and clicking Delete in the toolbar.

    Rename

    Renaming a query rule will automatically update references to it in all objects. To rename a query rule:

    1. Select the rule to rename from one of the views in Appian Designer.
    2. Click Rename from the more menu in the grid toolbar.

    Learn more on Renaming Design Objects.

    Security

    Query Rule Security

    The security rolemap of the query rule controls who can see or modify the rule definition and properties.

    The following actions can be completed by each role:

    Actions Administrator Editor Viewer Deny
    Evaluate the rule Yes Yes Yes Yes
    View the rule definition Yes Yes Yes No
    Update the rule definition Yes Yes No No
    View the security in the application view Yes Yes No No
    Rename the rule Yes Yes No No
    View the security in the application view Yes Yes No No
    Delete the rule Yes No No No
    Update the security Yes No No No

    By default, rules inherit the security of the folder that they are saved in. To view or modify the security of a rule, go to an application that contains the rule. Select it in the grid and then click the Security button in the grid toolbar. Unchecking the Inherit security from parent box will allow you to add new items to the rolemap or modify existing items.

    Expression Evaluation Context

    Expressions can be evaluated under different user contexts, depending on how the object is configured. Objects like records or reports evaluate the expression in the context of the user viewing the object. Process nodes can be configured to run as different users, including the process initiator or the process model designer, using the Assignment Tab.

    See the User Contexts for Expressions page for more information on what user context is used when evaluating expressions in process.

    Other

    The query rules you define are limited in how long they wait to return results (10 seconds). This setting can be configured by a system administrator.

    There is also a limit to the amount of memory that can be returned by a single query rule. This is set by the conf.data.query.memory.limit value. The system will display an error with code APNX-1-4164-024 if this error is reached by a query rule. Use the paging parameter to return less data (or return data in batches) to avoid the limit.

    See also: Configuring Query Rule Limits

    If called multiple times in the same expression with the same parameters, only one query is made to the data store when the expression is evaluated and the result is reused for each identical call. This caching only applies within a single expression evaluation. Return values are not cached in separate expressions, or in separate evaluations of the same expression.

    Best Practices

    It is important that you tailor your query rules to provide only desired information, especially when substantial growth in the data-sets queried is expected.

    When designing query rules, keep the following considerations in mind:

    Constrain Your Results

    By default, query rules do not return a constrained subset of matching data records, unless you configure query conditions and filters and call the query rule using the PagingInfo parameter.

    • In a typical SELECT query against an RDBMS, a constrained query might be built using the LIMIT clause. In Appian, this is achieved by calling the query rule with a PagingInfo parameter.
    • Using a query rule, all records from an entity that match the query conditions are returned.
    • The likely size of the data-set returned needs to be considered when building query rules.
    • A larger number of records returned may affect performance of the application.
    • Instead of writing a rule that returns all records or many records, add query conditions and filters that limit the number of results to only those needed.

    Use Additional Criteria

    • Instead of selecting all records less than or greater than a given record, look for additional attributes that you can use to filter the results.

    Use Multiple Conditions

    • Use more than one query condition to reduce the number of records in the result set.

    Problematic Design Example

    The following improper design example illustrates how not to effectively implement query rules.

    Given a form that also displays the value of a previous item using a query rule, the following configuration might be used:

    • A query rule named GetPrior(currentId) might be used to identify the next numerically-lower item according to the ID value.
    • In this example, currentId is a rule input that holds the id value of the current item.
    • If the query rule used a query condition of id < currentId, this query rule would effectively return all matching id values that are numerically lower than the currentId.
    • In this example, only the highest-valued record out of the query rule's result set is desired.
    • This approach returns much more data than is needed, resulting in a much larger system load than is necessary.

    Best Practice Example

    It is possible to implement a query rule with an additional rule input to function as a boundary for the attribute that you're filtering on.

    For example, instead of id < currentId, you could implement two query conditions:

    Id < currentId

    — and —

    Id > minimumBoundary

    The query rule would then use two rule inputs GetPrior(currentId, minimumBoundary). This allows you to nest the query rule in one or more if statements within your expression.

    For example:

    if(length(GetPrior(currentId, currentId-1)) > 0, GetPrior(currentId, currentId-1), GetPrior(currentId, currentId-10))
    FEEDBACK