Advanced App Building

Level: Advanced Duration: 1 hour

Recap: Made enhancements to the order management application to calculate total price of an order, update available stock, create purchase order, create customized reports and learnt how to replace existing code with reusable functions.

Create Order Management app using SubForms

In Tutorial 1, we created an order management app with the Order and Product forms and created a relationship between the forms using lookup fields.

In this tutorial, we will create the Order Management app using SubForms. (i.e) the Product Form will be added as a SubForm to the Order Form. A SubForm is a Form within a Form. It helps you submit related forms (with one-to-many relationship) in a single interface. The primary form is called the MainForm, and the Form that is inserted in the main form is called the SubForm.  We will also learn about Script actions that can be performed on a SubForm.

Create an Application with Products Form

  • Go to the home page of your Zoho Creator Application and click on Create Application.
  • Enter the name "Order Management for SubForms" and click on "Create"
  • In this newly created application, create a form named Products. This Form will be used to store details of Products that customers can purchase. Insert the following fields in the Form:
    1. Drag and drop the Single line field type in the editor box and name it as Product
    2. Drag and drop the Number field type in the editor box and name it as Quantity
    3. Drag and drop the Currency field type in the editor box and name it as Price

              Screen shot displaying Products Form

Create an Items Form

Now click on Create New - Form to create a form named Items. This Form will be used as a SubForm in Order Form(about to create). Insert the following fields in the Order Form:

  • Drag and Drop a Look-up field type linked to Products Form > Product Field (in Products Form) and name it Product Name
  • Drag and Drop a Number field type and name it Product Quantity
  • Drag and Drop a Currency field type and name it Product Price

                 Screen shot displaying Items Form

Create an Order Form with SubForm

Click on Create New - Form to create a form named Order. This Form will be used by customers to place their online Orders. Insert the following fields in the Order Form:

  • Drag and drop the Single line field type in the editor box and name it as Name
  • Drag and drop the Email field type in the editor box and name it as Email
  • Drag and drop the Currency field type in the editor box and name it as Total Amount
  • Drag and drop the SubForm field type in the editor box and select "Items" Form from the drop-down, and click on Done

    Screen shot displaying Orders Form

SubForm Actions

SubForm actions are actions that are performed whenever a change is made in the subform. For example, SubForm actions can be performed when a row is added, deleted or when a SubForm field is filled in. SubForm actions include On Add row, On Delete row and On User input to make the SubForm functionality more useful.

Screen shot displaying SubForm Actions

The On Add Row and On Delete Row scripts get executed when a row is added or deleted in the SubForm. The On User input script gets executed whenever the user adds data in the SubForm. Let us see an example of the On User input script which calculates the total amount based on the product, price and quantity selected.

  1. Click on Workflow tab on the left to go to the Script-Builder of the Order Form.
  2. Select Field Actions - Items - Product Name - On User input and add the following code in the editor box

    //Link the Product Name field in subform to the Product field in Products Form
    selected_Product = Products [ID = row.Product_Name];

    //Set the quantity of the selected product to 1 by default, if no quantity is declared
    if(row.Product_Quantity == null)
    {
    row.Product_Quantity=1;
    }
    if (row.Product_Quantity > selected_Product.Quantity)
    {
    alert("Please select a lesser quantity");
    }

    //Set the price of the selected product as per the Products Form
    row.Product_Price=selected_Product.Price;

    //Calculate the total amount based on the Product, Price and Quantity.
    tot = 0.0;
    for each item in Items
    {
    tot = tot + (item.Product_Price * item.Product_Quantity);
    }
    Total_Amount = tot;

  3. Select Field Actions - Items - Product_Quantity - On User input and add the following code in the editor box

    //Link the Product Name field in subform to the Product field in Products form
    selected_product = Products [ID = row.Products];

    //Set the quantity of the selected product to 1 by default, if no quantity is declared.
    if (row.Product_Quantity == null)
    {
    row.Product_Quantity = 1;
    }
    if (row.Product_Quantity > selected_Product.Quantity)
    {
    alert("Please select a lesser quantity");
    }

    //Calculate the total amount based on the product, price and quantity.
    tot = 0.0;
    for each item in selected_product 
    {
    tot = (tot + item.Price * item.Quantity);
    }
    input.Total_Amount = tot;

  4. Add the following script in On Add > On Success section of the Orders Form. This script updates the quantity of Products in inventory after subtracting the ordered quantity after each Form Submission.
    for each item in input.Items
    {
    selected_Product = Products [ID = item.Product_Name];
    selected_Product.Quantity = (selected_Product.Quantity - item.Product_Quantity);
    }

