Creating Charts in Zoho Creator using Zoho Sheet API

Posted by Posted on by
0

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.

To learn about the steps in creating a chart in Zoho Creator using the Zoho Sheet API, refer this Wiki Page.
Creating charts using Zoho Sheet API

Functionality based URLs in Zoho Creator

Posted by Posted on by
8
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.

The form url with query string will be,
http://creator.zoho.com/zchelp/employee-manager/#Form:Employee?Employee_ID=1234


2. To edit individual records by passing record ID in the URL

The
url to view and edit individual record using its record ID is given
below. This will display the zoho creator form in edit mode.

http://creator.zoho.com/<zoho username>/<applinkname>/#Form:<formLinkName>?recLinkID=<recordID>&viewLinkName=<viewlinkname>

Refer the topic Editing Records via Record ID for more information.

3. To set criteria for a view

The
criteria for a view can be passed as part of a query string to a view
url. Multiple criterias can be specified by appending it along with the
URL using ‘&’

The URL format for setting criteria with “=” (Equal) operator is given below:

http://creator.zoho.com/<zoho userName>/<applinkname>/#View:<viewlinkname>/<FieldName1>=<FieldName1 Value>
&<FieldName2>=<FieldName2 Value>

where, FieldName1 & FieldName2
are the Deluge script names for the field in the form and multiple
criterias are specified by appending it along with the URL using ‘&’

Example: 

The view http://creator.zoho.com/zchelp/employee-manager/#View:Employee_View lists all the records in the Employee View. To  view records with Name EQUALs Henry and Employee ID EQUALs 1234, we need to pass the field name and value as querystring.

The complete  url for this example is,
http://creator.zoho.com/zchelp/employee-manager/#View:Employee_View?Name=Henry&Employee_ID=1234

The URL format for setting criteria with operators other than “=” (Equal) is given below: 

http://creator.zoho.com/<zoho username>/<appLinkName>/#View:<viewLinkName>/<FieldName1>=<FieldName1 Value>
&<FieldName2>=<FieldName2
Value>&<FieldName1_op>=<constant>&<FieldName2_op>=<constant>

 where,

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 for GREATER_THAN is 21.

The complete  url for this example is,
http://creator.zoho.com/zchelp/employee-manager/#View:Employee_View?Employee_ID=1235&Employee_ID_op=21

Example2:

The URL to view records with EmailID ENDS_WITH zoho.com and Roles CONTAINS Supports,
http://creator.zoho.com/zchelp/employee-manager/#View:Employee_View?EmailID=zoho.com&EmailID_op=25&Roles=Support&Roles_op=26


Note:

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“.

http://creator.zoho.com/zchelp/employee-manager/#View:Employee_View?Date_of_Joining_op=52 


Zoho Creator URL Patterns

Posted by Posted on by
8

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 Patterns lists and explains in detail the URL structure of the various Zoho Creator application components. Please feel free to post your comments on that page.

Zoho Creator URL Patterns

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…

Editing Records In Zoho Creator

Posted by Posted on by
0

Zoho Creator supports multiple ways of editing your data.  You can,
  • Edit records from GUI
  • Edit records through Deluge script
  • Edit records by passing record id in the URL
  • Edit records through Zoho Creator APIs

Edit Records from GUI

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 APIs allow client applications to programatically edit records in a Zoho Creator application. You can use the following methods to edit your records.

Update Record using Rest API – Post method
Update Record using Rest API – Get method
Update Record using XML RPC
Update Record using CSV RPC

Adding Records in Zoho Creator

Posted by Posted on by
6


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 Link for 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.

Creating a Search Interface in Zoho Creator

Posted by Posted on by
12

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 = "#View:Employee_Search?searchParam=" + input.searchEmployee;
openUrl(searchUrl, "same window");


where,

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.

How To – Create relationship across forms in Zoho Creator

Posted by Posted on by
2

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,

  1. 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.
  2. Select the Column Properties option displayed on the left-side of the view.
  3. 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.
  4. 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.
  5. 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.