This guide aims to explain all the important stuff you need to know to create your online database application in Zoho Creator.
Create New Application
Gather your requirements
Before you create an application using Zoho Creator, it is crucial to know what you want your database application to do. What information do you want it to maintain? Here are some steps to guide you to gather your requirements:
Identify the information you want the application to manage. How can you categorize the information?. For instance, for a Sales database you can categorize the information to be managed under two broad headings - Product and Sales. In Zoho Creator, these categories become the Form in your application
- Next list the items under each category. For example, the Product category will have entries related to Product ID, Product Name, Vendor, Unit Price etc. While the Sales category will have entries related to Product ID, Units Sold, Revenue, Cost, Profit etc. Each individual entry then becomes a Field in the Form.
- Identify the relationship between the data in one category and the data in the other category. In the Sales Database, information in the product category does not have to be repeated in the Sales category. You can create a relationship between the Product form and Sales form, in a manner that Sales will refer to the specific product record from the Product form.
Once you have gathered your requirements, sign-in to Zoho Creator at creator.zoho.com and create the application by clicking Create Application button. You can choose a suitable option from the list. Each option is explained in detail below.
Create application from scratch
There are two ways you can do this. You can either create your application using pre-built template forms that suit your requirement or create your application by adding and customizing the required fields into your form using the Add Form option.
Here are the steps to create an application from scratch.
- In the Create New Application page, select the option From Scratch on the left, as shown in the screen-shot below.
- Specify the Application name. For example, let us create a Sales Database application.
- Now you will be taken to the Form Builder to create your form. Find the Form Properties on the right and specify the Form Name in the text box provided. For example, specify the form name as Product to enter product information.
- By default, you will see the Create From Scratch option selected to help you create your application with blank forms. If you would like to create your application with pre-built form templates select the Use From Template option.
- You can choose to keep your Zoho Creator application Private or make it Public. A Public application will be indexed by search engines and can be accessed by any one. A Private application can be accessed only if the owner of the application shares it with specific users. Shared users can add, modify and delete records based on the edit permissions provided by the owner. By default, an application created in Zoho Creator is "Private".
- A Zoho Creator application can be either Private or Public. A public application can be accessed by anyone and indexed by search engines . A private application can be accessed only if the owner of the application shares it with specific users. Shared users can add, modify and delete records based on the edit permissions provided by the owner. By default, an application created in Zoho Creator is Private.
- Click Create to create the application.
- The application named Sales Database will be created with a blank form named Product. You can add the required fields to your form through easy drag & drop of the required field types, as shown in the screen-shot given below.
- Zoho creator uses Reports to display the data collected via Forms. Reports are nothing but tables that are automatically populated with data added to a Form. By default, every form has its reports created and listed in the Reports link displayed below each form. The name is usually [FORM-NAME] report. In the screen-shot given below, a Product Report is created for the Product Form we created above, and is also listed under the Reports option.
- To submit data into your Forms and view the data submitted, click on the Access this application button. For more details, refer the section Access this application.
- To add new forms, modify forms, configure fields, configure form/field actions or set email notifications, refer to the section called Forms.
- To add new reports, modify and configure reports refer to the section called Reports.
Create application by importing data
You can turn your excel data into a Zoho Creator application in two ways as listed below.
- Copy/Paste Data from the XLS file
- Import Data from the XLS files by specifying the file name
Comma separated values (.csv) or Tab separated values (.tsv) stored in other file formats can also be used to create your application with data.
To create an application by importing data:
- Select the Import File icon from the Create New Application screen.
- Select the Choose Data Location as From Clipboard to copy-paste data from your .xls, .csv or .tsv file (or) Local File to import data from your .xls, .csv or .tsv file by specifying the file name (or) Google Docs to import data from GDocs or choose Download Migration Tool to migrate your data. For example, in the screen-shot given below, we have chosen the From Clipboard option to create an application by pasting data from an excel file.
- Specify the Application Name in the text box provided. This name appears on the Home page when your application is created.
- In the Paste From Clipboard box, copy and paste the data to be imported and click Create to create the application by importing Data.
- If the data contains a date field, select the Date Format that matches the date format contained in your excel file.
- Select the Time Zone for the application from the list of time zones.
- Click on Finish to import the data. The Importing Data dialog will display the data in the following format.
- The Form will be created with the Application Name.
- The data in the first row will be displayed as the default Label Names for each column. If you want to change a field name, type a new name in the Label Name field.
- The default Field Type is displayed based on the type of data pasted for each column. If you findt the field type listed not appropriate to your requirement, you can change it by selecting the appropriate field type from the drop-down list.
- Click Finish to create the application with data. In the screen-shot given below, the application named Sales Database is created with the data and is displayed in the Sales Database Report. To edit the Forms and Reports in the application, click on the Edit this application option.
- To add new forms, modify forms, configure form/field actions or set email notifications, refer to the section called Forms.
- To add new reports, modify or configure reports refer to the section called Reports.
Import MS Access Database
Zoho Migration tool for MS Access & Excel is a simple tool that you can use to upload your existing MS Access applications or XL Spreadsheets to Zoho Creator. All you need is your MS Access MDB file to import the information to this tool. Every table with the relationships intact will be imported, to Zoho Creator as an application or Zoho Reports as a database. In case of excel spreadsheets, you can import the XL file and each sheet becomes a form and corresponding views are created in the application. Click Here, to learn more.
Install from market place
Zoho Creator market place provides the largest repository of business applications for your day-to-day business needs. Application categories vary from IT Management, Sales, Marketing, Human Resources, Education, Non-Profits etc. You can view the demo of the application you require and install it to your account with just the click of a mouse.
To summarize, a Zoho Creator application is basically made up of Forms and Reports. Forms to collect the information from the user and store it in the database and Reports for viewing, filtering and looking up the information collected via Forms.
In this section we will briefly explain the various configurations that you can make to the Forms and Fields in your application.
Create New Forms
To add new forms,
- Click on Create New -> Forms option. In the New Form page,
- Choose the required form type form Select Form Type option. You can either create a new form from scratch or use an existing template form or import file to create a form.
- Specify a name for your form in the Form Name field.
- By default, the option Store data in creator is selected indicating that the data entered through this form will be stored in the database. (i.e) a unique table will be created for this form at the back-end to store the data from the form. De-selecting this option will create a Stateless form. The data entered in a Stateless form will not be stored in the database.
- In the homepage of your application, Click on Create New-->Form.
The new form page appears as shown below. The name of the form and the form type is given by the user. Once the details are given and create button is clicked, the form is generated. The form can be accessed from the homepage of your application under which the form is placed.
- For more information on Stateless Forms and their usage, refer the section Stateless Forms
Zoho Creator supports 20 types of form fields. You can add fields to your form and also re-arrange them through simple drag and drop. Choose a field type based on the kind of data that it should contain. By default, Zoho Creator supports validation for an adequate number of field types. For example, if you want users to enter their email-id in a certain field, select the Email field type. Zoho Creator checks if the data entered in the form for that field is a valid email-id, else it will display an error message to the user.
- Drag-n-drop the required field type from the Fields box displayed on the left,as shown in the screen-shot given below.
- In the Field Properties, specify the Field Nameas shown in screen-shot. The field name is the name displayed for the field in your Form. There are various additional parameters that can be configured for all the field types, except for the Formula field type. You have to specify the required formula expression based on which the value of the field will be created. Refer Formula fields for more information.
Zoho Creator provides the following options to configure your field.
- Force users to enter a field value by making it a required field
- Set a field as unique so that it will not accept any duplicate values.
- Set maximum characters for entries in a text/number field.
- Hide a field from being displayed in the form and make it accessible only to the application owner.
- Set an initial value for a field.
- Provide tool tip help for your fields.
Create relationship between Forms
A relationship is a link between two Forms. You can create relationship between forms in the same application or between different applications. To create a relationship between Form A and Form B, you have to add a Lookup field in Form B, imported from Form A.
How do I do this?
Let us take the example of the Sales Database application we created in the Create application from scratch section with a Product Form. We will now create the Sales Form to record the sale of each product, with fields ProductName, Units Sold, Revenue, Cost, Profit etc. Here, the field ProductName will reference the ProductName field from the "Product" Form. To do this,
- Add a Lookup field in the Sales form.
- Import data from, Sales Database - Product - Product Name
(App name - Form name - Field name)
- Display as Dropdown (Single select)
- Click Done to import the data.
- In the Field Properties, specify the Field name in the text box as Products.
- The lookup field Product which refers the Product Name in the Product form is added as shown below. Using the lookup field you can access the product details from the Product form.
Set email notification
Zoho Creator Form Builder provides an option to configure email notifications to be sent when new form data is submitted by the user.
How do I do this?
- Select Form Settings icon -> Email from the form header to configure email notifications.
- The Set Email Notification dialog is displayed, where you can specify the From address and To address of the mail recipient. The Cc, Bcc and Reply-to options can be added by selecting the required link displayed below the To address. You can also assign e-mail ids and form field values dynamically to the From/To and Message fields. To do this, click on the icon displayed on the right-side of the "From", "To" text box or the Insert Field button displayed in the Message header. This will display the Zoho Variables and the form fields, as shown in the screen-shot given below:
- The Zoho Variable zoho.adminuserid returns the email-id of the application owner.
- The Zoho Variable zoho.loginuserid returns the email-id of the currently logged-in user.
The fields in this form are listed here. Select the field whose value has to be dynamically assigned to the From/ To/cc /message area.
- Selecting the check-box Include User Submitted Data will include the data submitted in the form in the email message.
- Make sure that the Enable Email Notification box at the top of the pop up window is checkeded. To stop email notifications, set the uncheck the Enable Email Notification.
- Select Save to save settings or Reset to return to the form.
You can also send email notifications when existing form data is modified or deleted or for selected records in a view, by adding Deluge Script with the Send mail task.
Configure Form actions
Make your application powerful and dynamic by adding complex logic to your forms with the help of Deluge Scripting. The Deluge Script Builder provides a drag-and-drop user interface to add form action scripts. It can be invoked from the form header by selecting Settings icon in the edit mode or by directly selecting the Script tab
You can configure the following action scripts to your form, when a new record is added or when an existing record is modified or deleted.
"On load" scripts are executed when the form is loaded for adding or editing a record. For example, set a date field in the form with the current date or hide a field that you do not want to display.
"Validate" scripts are executed when a new record is submitted or when an existing record is modified and submitted or when an existing record is deleted. These scripts are executed before the form data is stored in the database. For example, check if a field value falls within a specified range, restrict the number of registrations to your form etc.
"On success" scripts are executed when a new record is successfully submitted to the database or when an existing record is updated or deleted from the database. These scripts are executed after the form data is stored in the database. For example, send a mail to the admin user_id when a record is deleted.
"On click" scripts are executed on click of the custom buttons that you have created in your Stateless Forms. For example, create a stateless form to specify the search criteria and on click of the Search button display a HTML view with the search results fetched from a Contacts Management form.
Actions on Form load
The Sales Form in the Sales Database has a field called Sale_Date. In the screen-shot given below, we have configured an "on load' script to set the "Sale_Date" field with the current date, when the form is loaded. To do this,
- Select the Sales Form from application dashboard.
- Select Workflows from the left and select Form Actions -> On Add -> On Load.
- Drag & drop the Set Variable Deluge task to the editor area.
- Click Edit and select the variable as Sale_Date and the value as the Zoho variable zoho.currentdate. For more information on Zoho variables refer the topic Deluge System Variables.
- Save the script. When the form is loaded the Sale_Date field will be displayed with the current date.
- To execute an On Load script when a form is loaded to edit a record, select Form Actions -> On Edit -> On Load and add the required script.
"Validate" Form action
The Sales Form in the Sales Database has a field called Units_Sold. In the screen-shot given below, we have configured a "Validate' script to check if this field contains a value of 5 or more. If not, an alert message is displayed and the submission is canceled.
To do this,
- Select the Sales Form from application dashboard.
- Select Workflows from the left and select Form Actions -> on Add -> Validate
- Drag & drop the if Deluge task to the editor area.
- Click Edit and specify the if condition as "input.Units_Sold < 5"
- Drag & drop the alert Deluge task and specify the required alert message,
- Drag & drop the cancel submit Deluge task.
- Save the script. When the form is submitted, the value of the Units_Sold field will be validated. If it is 5 or less, the data will not be submitted.
- To execute a Validate script when a record is submitted after editing, select Form Actions -> On Edit -> Validate and add the required script.
- To execute a Validate script before deleting a record, select Form Actions -> On Delete -> Validate and add the required script.
Actions "On success"
In the screen-shot given below, we have configured a "On success" script to redirect the user to a particular url when a record is submitted to the "Sales" form.
- Select the Sales Form from application dashboard
- Select Workflows from the left and select Form Actions -> on Add -> On Success
- Drag & drop the Open URL Deluge task to the editor area.
- Click Edit and specify the url to be opened and select the option to open the url in the same window, new window, parent window, popup or iframe.
- Save the script. When a new form data is submitted, the openUrl task is executed which redirects the user to the specified url.
- To execute a On Success script when a record is submitted after editing, select Form Actions -> On Edit -> On Success and add the required script.
- To execute a On Success script when a record is deleted, select Form Actions -> On Delete -> On Success and add the required script.
Actions on click of Form button
Zoho Creator supports creating stateless forms that does not generate a table at the back-end. You can add custom buttons to this form and write deluge script in the "on click" event of the custom button. The scripts can be programmed to do anything that you want to do in Deluge Script. This gives you the flexibility of storing the entered data into any field of any record of any Table in Zoho Creator. You can even use it to retrieve data from any field in any record of any Table in Zoho Creator. Refer the Help topic Stateless Forms for more information.
Refer the topics under Deluge Reference for more information on the description, syntax, examples of all the Tasks and Expressions supported by the Deluge Language.
Configure Field actions
You can add scripts to perform actions on specific fields in your form. You can validate specific field data, display other field values based on data entered into a field, send mail when a field value is updated etc. The scripts that are configured at the Field level are classified as On user input script and On update script. To add a Field action script, select the option Configure Field Actions, as shown in the screen-shot below. This will display the Deluge Script Builder.
- Actions on user input script is a client side action which will be called whenever the value of a field is modified either by the user or through the script. It is used to improve the usability of a form by validating field data even before it is submitted or display other field values based on the value specified in this field. The On user input script is executed before the changed data is persisted in the database. The On User input script will not be called if there is no change in the value of the field (i.e if the new value being set is same as the old value.)
- Actions on update script is executed whenever the value of a field is modified and the changed data is persisted in the database. For example, send mail if the value of a status field is updated.
Field Actions -> On User Input
The Sales form in the Sales Database has a field called Units_Sold. In the screen-shot given below, we have configured a On user input script to calculate the Revenue based on the number of units sold for a product. The script will be executed when the user inputs the value in Units_Sold.
- Select the Sales Form from the Dashboard.
- Select Workflows from the left and click on Field Actions -> Units_Sold (field name) -> On user input.
- Drag & drop the Fetch Deluge task to the editor area. Select the form "Product" whose name is the same as the product selected in the "Sales" form, store it in a variable named "rec"
- Drag & drop the Set Variable Deluge task to the editor area. Click Edit and select the field "Revenue" and set it with the value of (input.Units_Sold * rec.Unit_Price)
- Save the script. When the user inputs the value in Units_Sold field, the script will calculate the "Revenue" and set it in the Revenue field.
Field Actions - On Update
If you want to perform action whenever the value of a field is modified, you can write an on update script for that field. The on update script is executed after the changed data is persisted in the database.
Refer to the topics under Deluge Reference for more information on the description, syntax, examples of all the Tasks and Expressions supported by the Deluge Language.
Zoho creator uses Reports to display the data collected via Forms. Every form by default has a Report. The name is usually [FORM NAME] Report. For example, if you have created a Product form, a default report named Product Report will be created in the Reports Tab as shown in the screen-shot below. You can create any number of reports with criteria, filters, grouping and sorting from the Reports tab. To submit and view data, click on the Access this application button.
Create a New Report
You can add any number of Reports for a single form. To add a new report,
- Select the Create New -> Report option.
- Zoho Creator supports the following report formats. You can display your form data as a List, Calendar, Summary, Grid, Pivot Table and Pivot Chart.
- Specify the Report name, Select the Report type, select the Form based on which the report is created and set the criteria.
- Click Create to create the View.
- The data entered in the Form will be displayed in the Report. To view the data submitted, click on Access this application button.
Set criteria and filters
Zoho Creator allows you to easily create custom views for specialized reporting needs. You can:
- Set Criteria: Configure a report to display records that match a specific criteria. For example, in the screen-shot given below we have configured the Sales view with criteria to display the sales revenue that crosses $3000. You can also set complex criteria with AND/OR operators or a combination of both, from the GUI using the Add Group option or using Deluge Script.
- Set Filters: Filters are a set of named criteria that allows users to view specific records in a view. Default filters will be created for drop-down and date fields. You can create custom filters by selecting the New Custom Filter button.
- Set complex criteria and filters using AND/OR operators or a combination of both, from the GUI or using Deluge Script.
Change Field order
- Zoho Creator allows you to re-arrange the field order by easy drag-n-drop.
- Select the report from the Reports tab
- Select Report Settings -> Column Properties
- To change the order of column drag and drop the columns to the desired location, as shown in the screen-shot given below
Show/Hide columns, Change display name, Sum numeric columns
- Select Form Settings Column Properties option.
- Mouse over the column name and click on the delete button to hide the field or drag and drop the required fields from the left to show the fields.
- Click on the column name and enter a new name to change the display name of columns in the report.
- Enable summation for numeric fields by clicking on the summation icon that is displayed.
- The username and time when a record is added or modified and their IP address is automatically recorded by Zoho Creator. To display this information in the report, select the required column with name Added User, Added Time, Last Modified User, Last Modified Time, Added User IP Address, Modified User IP Address.
- Click Done to update the changes.
- Group Records
- Records can be grouped by specific column(s) - by status, by category etc to view records in a organized way. You must select at least one field for grouping by selecting Set Grouping and Sorting option. Records with same value for the selected field(s) will be grouped together.
- Sort Records
- By default, records are sorted by the order in which they are added. Latest records come on top.
- To sort by another field, select the Report Settings Set grouping and sorting option and drag and drop the field from left hand side.
- To change the order of sorting, select the field and click the Sort Asc/Desc button.
- Click Done to update the changes.
Shared users of a private application or users of a public application will be able to add, edit or delete records in a report only if access permission is provided by the app owner. The Permissions option is used to control the access permissions provided to the users. However, the app owner will be able to add, edit or delete records irrespective of whether the access is disabled/enabled to the user.
To invite users to share your application, refer Sharing.
Perform custom actions
Custom actions are function calls that can be invoked on selected records in a report. For example, you can define a function that sends an email and configure that function as a Custom action for this report. You can then invoke the action for selected records in the live mode. For more information on defining functions and configuring them as custom actions, refer the topic Functions.
Create combined Report
You can create a combined report with two or more related forms. As demonstrated under the topic Create Relationship between Forms, we created two forms - Product Form to enter product details and Sales Form to enter sales details and established relationship between the two forms. A combined report will display columns from two or more related forms.
To create a combined report from the Product and Sales Form:
- Create a report with the form which contains the Lookup field. In our example, the Sales form contains the lookup field "Product" that relates to the product form.
- Select the Form Settings -> Column Properties option.
- The lookup field will have the link. Click on the link, and select the columns to be displayed from the product form
Create Pages (HTML view)
One of the most powerful features in Zoho Creator is the ability to create Pages. The Pages enables you to create customized reports by combining HTML code and Deluge script. You can create a static HTML page as a report and use this page to deliver dynamic content from your database using Deluge Script. The HTML Builder provides an intuitive drag-n-drop user-interface to easily create the required Pages. Knowledge of HTML is not necessary.
For more information refer Pages
Customize Look & Feel
The Customize option offers a variety of layout and themes to customize the appearance of your forms and views. Layouts and Themes can be combined together to create a visually appealing website.
A layout is the arrangement of the Forms and Views in a Zoho Creator application. Select the required layout for your application from the Settings -> Customize -> Choose Layout tab.
Themes define the color scheme of the application viz the colors that will be applied to the header strip, the component header, the buttons on the forms, etc., the over all font that will be applied to the application, the different styles that will be applied to the headers/tabs/ component headers/ forms/ views etc . You can pick from a range of themes from the Settings -> Customize -> Choose Theme tab and also create your own customized themes, to suit your requirements. For example, let us select the Gradient theme with the required color and click on Apply.
Select Settings -> Sections to arrange the forms and reports under different sections or to add new sections. By default, all the forms and reports will be listed under the Home section.
Access the application
A Zoho Creator application can be either Private or Public. A public application is indexed by search engines and can be accessed by anyone. A private application can be accessed only if the owner of the application shares it with specific users. Shared users can add, modify and delete records based on the edit permissions provided by the owner. By default, an application created in Zoho Creator is private. The owner of the application can share the entire application -or- can share few form(s)/view(s). Users must have a Zoho Creator account to access the application shared with them.
To share the application:
- Click on Application Settings -> Share option.
- Select the Sales Report and click on Share to Users. If you want to share the entire application with users (i.e.) all the forms and views of the application, you can select the Sales Database section and then add users.
- Add the list of e-mail address of people with whom you want to share the application
- If you want to send them an invitation, select the check-box Send Invitation.
- Click Share, to share the Contact form. The shared e-mail addresses will be listed under Shared Details.
- The number of users with whom the application is shared, depends on your subscription plan. Click on the Go To -> Account Settings option displayed on the top right corner to view your current plan details and the list of shared users.
Embed in Website
Zoho Creator lets you embed Forms and Reports in your website/blog just by pasting few lines of code. You can also customize the look and feel of the embedded form/report to match your website. This makes it easy for your website visitors to fill in their data with no hassle.
To embed a form, select the form to be embedded and click on Settings icon -> Embed in your website.
To embed a report, select the report to be embedded and click on Settings icon -> Embed in your website.
For an Pages, the embed option will be available only in the edit mode. Hence, select the required HTML view from the Pages option, and click on More Actions -> Embed in your website from the view header.
Screen-shot of the view Embed dialog with the iframe code
Refer the topic Embed Forms and Reports for more information.
Import and Export Data
- Import existing spreadsheets or .csv/.tsv files into Zoho Creator and have your database application up and running in minutes. You can create a new application via import (i.e convert your .xls/.csv /.tsv data into a new Zoho Creator application) or import data to your existing Zoho Creator application. Refer the the topic Create new application by importing data for more information.
- Select the view from which you wish to export the data.
- Click on Settings icon -> Export Data. The links to the supported export formats will be displayed, as shown in the screen-shot given below.