The Items Form has been added as a SubForm in the Orders form. The Total Amount field automatically calculates the total amount based on the product, price and quantity selected. Click on "Access this application" on the top right corner to go to the live mode. Fill up the Products form and the Ordersform with sample entries. The Total Amount field displays the total amount based on the product, price and quantity selected.

Screen shot displaying the SubForm values with the Total Amount field displaying the calculated value

To view the data stored in the SubForm:

  • Go to Order Report -> Settings -> Column Properties
  • Click on the icon placed in the extreme right of the Subform field and check "Create a link to show all fields"
  • Now go to the Order report and click on any value in the Subform field type. It will display all the data stored in the subform field.

    Screen shot displaying Google Nexus 4 in link format

    Screen shot displaying the subform values when we click on Google Nexus 4

Configure Custom Actions

Custom Actions are actions performed on selected records in a report by executing Deluge functions. For example, you can define a function to approve or reject an order request and configure this function as a custom action for a report. This action can be invoked for selected records in the report. In this topic, we will learn how to create a custom action to approve or disapprove an order.

Add a new field "Status" in the Order form

Firstly, we will add a "Status" Field in the Order form with options "Approve", "Disapprove" and "Waiting for Approval".

  1. Go to the "Order" form.
  2. Drag and drop the "Radio" field type and name it "Status"
  3. Allow 3 options namely "Approved", "Disapproved" and "Waiting for Approval" and set the default option as "Waiting for Approval"

Create "Approve" and "Disapprove" functions

Now we will create two functions and configure those functions as "Approve" and "Disapprove" custom actions. First let us create the two functions

  1. Click on Workflow - Functions to create a new function
  2. Enter the Function Name as "Approve" and click on "Create the function"

    Screen shot displaying the "create function" window for "Approve" function

  3. Enter the following code in the editor box and click on "Save Script":
    //Link the "Approve" function with Order form
    void Approve(Order_form request)
    {
    request.Status = "Approved";
    }
  4. Now add another function by clicking on "New Function" and name it "Disapprove". Click on "Done"
  5. Enter the following code in the editor box for "Disapprove" function and click on "Save Script":
    //Link the "Disapprove" function with Order form
    void Disapprove(Order_form request)
    {
    request.Status = "Disapproved";
    }

Configure the functions as Custom Actions

Now let us configure these functions as "Approve" and "Disapprove" custom actions.

  1. Go to Dashboard - Reports - Order - Custom Actions and and create a custom action by entering the field values as show below:
  2. Click on "Create" to create the "Approve" custom action with entered values

    Screen shot displaying the custom actions window for creating the "Approve" custom action

  3. Similarly create "Disapprove" custom action.

Perform custom actions in live mode

The functions have now been configured as custom actions. Let us access the custom actions in Live mode.

  1. Access the "Order" form in live mode.
  2. Enter sample values and click on "Submit.
  3. Now click on Order Report to use the custom actions
  4. Select the required record and approve or disapprove the order using the "custom action" option

    Screen shot displaying the custom actions "Approve" and "Disapprove"

HTML Pages and Search interface

Pages are customized reports created using HTML tags and Deluge scripting. You can insert your form’s field variables into blank HTML pages and make excellent dashboards, presenting dynamic reports based on user selected criteria. The HTML Page Builder provides an intuitive drag-n-drop user-interface to easily create the required pages. You can use Pages to,

  • Create dashboard pages that contains a list of reports to provide your users with quick access to the information they need. For example, display the required reports on the left side of the page, and a table that contains information about the application on the right side.
  • Create customized bills and invoices based on the data stored in your application.
  • Embed forms, views and third party widgets in your HTML view and generate dynamic views by passing form data as parameters to the view.

