Using ZOHO Creator as a front end to ZOHO Reports

Well, it seems that ZOHO Creator has come a ways since last winter. I abandoned using ZOHO Creator which REALLY will someday give Filemaker a run for its money. It is BEYOND incredible what ZOHO has done with their database tool REPORTS which blows MS Access out of the water – and it is web based natively. I am also beyond impressed with their Spreadsheet tool (which has VBA in it – and it works!) and their inter-operability with their own platforms and other people’s platforms.

BUT – it seems that I should re-try ZOHO Creator as a front end to their reports database tables. Lets give it a go – follow along – it is Dec 13th 2013 so this blog article might take a week or so to settle down. Here goes …

Our first help doc from ZOHO (yes I did look into this brieifly last week).

Step 1: Made a table in ZOHO Reports called TCTest. It has the following columns

ID1 (AutoNumber)
Name
Value
Comment

Step 2: Using the link above, we see that their docs are not keeping up with their improvements

I made a form but had to make a new one to get the “Data will be stored in Zoho Createor to deselect it. There is a caveat in the instructions in a yellow section telling you have to do this as well but comes after you have already begun following the instructions
Well, I only got to the “add step” and … it worked. The instructions and the new format are quite easy to navigate. Next is an update step

Now, for bringing your record INTO an editable record. What a PIA – 4 hours of research (see Summary of Links 1-7 and MUCH searching. At least TED Talks was keeping me company while I got this far). All I have this far is to bring up an alert with a map of the results


invar = map();
 //invar.put(&quot;ZOHO_CRITERIA&quot;, &quot;%22ID1%22%3D2&quot;);
 mapVarFromRequest = postUrl(&quot;http://reportsapi.zoho.com/api/__username__/__database__/TCTest?ZOHO_ACTION=EXPORT&amp;ZOHO_OUTPUT_FORMAT=HTML&amp;ZOHO_ERROR_FORMAT=XML&amp;AUTHTOKEN=__mytoken__&amp;ZOHO_API_VERSION=1.0&amp;ZOHO_CRITERIA=%22ID1%22%3D2&quot;, invar,false);
 input.Comment = mapVarFromRequest.toString();
 alert(mapVarFromRequest.toString());

So this gives back a response with HTML but with one row – ID=2. Pasting it here was REALLY long- it had css and everything in it.

OK- so change the ZOHO_OUTPUT_FORMAT=XML, then we populate Comment (for now so we can see it) with the XML. So we need a line that loads XML and parses it to the correct fields


myURL = "http://reportsapi.zoho.com/api/__email__/__database__/TCTest?ZOHO_ACTION=EXPORT&ZOHO_OUTPUT_FORMAT=XML&ZOHO_ERROR_FORMAT=XML&AUTHTOKEN=__token__&ZOHO_API_VERSION=1.0&ZOHO_CRITERIA=%22ID1%22%3D" + idkey;
outMapVar = getUrl(myURL,false);
input.Comment = outMapVar.get("responseText");
startPath = input.Comment.executeXPath("/response/result/rows/row/node()");
 if (startPath != "")
{
input.Name = input.Comment.executeXPath("/response/result/rows/row/column[@name='Name']/text()");
 input.Value = (input.Comment.executeXPath("/response/result/rows/row/column[ @name = 'Value' ] /text()")).toLong();
 }

Add/Update Code

It took hours of fiddling to come up with the code behind an update/add button. It works that if the ID # is 0 – we add a record, else we update. For the add – another url has to be opened since you cannot update fields on the fly even if you reload. The reload in the code below does nothing but the line below – DOES contain the updated value in ID1 even though the screen does not show it. If you don’t go to the new URL new records keep getting added – SO THIS CODE IS FLAWED WITHOUT the OpenURL step.


if (input.ID1 = 0)
{
 //add
 creatingMap = map();
 creatingMap.put("Name", input.Name);
 creatingMap.put("Value", input.Value.toString());
 mapResponse = zoho.reports.createRow(__database__, "TCTest", creatingMap);
 input.ID1 = (mapResponse.get("ID1")).toLong();
 openUrl("https://creator.zoho.com/__loginname__/tc-formtest/#Form:TCTest1?ID1=" + input.ID1, "Same window");
 reload;
//alert(input.ID1 + "add");
 alert(mapResponse);
}
else
{
 //update
 // alert ("Now write something to update, not add");
 alert(input.ID1 + "update");
 creatingMap = map();
 creatingMap.put("Name", input.Name);
 creatingMap.put("Value", input.Value.toString());
 creatingMap.put("EffectiveDate", input.dtDateTime.toString());
 mapResponse = zoho.reports.updatedata(__database__", "TCTest", creatingMap, "ID1 = " + input.ID1);
}

Other Items

So another thing is that you cannot use AutoNumber fields in a formula – so that means that a URL string that goes to the edit screen – THIS FORM cannot be built – because we need the AutoNumber field – the ID1 field. So that means that I need a separate piece of code to make a list of the Report Database since I cannot make a view and share it. Maybe we can make a Query Report – I have not yet tried.

Conclusion

Like before, I don’t think things are ready. The integration of Creator and Reports needs to be closer with less code required. Having Report tables as a fundamental underlying table should be (and I am sure one day will be) more native without making maps to link one Creator field with a Reports table field.

Having XPATH is quite frustrating even for us geeks – yet it is a fundamental part of the code above – this should not be for a normal Reports user. Geeks are great to have in your back pocket but this necessitates one as a core team member. It keeps me employed a bit longer though which is a good thing.

I will be making a report on another server either in ASPX or in PHP (my customer’s decision – based on a some questions I have to ask them). So why shouldn’t i just do the whole thing – list and edit screens all in one. ASPX has a some great routines that I have already made for another customer and it worked fine. I will update this when that part is done.

Using ZOHO Creator as a front end to ZOHO Reports