Creator Help

Creating SubForms

Table of Contentsup

Overview

A SubForm is a form within a form. The primary form is called the MainForm, and the form that is inserted in it is called the SubForm. SubForms are generally used to show data from tables that have a one-to-many relationship. For example you would use a SubForm if you wanted to display an order with the order details. In this case, the order information would display on the SubForm. The data in the order table is the "one" side of the relationship. The data in the Item table is the "many" side of the relationship — each order can have more than one item. The sample application "Order Management" illustrates the SubForm feature.

Creating a SubForm

Zoho Creator supports the "SubForm" field type using which you can insert a SubForm into your MainForm. Please find the steps given below to create a SubForm.

Add the required Forms to your application from the dashboard page by selecting Create New -> Form option. For example, add the "Items" form to enter the Items ordered and the "Order" form to enter details about each order. Here, "Order" form is the MainForm and "Items" form is the SubForm.

In the edit mode of the MainForm, simply drag-n-drop the SubForm field type at the required position in your editor area. For example, drag-n-drop the "Items" form into the "Order" form.

 

In the Adding SubForm screen, a dialog box appears where you have to select the Form to be added as the SubForm field and click on Done. For example, select the "Order Item" form in the "Order Management" application as the SubForm. Click Done to insert the SubForm to the MainForm. You can also select the Blank form option and directly create the SubForm within the MainForm. 

 

The SubForm will be inserted into the MainForm as shown below:

When you access the application, the SubForm will be displayed within the MainForm, as shown below. Here, the "Order form" displays the "Items" SubForm to add the items ordered. You can use the "Add +" or the "Add New" button to add entries to the MainForm from the SubForm.

The MainForm report, (Order View) will display all the orders with the values of the selected SubForm fields, as shown below.

The Column Properties option in the MainForm report is used to configure the SubForm report and the SubForm fields to be displayed in the report, as shown below.

Creating Relationship between MainForm and SubForm

Create a lookup in the SubForm and relate it to the parent form. Once the lookup field is created in the child form, that field can be used as relationship field between the two forms. When adding the lookup, select the existing SubForm relation and pick the ID field, as shown in the image attached.

Select the SubForm report from the Reports tab, click on "Column Properties" and select the lookup field created above and click on "Link " button, to create a link to the MainForm data.

Set Default Entries and Limit Maximum Entries

You can set the "Default Entries" and "Maximum Entries" for a SubForm field type from the "Appearence" section under the field properties tab of the SubForm field.

  • The "Set Default Entries" will display the specified number of rows in the SubForm, when the form is loaded.
  • The "Limit Maximum Entries" will limit the number of entries that can be added to a SubForm.

Export MainForm Data with SubForm

Lets assume we have a MainForm named "Orders" form and a SubForm named "Items" SubForm. To export MainForm data with SubForm,

  1. Select the MainForm report from the Reports tab.
  2. From the "Column Properties" option enable the "Link" button displayed for the SubForm, as shown in the screen-shot below, and click "Done" to update the changes.

  3. Use the following URL format to export data from the MainForm report. This automatically includes the data of the MainForm in one tab and also the related SubForm data in another separate tab within the same excel sheet. The record link id column generated for the MainForm report can be used to refer the related SubForm entries.

<a href="https://creatorexport.zoho.com/<adminuser>/<appname>/xls/<viewname>/exportDisplayData=false
For example : https://creatorexport.zoho.com/sampleapps/export-data/xls/Order_Report/exportDisplayData=false

Import data into a Form with SubForm

To import data from an XLS file into a Form with SubForm, the XLS file must contain the MainForm data and SubForm data in separate tabs and the columns must be in a specific format.  

a. Creating the XLS file in the specific format

An easy workaround to create this XLS file will be to add a sample record into the Form with SubForm and then export this data in XLS format, as specified in the section Export MainForm data with SubForm.  For example, the XLS file generated by exporting MainForm named "Orders" and a SubForm named "Items", will be in the format as explained below. 

  1. The exported XLS file gets created with 2 Tabs - "Order_Report" tab and "Items_Report" tab. Note that these tab names should match with the view link names in your application exactly.
  2. The Order_Report tab contains the columns Title, Name, Email etc. as defined in the "Order" form. Similarly the SubForm "Items_Report" tab also contains the respective columns as the fields specified in "Items" SubForm.
              Screenshot displaying the Order Report tab in the exported xls file

              Screenshot displaying the Items Report tab in the exported xls file
  3. The "Items_Report" tab includes an additional column "REC LINK ID" in addition to the other field columns. This "REC LINK ID" column has a number-id like "8099932" which is unique for each row. This number-id is present in the"Order_Report" tab under the "Items" column. The Rec Link Id links the MainForm with SubForm using the number-ID.

