Advanced Scripting

Level: Advanced Duration: 1 hour

Recap: Created a simple order management application with Product and Order Forms.  Added business logic using the Deluge basic tasks to perform the following actions: (screen-shot of the order form with values to be embedded side-by-side)

• On Form Load - Set order date field with current date, Set email id field with login users email id.
• On User input - Changed delivery type based on number of products selected.
• On Validate - Checked minimum number of products in a order.
• On Success - Sent confirmation email to the user.

Enhancement to the Order Management Application

In this tutorial, we will go a step further and make enhancements to the order management application to calculate total price of an order, update available stock, create purchase order, create customized reports and also learn how to replace existing code with reusable functions.

Calculate Total Price

In this section, we will learn how to fetch and update the records stored in your Zoho Creator Forms.

1. Select the Order form.
2. Drag-n-drop the currency field type to the Order form.
3. On the left-side, specify the field name as "Total" .
4. Leave the defaults for the remaining fields properties.
5. The "Total" field will be auto saved to the Order form.  
6. Now, we will disable the "Total" field since it is not required to be edited by the user, by adding a simple script to the on load section of the form.  To do this,
   • Click on Form Settings > Workflow -  On Add > Actions on Load. This will display the script builder.
   • Add the disable <field> task in the script builder area, as given below.
     disable Total;
   • Click Save to add the script 
7. Now, when a user fills up the order form with Name, Email, Date, Products and Delivery, the total price of the order will be calculated and updated in the Total field. To do this, we will add a field action script to the Products field. The script will be executed when the user selects one or more products from the product list.
8. Select the "Products" field and click on "Configure field actions" link displayed on right-side.
9. This will display the script builder with the Products -> On user input section selected.
10. Add the following script to the builder area. Comments are added to explain the code.

    //Set Total value with 0.0 USD
    input.Total = 0.0;
    //loop through all the selected Items and calculate total
    //"For each list" task is used
    for each itemID in input.Products
    {
    //Get the Product Row from the Products form
    //Fetch record task
    productRow = Products [ID == itemID];
    //Calculate Total Price
    input.Total = (input.Total + productRow.Price);
    }

11. Click Save to add the script.
12. When you access the application and add a record to the order form, the total value will be calculated and displayed in the Total field as shown in the screen-shot below.

    

Automate Inventory management

In the order management application, the product details are stored in the product form, as shown in the screen-shot below. In this section, we will learn how to update the available stock when an order is made.

To update available stock of the products listed in the product form,

1. Click on Form Settings - On Add - Actions on success. This will display the script builder.
2. Add the following script in the script builder area, as given below.

   //loop through all the selected Items and update the Inventory
   //for each list task is used. 
   for each itemID in input.Products
   {
   //Get the productRow from "Products" form
   //Fetch record task is used. Here, productRow is the variable.
   productRow = Products [ID == itemID];
   //Update the Product stock in "Products" form
   //Update record task
   productRow.Available_Stock = (productRow.Available_Stock - 1);
   }

3. Click Save to add the script

Generate Purchase Order

In this section, we will learn to use the scheduler feature to monitor the available stock in the "Products" Form and raise a purchase order if stock is less than the threshold value.

1. Create a new "Purchase Request form with fields Product, Quantity, Status,
   • Click on Create New - Form.
   • Drag-n-drop the single-line field type and specify field name as "Product".
   • Drag-n-drop the number field type and specify the field name as "Quantity"
   • Drag-n-drop the drop-down field type and specify the field name as "Status" with choices "Pending" and Completed"

     

