Post navigation

Zoho Creator charting functionality helps you to quickly and easily analyze data in a visual format using a variety of graph types. To create charts with data from multiple ZC forms or to display grouped data, you can use the chart API supported by Zoho Sheet. The Sample Application named “Student Management”
demonstrates this functionality. In this application, we have defined a
function called “getChartUrl” (you can see the function used in the
application under the Script -> Functions tab in edit mode), using which the required data is dynamically submitted to Zoho Sheet API, which creates the required chart as an image.

About the application

The Student Management application comprises of the following forms :

Student Form to enter the name of the students and the country they belong to.

Course Form to enter the name of the courses offered.

Student Course Form to enter the course details enrolled by each student.

The Country Chart view displays a 3D Bar Chart
which depicts the total number of students enrolled in each course for
the selected country, as shown in the image below. The bar chart is
created by passing the required data as parameters to the Zoho Sheet
chart API.

In the blog topic Zoho Creator URL Patterns we learned about the
default URL patterns to quickly access a Zoho Creator application and its forms,
views and report formats. Advanced URL patterns for Zoho Creator Forms and
Views are listed below:

1. To set default values for form fields

The
default values for form fields can be set by passing additional
parameters to the form URL. For example, to automatically load a value
for Employee ID field in http://creator.zoho.com/zchelp/employee-manager/#Form:Employee
the default values is passed as querysting(Employee_ID=1234) to the
url, where Employee_ID is the script name of the field in your name.

FieldName1 & FieldName2 are the Deluge script names for the field in the form.FieldName1_op & FieldName2_op are parameter names for the operator (fieldname appended by _op).constant is the value assigned for each operator. Refer the table to view the list of all operators and their constants.

Example1:

The view http://creator.zoho.com/zchelp/employee-manager/#View:Employee_View lists all the records in the Employee View. To view records with Employee ID GREATER_THAN 1235, we need to pass the field name, value, parameter name for the operator and its value as querystring. The parameter name for Employee_ID is Employee_ID_op and the constant forGREATER_THAN is 21.

For some operators like EMPTY, NOT_EMPTY, YESTERDAY, TODAY,
NEXT_YEAR, TRUE, FALSE, etc. the criteria value need not be specified.
For example, the following URL will display records from the
“Employee_View” whose “Date of Joining” falls on the “CURRENT_YEAR“.

The knowledge about the URL of the various Zoho Creator application components like Forms, Views, PDF report etc., will help in providing quick access to the application and its forms, views and report formats.

The image given below conveys the url pattern of the various Zoho Creator application components.The wiki page Zoho Creator URL Patternslists and explains in detail the URL structure of the various Zoho Creator application components. Please feel free to post your comments on that page.

In the next series, we will cover more advanced URL patterns for forms and views, like setting form properties, look and feel, view criteria etc. Stay tuned…

You can edit records directly from the Zoho Creator GUI, by selecting the desired record in the view and using the Edit option, to edit the specific record. You can also edit multiple records at a time, using the Bulk Edit option.

Edit Records through Deluge Scripting

You can Fetch and Update records dynamically from Zoho Creator Database using Deluge Scripting. The Fetch record and Update record Deluge task

offers the flexibility to fetch records from the ZC database based on a given criteria and update it with
the given values or with dynamic values and is triggered by form and field actions.

Edit Records by passing record id in the URL

Zoho Creator generates a unique ID for every record that is added to the database. The ID can be displayed in your view by selecting the “ID” field from the “Column Properties” of the View, in Edit mode. The record-edit feature supports to edit individual records via its record ID, by displaying the form with the record details. This makes it easy for users to edit records without having to display the view, navigate to the desired record and then requesting to Edit it. Refer Editing Records via Record ID for more information.

Edit Records through Zoho Creator APIs

Zoho Creator APIsallow client applications to programatically edit records in a Zoho Creator application. You can use the following methods to edit your records.

Zoho Creator offers you the flexibility to add new records to your database, in multiple ways. You can,

Add data through onlilne HTML forms from your PC or from your mobile

Add records through email

Add records by importing from Excel file

Add records by executing Deluge script

Add records using Zoho Creator APIs

1. Add record through online HTML Forms

Zoho Creator provides drag-n-drop Web Form Builder
to easily create custom HTML Forms in minutes. You can access the
online HTML form from your PC or right from your mobile. The data
submitted through these forms will be directly added to your database.
Here is a sample form created with Zoho Creator.

If you do not like the default positioning of the form fields, you can customize the same using the Zoho Creator Client API and create your own custom form. The Client API
allows field level embedding to customize the look of your HTML forms
to suite your website. Each field in the form can be individually
positioned, providing a perfect fit and finish to your web page.

2. Add record through Email

Every form in Zoho Creator has its own email address, so you can just sent an email to submit form data. Zoho Creator checks for form updates via e-mail, once in every ten minutes. The data submitted through e-mail will be directly added to your database. Refer the Help link for more information.

3. Add record by importing from an Excel file

If
you want to create an online database application from your .XLS files,
you can paste data from these files and directly create your
application with Form and Data. Comma separated values (.csv) or Tab
seperated values (.tsv) stored in other file formats can also be used
to create your application with data. Refer Help Link for more information.

