Get Records

The get records deluge task is used in integration scripts to get the list of records in a database for a specified criteria value. This criteria can be specified with the help a combination of operators. The get records by ID task can be used to get details of a particular record based on record ID. 

next_token: This token is returned in the JSON response of the getRecords task, if there are any remaining records and can be used to pull the next set of records from the database. 

The zoho.cliq.getRecords deluge task can be used with the following parameters in the query_map:

ParametersDescription
criteriaGet list of records that meet the specified criteria
from_indexIndex from which the records needs to be fetched
limitGet a fixed set of records by specifying the limit. The getRecords task returns a list of 100 records by default when the limit is not specified
start_tokenUse the next_token obtained in the response JSON when the from_index and limit are specified as a value for the start_token key. Using this parameter in the next corresponding request will get the list of remaining records in the database. 
sort by lowest to highest (ascending)Sorts a column and returns a list ordered from the lowest value to the highest value. Let's Consider an example of an employee database. To sort a column emp_id in ascending order use {"order_by","+emp-id"}. Doing this, sorts the column emp-id in ascending order.
sort by highest to lowest (descending)Sorts a column and returns a list ordered from the highest value to the lowest value. Consider the same example of an employee database. Now, to sort the column emp-id in descending order use {"order_by","-emp_id"}. Doing this, sorts the column emp-id in descending order. 

Sample syntax using criteria


query_map = Map(); 
criteria_string="field_name==field_value"; 
query_map.put("criteria",criteria_string); 
response_map=zoho.cliq.getRecords("<database_name>", query_map); 

Sample syntax with criteria and sort by order_by

query_map = Map(); 
criteria_string="field_name==field_value"; 
query_map.put("criteria",criteria_string); 
//To sort a column in a database in ascending order use  
query_map.put("order_by","+ field name"); 
//To sort a column in a database in descending order use
query_map.put("order_by","- field name");
response_map=zoho.cliq.getRecords("<database_name>", query_map); 
 

where,

  • criteria_string = criteria with which the records will be fetched from a particular database
  • criteria = key value for which the criteria_string should be passed

Operators 

While it is always easy to filter out records with parameters, it is important to use operators or filters to get finer results. Operators can be used to fetch a list of records that satisfy one or more conditions. 

First, let's start by creating a sample database to store attendee information for Conference XYZ. This database contains the following fields

  • Name
  • Age
  • Role
  • Country 
OperatorSample SyntaxResult
AND (&&)"(country!=USA)&&(gender==Female)"Returns records of a list of female attendees belonging to a country other than the USA
OR ( || ) "(country!=USA)||(gender==Female)"Returns records of the list of attendees who belong to a country other than the USA or are female or attendees who satisfy both the conditions
  IN"(country IN USA,India,Australia)"Returns records of the list of attendees who belong to the countries USA, India or Australia.
NOT IN "(country NOT IN USA,India,Australia)"Returns records of the list of attendees who belong to a country other than USA, India or Australia.

Query Criteria Types 

The list of criteria conditions that can be used to query records from a database are listed in the table below. 

To fetch records that...Criteria ConditionField TypeQuery ExampleQuery Result
Exactly match a given value==String, Integer, Boolean"role==Marketing Analyst"; Returns records where Role field is set as Marketing Analyst
Do not match a value!=String, Integer, Boolean"country!=China";Returns records where the county field is set to values other than China
Is greater than the given value>Integer"age > 25";Returns records where age field contains entries greater than 25 
Is less than the given value<Integer"age < 25";Returns records where age field contains entries that are less than 25
Is greater than or equal to the given value>=Integer"age >= 25"; Returns records where age field contains entries that are greater than or equal to 25
Is less than or equal to the given value<=Integer"age <= 25"; Returns records where age field contains entries that are lesser than or equal to 25
Contains the specified stringlike *"insert string"*String"country like *ia*";Returns records of the country field containing the string *ia*, India for example
Does not contain the specified stringnot like *"insert string"*String"country not like *na*";Returns records of the country field containing anything other than the specified string *na*, India, USA for example
Begins with a specific stringlike "insert string"*String"country like U*";Returns records of the country field that begin with the string U*, for example USA
Does not begin with the specified stringnot like "insert string"*String"country not like I*";Returns records of the country field that begin with  anything but the string I*, for example USA, China
Ends with a specific stringlike *"insert string"String"country like *ia ";Returns records of the country field that end with the string *ia, for example India
Does not end with the specific stringnot like *"insert string"String"country not like *A";Returns records of the country field that end with anything but the string *A, for example New Zealand 

Example

Let us consider creating a /myrequests command which will get the list of all requests raised by you. The userID of the user executing the command is passed as the criteria, to get the list of all records raised by the user.


response = Map();

// Define a criteria according to which the list of records must be fetched using the zoho.cliq.getRecords deluge task
query_map = Map(); 
criteria_string="userid=="+user.get("id"); 
query_map.put("criteria",criteria_string); 
response_map=zoho.cliq.getRecords("zylkerrequestmanagement", query_map); 
if(response_map.get("status").equalsIgnoreCase("SUCCESS") && response_map.get("list").size()>0)
{ 
    requestList = response_map.get("list"); 
} 
info response_map;
info requestList;

// Iterate the record list to display them in a tabular format in the message card. 

rows = List();
for each responseItem in requestList
{
row = Map();
row.put("Request ID",responseItem.get("id"));
row.put("Request", responseItem.get("request"));
row.put("Assignee", responseItem.get("assignedto") );
row.put("Status", responseItem.get("status"));
row.put("Delete Request", "[Delete Request](invoke.function|deleterecord|scott.fisher@zylker.com|"+responseItem.get("id")+")");
rows.add(row);
}
table = {"type":"table","title":"Usage Trend","data":{"headers":{"Request ID","Request","Assignee","Status"},"rows":rows}};
response = {"text":"These are the list of requests raised by you : ","card":{"theme":"modern-inline","title":"Your IT requests"},"slides":{table}};
return response;

A user executes the /myrequest command to view the list of requests raised and the status of the request. 

 

Get a record by id

The getRecordById deluge task is used in integration scripts to get the details of a particular record based on the record ID. 

Syntax


record_id="123456789"; 
response_map=zoho.cliq.getRecordById("<database_name>", record_id);

where,

  • database_name = Name of the database
  • record_id = Unique ID assigned to a particular record.