The recipes on this page show how to perform common data lookups using the
a!queryEntity() expression function.
Although the examples on this page all deal with
a!queryEntity(), the same patterns also apply to the
queryrecord() function, which can be used to perform data lookups against entity-backed and process-backed records.
The recipes can be worked on in no particular order. However, make sure to read the first section to get yourself set up.
Before we start with the recipes, we'll need a data store entity. For our examples, let's use the employee entity from the Records Tutorials.
See also: Records Tutorial
Next, create a constant called
EMPLOYEE_ENTITY with Data Store Entity as the Type and the Employee entity as the Value.
See also: Constants
Goal: Retrieve the data for a all fields of an entity.
When you execute a query rule, it pulls back the data for all of the fields of the entity. This recipe replicates that functionality using
a!queryEntity(). See Querying Data From an RDBMS on how to decide which function to use.
To retrieve all fields of the entity being queried, simply omit both the
aggregation parameters from
a!query(). When using this approach, you should cast the result to the appropriate type to ensure that it works smoothly in process models and rules that use it. This is because
a!queryEntity() always returns a datasubset that includes a dictionary, while query rules return a list of CDTs.
In this example we are going to retrieve the employee whose id is
This example should return the value
[id=8, firstName=Jessica, lastName=Peterson, department=Finance, title=Analyst, phoneNumber=555-987-6543, startDate=2004-11-01]. This value will be of type
If you expect your query to return multiple results, you should instead cast to a list of CDT. In this example, we will retrieve any employees whose first name begins with "A".
This example should return the value
[id=17, firstName=Andrew, lastName=Nelson, department=Professional Services, title=Consultant, phoneNumber=555-789-4560, startDate=3/15/2005]; [id=4, firstName=Angela, lastName=Cooper, department=Sales, title=Manager, phoneNumber=555-123-4567, startDate=10/15/2005].
Goal: Retrieve the data for a single field of an entity rather than all of the fields.
When you execute a query rule it pulls back the data for all of the fields of the entity. The more data you pull back from the database the longer the query rule takes to run. A common way to restrict the amount of data returned by a query rule is to create several different data store entities that reference the same database table, each of which only contains some of the fields. Instead, using
a!queryEntity() to select specific fields as shown below restricts the amount of returned data, is faster to develop, and has the advantage that the field or fields can be selected at run-time rather than design-time.
In this example we are going to retrieve the phone number, stored in the field
phoneNumber, for the employee whose id is
This example should return the value
To retrieve data for more than one field, you can add additional
a!queryColumn()'s to the
Goal: Retrieve the unique list of values in a given field.
It will almost always be significantly faster to have the data source do the uniqueness calculation before returning the data to Appian. This is especially true for large data sets.
a!queryEntity() lets the data source perform the uniqueness calculation.
In this example we are going to retrieve the list of departments that have employees.
This example should return a list containing
"Professional Services", and
"Sales". Note that even though there is more than one employee in many of these departments, each department is only listed once in the result.
Goal: Perform an aggregation or computation on all values of field.
In this example we are going to count the number of employees in each department.
This example should return one dictionary for each department where the keys in the dictionary are
numberOfEmployees and the values match the following table.
Goal: Retrieve data that meets at least one of two different conditions.
Using query rules, the only way to find entries that match at least one of two conditions is to run two different query rules and combine the results. Using a
logicalExpression inside the Query object we can execute the same logic in a single call to the data source, resulting in faster performance.
In this example we are going to retrieve the names of employees who either started within the last 2 years or have the word "Associate" in their title.
The exact list of results that is returned will to depend on when you run the example:
Jan 2, 2015:
John Smithwill be the only employee returned because of the start date condition.
Jan 2, 2015: no employees will be included in the results because of their start date.
Laura Bryant and
Stephen Edwards will be included in the result regardless of when you run the example as they are included because their title contains the word
Goal: Retrieve data based on complex or nested conditions.
In this example we are going to retrieve the names of the senior members of the Engineering department where "senior" is defined as either having a title of "Director" or having a start date of more than 10 years ago.
This example should return
John Smith and
John Smith is included because he is a Director and Mary Reed is included because her start date is more than 10 years ago. Both of them are in the Engineering department.
Goal: Find entries where a given field is null.
In this example we are going to find all employees who are missing either
This example does not return any results because none of the employees in our sample data are missing any of the specified fields.
Goal: Retrieve data based on search criteria specified by end users e.g. when looking for employees by last name, title, or department. Search criteria that are left blank are not included in the query.
For an example on filtering for null values, see the recipe: Filtering for Null Values.
First, create an expression rule
ucSearchEmployees with the following rule inputs:
Enter the following definition for the rule:
Test it out
Unlike the recipes above, this one is a rule with inputs. So rather than just getting a single result let's take a look at several different results for different rule inputs.
First, let's try not specifying any fields except for pagingInfo:
This query will return the first 30 employees, sorted A-Z by last name.
Next let's try specifying a department in addition to the pagingInfo:
This expression will return a list of employees in the Sales department, sorted A-Z by last name. In this example that is:
Stephen Edwards, and
We can also combine multiple filters together. Let's try searching by both last name and department:
This expression will return a list of employees that are in the
Sales department and have a last name that contains
Bryant. In this case that's a single employee:
To see an example of integrating this query into a SAIL interface, see the SAIL recipe: Searching on Multiple Fields
Goal: Demonstrate how to sort on multiple columns when there is no field aggregation.
Using the a!querySelection function allows you to define a set of column selection configurations. When using this function, you can sort on any of the fields in the data entity, whether the fields are included in the selection or not.
In this example we are going to retrieve the department, first name, and last name of employees and sort them ascendingly by department, then by title. The data will be sorted first by department, and then by title, eventhough title is not part of the query selection.
Goal: Demonstrate how to sort on multiple fields when aggregation is performed in one or more fields.
In this example we are going to retrieve the count of employees by department and title. We are also going to sort the results ascendingly; first by department, then by title.