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.<\/p>\n
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 …<\/p>\n
Our first help doc from ZOHO<\/a> (yes I did look into this brieifly last week).<\/p>\n Step 1: Made a table in ZOHO Reports called TCTest. It has the following columns<\/p>\n Step 2: Using the link above, we see that their docs are not keeping up with their improvements<\/p>\n 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<\/a> 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<\/p>\n 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.<\/p>\n 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<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-552","post","type-post","status-publish","format-standard","hentry","category-general"],"yoast_head":"\n\n
\n
\r\n\r\ninvar = map();\r\n \/\/invar.put("ZOHO_CRITERIA", "%22ID1%22%3D2");\r\n mapVarFromRequest = postUrl("http:\/\/reportsapi.zoho.com\/api\/__username__\/__database__\/TCTest?ZOHO_ACTION=EXPORT&ZOHO_OUTPUT_FORMAT=HTML&ZOHO_ERROR_FORMAT=XML&AUTHTOKEN=__mytoken__&ZOHO_API_VERSION=1.0&ZOHO_CRITERIA=%22ID1%22%3D2", invar,false);\r\n input.Comment = mapVarFromRequest.toString();\r\n alert(mapVarFromRequest.toString());\r\n\r\n<\/pre>\n
\r\n\r\nmyURL = "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;\r\noutMapVar = getUrl(myURL,false);\r\ninput.Comment = outMapVar.get("responseText");\r\nstartPath = input.Comment.executeXPath("\/response\/result\/rows\/row\/node()");\r\n if (startPath != "")\r\n{\r\ninput.Name = input.Comment.executeXPath("\/response\/result\/rows\/row\/column[@name='Name']\/text()");\r\n input.Value = (input.Comment.executeXPath("\/response\/result\/rows\/row\/column[ @name = 'Value' ] \/text()")).toLong();\r\n }\r\n<\/pre>\nAdd\/Update Code<\/h2>\n
\r\n\r\nif (input.ID1 = 0)\r\n{\r\n \/\/add\r\n creatingMap = map();\r\n creatingMap.put("Name", input.Name);\r\n creatingMap.put("Value", input.Value.toString());\r\n mapResponse = zoho.reports.createRow(__database__, "TCTest", creatingMap);\r\n input.ID1 = (mapResponse.get("ID1")).toLong();\r\n openUrl("https:\/\/creator.zoho.com\/__loginname__\/tc-formtest\/#Form:TCTest1?ID1=" + input.ID1, "Same window");\r\n reload;\r\n\/\/alert(input.ID1 + "add");\r\n alert(mapResponse);\r\n}\r\nelse\r\n{\r\n \/\/update\r\n \/\/ alert ("Now write something to update, not add");\r\n alert(input.ID1 + "update");\r\n creatingMap = map();\r\n creatingMap.put("Name", input.Name);\r\n creatingMap.put("Value", input.Value.toString());\r\n creatingMap.put("EffectiveDate", input.dtDateTime.toString());\r\n mapResponse = zoho.reports.updatedata(__database__", "TCTest", creatingMap, "ID1 = " + input.ID1);\r\n}\r\n\r\n<\/pre>\nOther Items<\/h2>\n
Conclusion<\/h2>\n
Summary of Links:<\/h2>\n
\n