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>;
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.
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.
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:
Screenshot displaying Customer Report
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
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.
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";
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.