You can also add new records to an existing application by importing data from a .xls, .csv, .tsv file. Refer Help Linkfor more information.

4. Add record through Deluge Script

Zoho Creator offers the flexibility to add dynamic data to your web forms by writing a bit of Deluge Scripting. The add record
Deluge task offers the flexibility to add new records to a form, with
the given values or with dynamic values and is triggered by form
actions, field actions or functions. For example, the script can be
executed when form data is submitted or when a field value is updated
or from user-defined functions . A few common scenarios where the add record Deluge task can be used:

Add data submitted from different Stateless forms in one main table. Refer Stateless forms for more information.

Add a record to a form by invoking a Custom Action in the view. Refer Tips & Tricks for more information.

5. Add record using Zoho Creator APIs

Zoho Creator – RPC Web API allows client applications to add record to a Zoho Creator form, using the HTTP POST method. You can add multiple records to multiple applications and multiple forms, at a time.

The Deluge postURL task can also be used to add a new record to another Zoho Creator application, using Zoho Creator API. Refer Add Record using API, for more information on using the POST URL task with Zoho Creator XML RPC API.

A
search interface comprises of a Search Form where users can enter a
search term and view the search result. Zoho Creator supports creating
a Search Interface that display customized views based on the search
keyword. Let us take the Employee Manager application in the Zoho Market place for illustration.

The Dashboard page of the Employee Manager displays the Search form to search employees based on the specified Employee Name, Team Name or Department. On click of the Search button a custom HTML view is displayed, as shown in the screen-shot below.

Here are the steps in creating a Search Interface in Zoho Creator:

1. Create the 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. Refer Stateless Forms for more information on creating Stateless forms.

Add a Custom Button to the Stateless Form.

For example, in the Search form displayed below, we have a field with script name "searchEmployee" and Custom button named Search.

2. Create the HTML View with Search Parameter

Create the required HTML view with parameter, e.g. searchParam 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.

You
can use the drag-n-drop Script Builder in the HTML View, to add HTML
and Deluge code and create the required view. Refer the Help link Creating HTML Views for more information.

For example, in the Employee Manager, the HTML view named Employee_Search is created to fetch and display the required data
whose value matches with the search key that is passed as paramater to
this view. The screen-shot of the drag-n-drop UI to create the HTML
view is given below:

3. Invoke the HTML view on click of the Search Form

Add on click script to the Stateless form to display the HTML view, on click of the Search Button, as shown below:

The on click script, uses the OpenURL Deluge task to redirect the stateless form to the HTML view.

searchUrl – is the variable that holds the url to be displayed when the Search button is clicked. In the Employee Manager application, #View:Employee_Search is the HTML view link, searchParam is the parameter that holds the search key from the searchEmployee field in the stateless form (Search Form).

openUrl – is the Deluge task that redirects the form to the specified url. Refer the Help Doc -> Deluge Reference for more information on the syntax and usage of each Deluge task.

Click Save Script to update the script. In live mode, the Employee Search view will be displayed on click of the Search button.

Creating relationship across forms in Zoho Creator is easy. You can also create combined views with two or more related forms.

Creating relationship using single-select lookup field

Assume you have a Form called Employee to add employee details and another Form called Department
to store the department details. Typically an employee will be
assigned to only one department and hence a relationship between the Employee and Department form is created using single-select lookup field. To do this,

1. Add a Lookup field in the Employee form

Specify a label name, for example, "Department"

Import data from Employee Manager – Department – Department Namewhere,Employee Manager is the application name, Department is the form name,Department Name is the field whose data is imported.

Display as Dropdown/Radio-button (Single select)

Click Done to update the changes

2. In the Employee form, the Department lookup field will display the name of all the departments from the Department Form.

Creating relationship with multi-select lookup field

Assume you have a form called "Roles" that stores the different type of employee roles. Typically an employee can be assigned to one or more roles and hence a relationship between the Employee form and Role form is created using multi-select lookup field. To do this,

1. Add a Lookup field in the Employee form

Specify a label name, for example, "Roles"

Import data from Employee Manager – Roles – Role Namewhere ,Employee Manager is the application name, Roles is the form name,Role Name is the field whose data is imported.

Display as a multi-select lookup.

Click Done to update the changes.

2. In the Employee form, the Role lookup field will display the name of all the Roles from the Role Form. You can relate one or more roles to each employee by selecting the required options.

Creating Combined Views from Related Forms

You can create combined views that display the required fields from one or more related forms. For example, we created three forms – Employee form, Department form and Roles form and created relationship across these forms using Lookup fields. To create a combined view,

From the View tab, select the view which contains the Lookup field. In our example, the Employee form contains the single-select lookup field "Department" from the Department form and multi-select lookup field "Roles" from the Roles form.

Select the Column Properties option displayed on the left-side of the view.

The lookup fields will have the related fields as link. Click on the related fields link to display all the fields in the related form.

Select the required fields to be displayed from the
related form. Here, we have selected the related field "Mail alias"
from the Department form, as shown in the screen-shot below.

Click Done to update the changes.

In Live mode, the combined view will be displayed with the selected fields. For example, in the screen-shot of the Employee view shown below, Mail Alias is a field in the Department form.