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:

  1. Customer form is used to enter customer details like Customer ID, Name, Email and Address

                                                    Screenshot displaying Customer Report

  2. New Request formis used to enter a new service request from a customer. This will have a field named "Customer number" as a lookup field from Customer form's Customer ID field. Other fields are present to display the customer details and request details. When a Customer Number is selected from the list, the customer details like, Name, Address and email will be displayed from the Customers Form as shown in the image below.

    Screenshot displaying New Request form with data populated automatically based on the selected Customer ID

To achieve this, add the following code in the On User Input section of the "Customer number" field in New Request form

if (Customer_form[ID == input.Customer_number].count() > 0)
{
x = Customer_form [ID == input.Customer_number];
//input. refers to fields in current form
input.Name = x.Name;
input.Address = x.Address;
input.Email = 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_form [ID == input.Customer_number]; Fetch records from Customers form with the given criteria and store it in collection variable named “x”
input.Name = x.Name; Update the Name in the current form from the variable “x”. Here, input.Name refers to the customer name field in the current form and x.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.

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