LogoLogo
About
  • Home
  • Guides
  • BDK
  • REST API
  • Release Notes
  • First Edition Books
  • Airtable
    • Fetching Airtable Data
  • AWS S3
    • Listing Files
    • Retrieving Files
    • Uploading Files
    • Uploading CSV
    • Uploading Strings
    • Deleting Files
    • Deleting CSV
  • Azure Blob Storage
    • Uploading Files
    • Retrieving Files
  • Azure Service Bus
    • Sending Messages to Azure Service Bus Queue
    • Receiving Messages from Azure Service Bus Queue
  • Azure Translate
    • Translating A Document
  • Database
    • Retrieving Database Records
    • Updating Database Records
    • Creating Database Records
    • Deleting Database Records
  • Document Processing
    • Document Processing: Workflow Patterns and Best Practices
    • Document Processing with OCR
    • Extract Data from a Document
    • Extract Pages from a Document
    • Extract Tables from a Document
    • Extract a Subdocument
    • Extract Subdocuments
    • Getting Fields from a Document
    • Getting Tables from a Document
  • Google Cloud Storage
    • Dumping Files to Google Cloud Storage
    • Purging Files from Google Cloud Storage
    • Dumping CSVs to Google Cloud Storage
    • Purging CSVs from Google Cloud Storage
    • Enriching Questions
  • HTML
    • Extracting Tables from HTML
  • HTTP
    • GET Request
    • POST Request
    • DELETE Request
    • PATCH Request
    • PUT Request
    • HEAD Request
  • Hubspot
    • Fetching Data from Hubspot
  • Microsoft Excel
    • Opening Excel Files
    • Converting a File to Excel Format
    • Getting a Table from Excel
    • Converting Tables to Excel Workbooks
    • Renaming Excel Files
    • Creating a Merged Spreadsheet from Multiple Files
    • Getting an Excel Worksheet's Row
    • Getting an Excel Worksheet's Row's Cell
    • Getting an Excel Worksheet Cell's Column Label
    • Setting an Excel Worksheet Cell to a String
    • Getting an Excel Worksheet's Changelog
    • Apply Changelogs to an Excel Worksheet
    • Saving An Excel Worksheet
  • Microsoft Outlook
    • Email Operations
    • Getting Schedules
    • Moving Emails
  • Microsoft Power BI
    • Working with Power BI Objects
  • Microsoft SharePoint
    • Retrieving SharePoint Items
    • Uploading a File to SharePoint
    • Converting Data to SharePoint String
    • Moving a File to a Folder
    • Deleting a Folder
    • Getting a Folder
    • Uploading a String to a Folder
    • Creating and Retrieving a Folder
    • Deleting a File
  • Oracle Cloud Fusion
    • Fetching Data from Oracle Cloud Fusion
    • Adding New Records
    • Updating Records
    • Deleting Oracle Fusion Objects
    • Managing Attachments
    • Downloading Files from Oracle Fusion Objects
  • Paycom
    • Working with Employee Details
    • Managing Punch Entries
  • Salesforce
    • Working with Salesforce Objects
    • Submitting Sales Objects for Approval
    • Creating Salesforce Reports
    • File Management
  • Servicenow
    • Retrieving Data
    • Manipulating Data
  • Slack
    • Sending Slack Messages
    • Reading Slack Messages
  • Stripe Pay
    • Fetch Data from Stripe
    • Add New Records
    • Working with Invoices
    • Deleting Objects from Stripe
  • Zendesk
    • Creating a Ticket
    • Updating a Ticket
    • Assigning a Ticket
    • Deleting a Ticket
    • Getting a Ticket
Powered by GitBook
On this page
  • Overview
  • Retrieving All Records
  • Retrieving Specific Records
  • Handling Empty Tables
  • Storing Retrieved Records

Was this helpful?

Export as PDF
  1. Database

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 "user@gmail.com"
    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

Last updated 18 days ago

Was this helpful?