Home  »  Creating Data Entry Forms for Zoho Reports using Zoho Creator

Creating Data Entry Forms for Zoho Reports using Zoho Creator

Tags:  

Creating Data Entry Forms for Zoho Reports using Zoho Creator



Zoho Creator is a popular Online Database Application creation service from Zoho. With Zoho Creator you can easily create powerful Web applications with Forms for collecting any type of data.  Zoho Reports allows you to make use of these data entry Forms created with Zoho Creator to add data into your tables in Zoho Reports database. These Web Forms that you create in Zoho Creator can be used privately by you or can be privately shared to your colleagues/users for collaborative data collection or published as interactive Web Forms in websites, allowing users to submit data. The data submitted through these Web Forms can be made to be saved in your Zoho Reports tables for further analysis & reporting.

The following sections will describe in detail, how to create such Web Forms using Zoho Creator for entering data into Zoho Reports.

Creating a New Form in Zoho Creator

Let's say you have Sales table in a database called StoreSales in your Zoho Reports account as shown below and you want to create a data entry Web Form to this table. To do this, follow the steps given below.



  • Login to Zoho Creator (http://creator.zoho.com/) with the same login credentials that you use to login to Zoho Reports. 
  • If you have already created an Application in Zoho Creator you can open the existing application. If not, create a new application by clicking the Create New Application button available in the home page. 
  • If you create a New Application then as part of the process it will ask you to provide a Form Name as shown the screen shot below. Also choose None for the option Choose a Form Template.

  • If you are opening an existing Application then go to the Edit mode of the application by clicking "Edit Application" Option.  Then create a new Form by selecting New Form option from Forms tab.
  • Select Add Form icon in the opened tab and specify the new Form name in the Specify Form Name ( eg., Sales Form) text box as shown below.



  • Deselect the option Store Data in Creator and click Done. By default, Zoho Creator will have a table generated in the server to store data submitted through the Form. To save the submitted data into a Zoho Reports table instead of in a Zoho Creator table, this option should be deselected.

    Note :

    Store Data in Creator option is not shown when you are creating a New application with blank Form. This option will only be shown when creating a New Form in an existing application. In this case, instead of using the first Blank form that you have created, create another form and follow the given instructions.

  • A new blank Form named Sales Form will be created and listed under the Forms Tab.
  • Now, drag and drop the required field types, depending on the kind of data that the Form should collect, from the Insert Fields box displayed on the left-side of your Form as shown below.



  • Enter the name for this field in the dialog that appears and click Done.
  • After you have created the required fields, click on the  link present at the right side bottom, to add a button to the Form.
  • Specifying the button name (Add to Zoho Reports Database in the example shown here) and select the Button Type option as 'Submit'.
  • Click Done . The resulting Form should like the one shown in the screen shot below.

On Click Script

After you create the Form, you have to write a simple Deluge script which executes on clicking the button. Deluge script is an online scripting language which enables you to add the required logic to the Form to perform an action.

On Click Script to Add Data:

To add data submitted via Form, as a new row into the table in your Zoho Reports account, we have to write a deluge with createRow() task.
 
 Syntax: A generalized format of the deluge task to Add data is:

<return value as map> = zoho.reports.createRow(<Database Name>,<Table Name>,<Data as map>);

<Database Name> is the name of the reporting database in Zoho Reports. 
<Table Name> is the name of the table in the corresponding reporting database in Zoho Reports. 
<Data as map> is the map variable that holds key, value pairs. The map key is the column name of the table in your Zoho Reports account and the map value is the field value submitted in the Zoho Creator Form.
<return value as map> is the map object containing 
response returned by Zoho Reports.

Let's continue with the above example to illustrate how to use createRow() task to add data. Once you have dropped the required fields and added Add to Zoho Reports Database button to the Sales Form,

  • Hover your mouse over Add to Zoho Reports Database button and select Action on Click option. This will invoke the script builder in the Script tab.
  • Switch to Free-flow Scripting mode by clicking on the option present at the right side top corner of the script editor
  • Write the following deluge script as shown below in script editor and click Save Script.
     
    creatingMap = map();
    creatingMap.put("Date", input.Date_field);
    creatingMap.put("Region", input.Region);
    creatingMap.put("Product Category", input.Product_Category);
    creatingMap.put("Product", input.Product);
    creatingMap.put("Customer Name", input.Customer_Name);
    creatingMap.put("Sales", input.Sales.toString());
    creatingMap.put("Cost", input.Cost.toString());
    mapResponse = zoho.reports.createRow("StoreSales", "Sales", creatingMap);


In the above deluge script,
  • creatingMap is the map variable that holds the column names(Date, Region, Product Category, Product, Customer Name, Sales and Cost) and their corresponding values submitted in the Sales Form.
  • mapResponse is the variable which holds the XML string returned by Zoho Reports as response on adding the row.

When a user enters data and clicks Add to Zoho Reports Database button in the Sales Form, the given deluge script executes and adds submitted data as a new row into the Sales table in your Zoho Reports account

On Click Script to Update Data

You can also use a Zoho Creator Form to update existing data of a table in your Zoho Reports account. To update a row in Zoho Reports using a Form created in Zoho Creator, we have to write a deluge script with task updatedata(). A generalized format of the deluge task to Update row(s) in Zoho Reports is:
 
Syntax:
 <return value as map> = zoho.reports.updatedata(<Database Name>,<Table Name>, CreatingMap,  <Criteria>);
<Database Name> is the name of the reporting database in Zoho Reports. 
<Table Name> is the name of the table in the corresponding reporting database in Zoho Reports.

<Data as map> is the map variable that holds key, value pairs. The map key is the column name of the table in your Zoho Reports account and the map value is the field value submitted in the Zoho Creator Form. 
<Criteria> must be in the format:  {<colName>  <operator>  <colValue>}. For example ("Region"='East'). Refer here for more details about the format for the criteria.
<return value as map> is the map object containing response returned by Zoho Reports.

In the given example, to Update an existing row in the Sales table of the StoreSales Database in your Zoho Reports account with the submitted data via Sales Form,

  • Add a button (say, Update button) to the Sales Form following the same steps as described above.
  • Hover your mouse over the Update button and select Action on Click option. This will invoke the script builder in the Script tab.
  • Switch to Free-flow Scripting mode by clicking on the option present at the right side top corner of the script editor.
  • Write the following deluge script as shown below in script editor and click Save Script.

creatingMap = map();
creatingMap.put("Date", input.Date_field);
creatingMap.put("Region", input.Region);
creatingMap.put("Product Category", input.Product_Category);
creatingMap.put("Product", input.Product);
creatingMap.put("Customer Name", input.Customer_Name);
creatingMap.put("Sales", input.Sales.toString());
creatingMap.put("Cost", input.Cost.toString());
mapResponse = zoho.reports.updatedata("StoreSales", "Sales", creatingMap, "Region =
'East' and Sales =10000 ");
When a user enters data in to the Sales Form and clicks Update button, the given deluge script executes and all the rows of Sales table in StoreSales database with Region as 'East' and Sales of '10000' will be updated with the submitted data.

On Click Script to Delete a Row:

To delete a specific row of a table in your Zoho Reports, we have to write a deluge with deleterow() task. 

Syntax: Format of the deluge task to Delete row(s) in Zoho Reports is:

 <return value as map> = zoho.reports.deleteRow(<Database Name>,<Table Name>, <Criteria>);

<Database Name> is the name of the reporting database in Zoho Reports. 
<Table Name> is the name of the table in the corresponding reporting database in Zoho Reports.
 
<Criteria> must be in the format:  {<colName>  <operator>  <colValue>}. For example ("Region"='East'). Refer here for more details about the format for the criteria.
<return value as map> is the map object containing response returned by Zoho Reports. 

For
example, to delete all the Sales data of the 'West' region from the Sales table of the StoreSales Database in your Zoho Reports account, deluge script as shown below should be written in the script builder.
s = zoho.reports.deleteRow("StoreSales", "Sales", "Region='West'");

Entering Data in the Form

To enter the data in the Form and add/update the entered data into the table in your Zoho Reports account, select Access this Application option at the top right. When you access the application, the Sales Form you have created will be displayed as shown below.



Enter the required data into corresponding fields and click the required button to add or update the record in the corresponding table in Zoho Reports.

Checking Data in Zoho Reports table

Once the Form is successfully submitted, data will be added as a new row to your Sales table of StoreSales database when you click Add to Zoho Reports Database button,  as shown below.



Similarly, if a user clicks Update button, all the rows in the table of the Zoho Reports that matches the given criteria will be updated with the submitted data.

Sharing the Form and Embedding

By default, only application owner can access the Form in a private application. To enable your users to access the Form, you have to Share the Form to your users or embed it in your Web Site/Blogs and set required permissions.

Sharing Your Form:

You can share your views to your user by providing the user's email address under Share tab. Please refer to the Zoho Creator's help documents on Sharing for details on how to share a Form and set different level of permission for each user.

If the shared user holds an existing Zoho account with login id as the shared e-mail id, he can just Sign in to Zoho Creator to access the application. If the shared user does not own an existing Zoho account, he has to Sign Up with this e-mail id specified in the invitation e-mail.

After login the user can access the shared Form by clicking the Shared to me tab provided in his login home page and selecting the corresponding application. Alternatively the user can directly access the form by clicking on the link provided in the Invitation Mail that would have been sent by you while sharing the form.



Embedding Your Form:


You can also embed your Forms in your Web Pages, Web applications or blogs by including a few line of HTML code snippet into your Web page HTML content. On including this code snippet, whenever the Web page is loaded in a browser, the embedded Form will also get loaded.

HTML code snippet can be generated by invoking More Actions->Embed in your Website option from the Form header as shown below.




You can paste this HTML code within the relevant section in your Web page HTML content to see the embedded form. The following screenshot shows the Sales Form embedded into a  web page.



Please refer to the Zoho Creator's help document on Embedding, for details on how to embed a Form and the various settings that are possible.

 Related Topics:Zoho Reports Solutions
Previous: Zoho Reports for Google Apps

Mark Thurman 1351 - days ago 
Any plans for a zoho.reports.getRow(dbName,tbName,<criteria>) function?
Mark Thurman 1351 - days ago 
Okay just seen the answer here: https://forums.zoho.com/topic/zoho-reports-4-5-2011
Guest 1065 - days ago 
Hi i have a problem, i made a form that has information that when someone fills the form, i then edit and make update, tried to use <return value as map> = zoho.reports.updatedata(<Database Name>,<Table Name>, CreatingMap, <Criteria>); and put it on edit on submit, but when i edit a record and make update it it deletes the other and puts this one in every row that there is information, can someone help me?hx
leote.work@gmail.com+(Guest) 1065 - days ago 
Hi i have a problem, i made a form that has information that when someone fills the form, i then edit and make update, tried to use <return value as map> = zoho.reports.updatedata(<Database Name>,<Table Name>, CreatingMap, <Criteria>); and put it on edit on submit, but when i edit a record and make update it it deletes the other and puts this one in every row that there is information, can someone help me?thx
Fadibitz (Guest) 977 - days ago 
I don't see any information regarding how to bind drop-down list option values to the existing contents of a table. Any ideas?

I also do not see where any of the questions posted actually have responses. Am I looking in the wrong place?
prameena.s 973 - days ago 
Hi Fadibitz,

From your post we understood that you have to bind Zoho Creator drop-down list options with Zoho Reports table contents. Hence we infer that, the data ( or distinct data ) from Zoho Reports table is to be listed in your drop-down list. Please correct us if our assumption is wrong.

With this assumption, to pull the data from Zoho Reports database and show it in the form, you can use EXPORT API(https://zohoreportsapi.wiki.zoho.com/Export.html) to fill the form onload with the data from Zoho Reports database. Follow the steps as below,

1. Open your view in Zoho Creator and move to "Edit this application" view.

2. Click Script tab -> On Add -> On Load -> Free-flow Scripting.

3. Copy the below code in that script area. ( The following sample code fetches the distinct value from the column "Product" in the table "Sales Data" in Zoho Reports and populates it in the drop-down element "Product" in Zoho Creator form ).

############### Sample Code Starts ###############

// Gets the authentication for your account.
ticketDetails = getUrl(("https://accounts.zoho.com/login?servicename=ZohoReports&FROM_AGENT=true&LOGIN_ID=<user <br=""> Name>&PASSWORD= <password>"));
ticket = (ticketDetails.toString()).subString(((ticketDetails).indexOf("TICKET=") + 7),((ticketDetails).indexOf("RESULT") - 1));

// Sample query, which fetches the distinct values of "Product" column in "Sales Data" Table in Zoho Reports.
query = "select Distinct(Product) from Sales Data";

// Zoho Reports Export API URL which fetches data from the table "Sales Data" with the criteria (query)

url = "http://reports.zoho.com/api/<user name="">/<database name="">/? ZOHO_ACTION=EXPORT&ZOHO_OUTPUT_FORMAT=JSON&ZOHO_ERROR_FORMAT=XML&ZOHO_API_KEY=<apikey>&ticket=" + ticket + "&ZOHO_API_VERSION=1.0&ZOHO_SQLQUERY=" + query;

// Invoking Zoho Reports Export API and parsing the data.
response = getUrl(url);
result = response.executeXPath("/root/response/result/rows/text()");

// Clearing the values in drop-down element "Product" on loading in Zoho Creator form.
clear Product;

// Loading the fetched distinct values in the drop-down item "Product"
Product:ui.add(result.toList("-|-")); // Adds this new data list to your drop-down option.

############### Sample Code Ends ###############

4. Change the values mentioned in "<>" tag with your details and save the script and access the application. Now, whenever you open the form, the above script fetches the distinct data from Zoho Reports and loads them in your Zoho Creator drop-down list.

5. To generate APIKEY, go to https://zohoreportsapi.wiki.zoho.com/Prerequisites.html.

Kindly get back to us for any further support. For any support and clarifications, you can reach us at support [at] zohoreports [dot] com.


Thanks and regards
Prameena S
Zoho Reports</apikey><table name=""></table></database></user></password></user>
tarun.chaudhary 663 - days ago 
hello mam
i just put the above code with my account details but an error is coming,"error in executing set variable task and unable to update template variable result" can u help me with this..
with regards
tarun
tarun.chaudhary 663 - days ago 
i m waiting...............
janani.t 659 - days ago 
Hi,

We apologize for the delayed response.


The said message will be shown when either an invalid value is passed or a mandatory value is not passed. It will be helpful for us to find the exact reason and provide a solution for you, if you could share the Report in your application with admin rights to edit@zohocreator.com and send the URLs of the application and Reports to support@zohocreator.com.

To know how to share reports, please refer to the below link.

http://kbase.creator.zoho.com/share/edit-access-to-support


Sincerely,
Janani | Zoho Cares
Post a comment

Your Name or E-mail ID (mandatory)

Note: Your comment will be published after approval of the owner.





 RSS of this page