2. Create a  custom schedue to monitor available stock in Products Form and create a PO if stock is less than the threshold value.
   • Go to the Products Form
   • Select Workflow > Schedules
   • Click on New Schedule > Custom Schedule to display the custom schedule page.
   • In the "When to do" section, specify the details as to when the schedule should get executed, as shown in the screen-shot below.  Here, we have configured the schedule to be executed everyday at 12:00 pm.
     
   • In the "What to do" section, we will add the following script to create a PO if stock is less than the threshold value.

     //iterate all products that have less than 5 stock
     //use for each record task
     for each product in Products[Available_Stock < 5]
     {
     //Automatically make a request using "Add Record task"
     //The Add Record task adds a new record to the Purchase_Request form with Status="Pending"
     insert into Purchase_Request
     [
     Added_User = zoho.loginuser
     Product = product.Product
     Quantity = 5
     Status = "pending"
     ]
     }

3. The above schedule will be executed everyday at 12.00 pm. It will check the available stock of each product in the product form and if the stock is less than 5 units, a new record is added to the purchase request form to raise a PO.

Replace code with user defined functions

In this section, we will create user-defined functions that will replace the stock handling code added to the on add - on success block of the Order Form.

1. Create the function 
• Select WorkFlow - Functions tab.
• Click on New Function button displayed on the top-left corner of the Functions page, to add a new function.
• Select the radio button Write script / Copy sample function.
• Copy-paste the function given below in to the blank text-box and click on Done to add the function.

  void updateStock(list Items)
  {
  //loop through all the selected Products and update the Inventory
  //for each list task
  for each itemID in input.Items
  {
  //Get the productRow from "Products" form
  //Fetch record task
  productRow = Products [ID == itemID.toLong()];
  //Update the Product stock in "Products" form
  //Update record task
  productRow.Available_Stock = (productRow.Available_Stock - 1);
  }
  }

2. The function named updateStock will be listed in the Functions tab. Click on Save Script to save the function code.
3. Replace the Stock handling code with updateStock Function
• Select the Order Form and click on Form Settings - On Add - Actions on Success. This will display the script builder with the scripts added to this block.
• Replace the stock handling script with the call function code, given below. 

• /*reusable function to update stock*/
  thisapp.updateStock(input.Products);

4. Click on Save Script to update the changes. You will now find the code in the on success block is more readable and easy to manage. You can reuse the function code whereever stock handling is needed.

Generate Custom Reports using Scheduler

In this section, we will learn to use the aggregate functions like sum, count, avg etc., to calculate total sales, todays sales and average sales for a specific locality. The values will be sent as a daily report to the admin user using Scheduler in Zoho Creator. 

• Go to the Products Form
• Select Workflow > Schedules
• Click on New Schedule > Custom Schedule to display the custom schedule page.
• In the "When to do" section, specify the details as to when the schedule should get executed, as shown in the screen-shot below.  Here, we have configured the schedule to be executed everyday at 5:00 pm.
  
• In the "What to do" section, we will add the following script

  /*count all orders in Order form whose value is greater than 1000 USD*/
  countOfAllOrders = Orders[Total > 1000.0].count();

  /*get the total sale*/
  totalSale = Orders.sum(Total);

  /*Today sale*/
  todaySale = Orders[(Order_Date == zoho.currentdate && Order_Status == "Delivered")].sum(Total);

  /*this month sale*/
  thisMonthtSale = Orders[(Order_Date in this month && Order_Status == "Delivered")].sum(Total);

  /*get the total sale in California*/
  totalSaleInCA = Orders[State == "California"].sum(Total);

  /*get the average sale in California*/
  avgSaleInCA = Orders[State == "California"].avg(Total);

  /*get the maximum sale price in California*/
  maxOrderPriceInCA = Orders[State == "California"].maximum(Total);

  /*get the minimum sale price in California*/
  minOrderPriceInCA = Orders[State == "California"].minimum(Total);

• Configure the Send mail task in "Scheduler" using the above functions, as shown below.
  
• When the Send mail task is executed, an email will be sent to the admin user in the following format.
  

Summary

Given below is a brief summary of the Deluge tasks used in each section of this tutorial.   

• Total Calculation - For each record task, Fetch record task
• Update Inventory - Fetch record task, Update record task
• Generate Purchase Order - Scheduler
• Generate Customized Reports - Aggregate record task , Send mail task