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>
<collection variable> = formname[<criteria>] sort by <field> range from <x> to <y>;
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 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.
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.
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.
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)
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 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.