Retrieving Database Records

Automate database record retrieval with the Database Book.

Overview

These procedures allow you to get and retrieve records from a database. You can automate tasks like fetching individual records, retrieving all records from a table, or applying conditions to return specific results. These capabilities support data-driven workflows, allowing you to retrieve and use database records to automate tasks and keep information in sync across systems.

These procedures are part of the First Edition Database Book. Be sure your agent has learned this Book before using these procedures in an automation.

Retrieving All Records

To retrieve all the records from a specific table in your database, use the following syntax, replacing object with the name of the object you want to retrieve.

Syntax

get a database's {object}

Examples

get a database's Users

get a database's emails

get a database's Orders

Specifying Table Names

When retrieving all the records from a database table in Kognitos, the system tries to infer the table name from the sentence. This works when the table name is plural, such as employees, orders, or products.

However, if your table name is singular (like employee), and you're retrieving multiple records from the database, you should explicitly specify the table name using this syntax:

get a database's {object}
    the {object} table is "{table name}"

Example

For example, suppose your table name is employee (singular), and you want to retrieve a list of employees:

get a database's employees
    the employees table is "employee"

This tells the system:

• You're asking for multiple employees (using the plural noun, employees).

• You're explicitly pointing to the correct table name (employee).

Note: If the database table name is plural and matches the object in your sentence, you don't need to explicitly specify it.

Retrieving Specific Records

To retrieve specific records from a database table, you can specify filter conditions. There are two ways to do this.

1. Using whose

You can use the whose keyword to add filter conditions when retrieving specific records. The following filter operations are supported:

Operator	Meaning
is, matches	Equals (=)
is not, is not equal	Not equal (!=)
is greater than	Greater than (>)
is less than	Less than (<)
is greater than or equal	Greater than or equal (>=)
is less than or equal	Less than or equal (<=)
is not less	Greater than or equal (>=)
is not greater	Less than or equal (<=)

Syntax

get a database's {object} whose {field} {operator} {value}

You can combine up to two filters conditions using the and keyword:

get a database's {object} whose {field} {operator} {value} and whose {field} {operator} {value}

Examples

get a database's WorkOrders whose ID is greater than 3

get a database's users whose email matches "[email protected]"
    the users table is "user"

get a database's employees whose FirstName is "Jane" and whose LastName is "Doe"

2. Using query filter

A query filter uses SQL syntax to define filter conditions and offers greater flexibility compared to whose filters. This method supports any valid SQL condition, including:

• Complex expressions

• Multiple conditions with AND/OR

• Wildcards with LIKE

• Advanced comparisons and functions

Syntax

get the database's {object} where
    the query filter is "{filter}"

Examples

get the database's person where
    the query filter is "department='Engineering' and first_name='John'"

get the database's person where
    the query filter is "department='{the department}' and first_name='{the first name}'"

Handling Empty Tables

By default, retrieving records from an empty database table will raise an exception in Kognitos. However, you can explicitly tell the system to permit empty results without raising an exception by writing the following line:

the department's database null result is "allowed"

Examples

the department's database null result is "allowed"
get a database's employees

In the example below, the table name is singular (employee).

the department's database null result is "allowed"
get some database's employees whose employee_id is 0
    the employees table is "employee"

Storing Retrieved Records

After retrieving records from the database, you can store them in an automation for later reference. To do this, use the following phrase, replacing plural data name with any meaningful plural term.

use the above as the {plural data name}

To reference a specific record within the retrieved database records, write an additional line using positional keywords like first, second, etc.

use the {position} record as the {singular data name}

Example

In the following example:

• All the retrieved database records from the query are stored as the records.

• Only the first database record is stored as the employee.

get a database's person whose FirstName is "Nancy"
use the above as the records
use the first record as the employee