Creator Help

Fetch Records

Table of ContentsDown

Fetch Records

Forms are structures that contain data in Zoho Creator. The form data is stored in a relational database and Deluge provides an easy wrapper called collection variable for accessing these data. The collection variables hold one or more records fetched from a form, based on a given criteria, sort by and range. To access the currently submitted/persisted field values, you can use the Deluge variable named input with syntax input.<fieldname>

Syntax

<collection variable> = formname[<criteria>] sort by <field> range from <x> to <y>;

where,

  • collection variable - name of the variable that contains the fetched records from the form.
  • formname - name of the form whose data has to be fetched.
  • criteria expression - the criteria expression that evaluates to a value
  • sort by <field> - the field based on which the records will be sorted in ascending order . By default, the records will be sorted in ascending order.  Specifying the sort by <field> is optional.
  • sort by <field> desc - the field based on which the records will be sorted in descending order.
  • range from <x> to <y> - range enables you to limit the records fetched within the start index (x) and end index (y). If range is not specified, all the records in the form that satisfy the given criteria, will be fetched.

Examples 

1. Fetch all records form a Form

To fetch all the records from a form (FormA), specify the criteria as [ID != 0], where ID is a auto-generated field that contains a unique value for each record in the form.

x = FormA[ID != 0];

where,
    •    x is the collection variable that holds the form data.
    •    FormA is the form name from which the data is fetched.
    •    [ID != 0] is the criteria expression based on which the all the records are fetched from the Employee form.

 

2. Fetch records within a given range

To fetch records from a Form (FormA) within a given range, specify the start index and end index range in the Fetch record task, as shown in the sample code below. Here, the first five records that are sorted by Added_Time field will be fetched from the form.

 x = FomA [ID != 0] sort by Added_Time range from 1 to 5;

where,

  • x is the name of the collection variable that contains form data.
  • FormA - name of the form whose data has to be fetched.
  • [ID !=0] is the criteria expression.
  • sort by Added_Time is the field based on which the records will be sorted. Here, Added_Time is the Deluge variable, which returns the time when the record was added.
  • range from 1 to 5 - range enables you to limit the records fetched within the start index(x) and end index (y). If range is not specified, all the records in
the form that satisfy the given criteria, will be fetched.

3. Fetch data from related form (Customer Form) and populate in current form (New Request Form)

To fetch data from a form and use it in another form, a relationship has to be established between the two forms, using Lookup fields. To create relationships between forms using Lookup fields, refer the topic Creating relationship. Let us illustrate this with the help of an example. The sample application has two forms:

- Customer form holds the customer details like Customer ID, name, address, post code, phone number and email address

- New Request form is used to enter a new service request from a customer. This will have the Customer ID as a lookup field from Customer form and other fields to display the customer details and request details. When a Customer Number is selected from the list, the customer details like, Name, Address etc will be displayed from the Customers Form.

if (count(Customer [ID == input.Customer_ID]) >0)
{
x = Customer [ID == input.Customer_Number];
//input.<field_name> refers to fields in current form 
input.Customer_Name = x.Name;
input.Address = x.Address;
input.E-mail = x.Email;
}

Code Explanation:

Check if the customer already exists. If exists, fetch records that satisfy the given criteria, and store it in a collection variable. Update the current form fields with the fetched data

x = Customer [ID == input.Customer_ID]; Fetch records from Customers form with the given criteria and store it in collection variable named “temp”
input.Customer_Name = x.Name; Update the Customer_Name in the current form from the variable “x”. Here, input.Customer_Name refers to the customer name field in the current form and temp.Name is the customer name field fetched from the “Customers” form.

4. Fetch and update related form data from current form

In the following example, the on add - on success script is written to fetch specific records from the Book form with Name same as the currently submitted book name in the "Issue Book" form. The record fetched is stored in the collection variable namedmyBook. You can now access any field in this record, from the myBook collection variable.

on success
{
if (count(Book [ID == input.book]) > 0)

//fetch the book row from the 'Book' form

myBook = Book [ID == input.book];
//modify the 'Status' of this book to 'Issued'.
myBook.Status = "Issued";
}

5. Copy records from one form (FormA) to another form (FormB)

//iterate each record in FormA
for each x in FormA
{ //add to FormB, using the Insert record task
insert into FormB
[
FirstName = x.FirstName
Added_User = zoho.loginuser
]
}

6. Fetch the ID of the last added record

To fetch the Id of the last added record, add the following code in the On Add -> On Success section of the form.

//"record" is a variable that holds the record ID
record = Order_form[ID != 0] sort by Added_Time desc;

where,
record is the variable that will hold the record ID.
Order_form is the form link name.
Added_Time is a Zoho Variable that contains the time at which a record is added.
desc sorts the records in the descending order.

Related Topics

Top