b. Importing the data 

  1. Populate the required data into the XLS file created above.
  2. Access the MainForm Report in live mode and click on Settings icon - Import Data from the Report header. 
  3. Select the File Type as Excel and the Data Location as Local Drive. 
  4. Browse and give the XLS file created above and import data. (The tab generated for the MainForm report must be selected in the XLS file). It will import data across the 2 forms - MainForm and SubForm.

Customize SubForm field size

To customize the size of the fields within the SubForm:

Go to SubForm Field properties -> Field width customization -> Adjust field width manually

In the popup window, select the required field size and click on Done to save the changes.

Note

The SubForm layout size can be customized from SubForm Field Properties -> Appearance -> Field Size.

Deluge Script

The Subform name represents the entire collection of records in a SubForm. ID value will be available only after the SubForm record gets persisted in the database. The expressions <collection>.<subform_name> will return another collection. A row is a pre-defined variable available in on add row, on delete row and on user input action of a SubForm field. You can access, update and dynamically append or clear values of SubForm field using a row.

To execute tasks when a row is added or deleted in the SubForm

The following field action script is supported for SubForm field types, as shown below.

  • On Add Row: To execute tasks when a new row is added to the SubForm.
  • On Delete Row: To execute tasks when an existing row is deleted from the SubForm.

To execute tasks on user input of SubForm fields

The following Field Action Script is supported for all the fields within the SubForm.

  • On User Input : To execute tasks when a value is input by the user. For example, in the script given below, the Unit Price of each item selected is fetched from the "Products" form and updated as the "Unit Price" in the SubForm.

To add items to a picklist

You can add items to a picklist in a SubForm using the UI.append task. Refer this help topic for syntax and sample code.

To add items to a picklist in a SubForm based on selection in MainForm:

  • The “contacts” stores the Employees of each Client.
  • The “avtivities” form is the MainForm which has a lookup to the “Client” on the parent form and the “Employee” field on the SubForm. 
  • The single select “Employee” field on the SubForm can be limited based on the selected client by adding “on Add Row” script on the SubForm A sample script is given below. The script will be executed when a row is added to the SubForm. 
 
if(count(contacts[Client == input.client_field]) >0)
{
emps = contacts[Client == input.client_field];
ls = emps.contact.getAll();
clear row.Employee;
row.Employee:ui.append(ls);
}

Changing the selected Client after adding the SubForm rows will not reload the employees list. A script to limit the options is given below. This script should be added to the on user input block of the client_field.


for each y in emp_activity
{
   emps = contacts[Client == input.client_field];
   ls = emps.contact.getAll();
   clear y.Employee;
   y.Employee:ui.append(ls);   
}

To Hide/Disable SubForm fields

You can Hide and Disable SubForm fields in theon load action of MainForm andon user input action of MainForm fields.The syntax for hiding and disabling SubForm field is as follows:

hide <SubForm name>.<field name>;
show <SubForm name>.<field name>;
enable <SubForm name>.<field name>;
disable <SubForm name>.<field name>;

The above tasks when executed perform the operation on entire column. For ex, when hide task is applied on SubForm field, it hides the entire column in the SubForm grid.

To populate fields in a SubForm

You can use the following script to populate a field in the SubForm with the value of the field in the MainForm. In the "On Add Row" task of the SubForm, give the script as

row.SubFormField = input.FormField;

To create an HTML view with SubForm records

The Sample application SubForm in HTML View opens a HTML view on submit of a record in the Employee Form. The on add ->on success script added to the Employee Form, calls the HTML view named “Print” using the OpenUrl task. The HTML view prints the record with the SubForm data.

Copy the application to your account and view the script,

To send SubForm details in an email

To send SubForm data in an email, you can iterate the SubForm data and concatenate with the message of an email. Please find the sample code format given below:


message= “Details of the data added ”;
for each item in <Field_Name> // SubForm field name in the MainForm
{
   message=message+”Employee no:”+item.Employee_No;// variable.Field_Name_In_The_Subform
}

The Sample application Subform Details in Email comprises of a Student Form which is a parent form with SubForm. A script is added to the on add ->on success of the “Student” form to send email with SubForm data

To add multiple attachments to a record

You can add multiple attachments to a record, using SubForm feature.  Create a form with a single “File Upload” field type and add it as a SubForm field to your primary form, to add multiple attachments per record.

Top

© 2015, Zoho Corporation Pvt. Ltd. All Rights Reserved.