Lets see an example to create a search functionality using Pages. Using the search option, we will search for an order using the customer name. The returned record will contain the email address and the total amount of that particular order.

Create a Search Form

Create a Search Form with the Fields that would make up a Search Dialog. A search Form is basically a Stateless form, that does not store data in the ZC database.

  1. Click on Create new -> Form
  2. Specify the Form Name as "Search order", Deselect "Data will be stored in Zoho Creator" and click on "Create"
  3. Drag and drop the "Single Line" field and name it "Name"
  4. Click on "+" symbol at the bottom and name it "Search"
  5. Click on "Configure On Click Actions" in "Field Properties" for the "Search" button and add the following code:
    //redirect the form to HTML page named "Search".
    //"searchname" is the declared parameter
    //"manage-orders-workbook-edition" is the application link name
    openUrl("https://creator.zoho.com/" + zoho.adminuser + "/" + zoho.appname + "/" + "#View:Search?searchname=" + input.Name, "Same window");

Create HTML page with Search parameter

Create the required html page with parameter, e.g.searchname, to pass the search key (specified in the above Search form). Write the code for this view to retrieve the records from your Table whose value matches the passed parameter.

  1. Click on Create New -> Page.
  2. Name it as "Search"
  3. Add the following code in the editor box:

    //pass the parameter (searchname) declared in "Search order" stateless form
    //fetch the record from "Order form" 
    //"Order_form" is the link name for the Order form
    htmlpage Search(searchname)
    <%{
    name = Order_form [Name == input.searchname];
    message = "";
    if (name.count() > 0)
    {
    //iterate subform records
    for each x in name.Product
    {
    // subform field name in the main form
    message = message + x.Products;
    // variable.Field_Name_In_The_Subform
    }%>

    //create a table to display the fetched records
    //"Search_order" is the stateless form link name
    <table>
    <tr>
    <td width="222px" valign="top">
    <div elName='zc-component' formLinkName='Search_order' params='zc_FtrClr=_ffffff&zc_InpFieldWidth=100&zc_Header=false'>Please Wait...</div>
    </td>
    <td>
    <table class="zc-viewtable" style="border:0px solid #75b5f2">
    <tr>
    <th>Order Details</th>
    </tr>
    <tr class="zc-row-header">
    <th class="zc-viewrowheader">
    Email
    </th>
    <th class="zc-viewrowheader">
    Products
    </th>
    <th class="zc-viewrowheader">
    Order Value
    </th>
    </tr>
    <tr class="zc-viewrow zc-row-1">
    <td><%=name.Email%></a></td>
    <td><%=message%></a></td>
    <td><%=name.Total_Amount.round(0)%></a></td>
    </tr>
    <tr></tr>
    <tr></tr>
    <tr></tr>
    </table>
    </td>
    </tr>
    </table>

    //display error if no records found
    <div style="text-align: center;">&nbsp;</div> <%}
    else if ((input.searchname != null) && (input.searchname.length() > 0))
    {%>
    <h2>Name Not Present</h2>
    <%}
    }%>

Perform Search in Live mode

Access the application in live mode to use the "Search" functionality

  1. Go to the "Search" page.
  2. Enter a customer name and click on "Search".
  3. It will return records pertaining to the given name. The following screenshot displays the details of cutomer name "Henry":

Note

  • The tag that will set the Deluge tasks apart from the HTML are the <% and %> tags. If Deluge expressions are used within an HTML, the <%= and %> is used to differentiate it from the HTML.
  • The "Search order" form redirects to the HTML page. Hence you can choose to hide the original HTML Page for a better appearance of the application. To hide the page:
    1. Go to Settings -> Sections -> Search Page
    2. Click on Show/Hide to hide the Search page