Land of Zoho Creator

A post over at the Google Docs blog describes a new form creation feature, Stop sharing spreadsheets, start collecting information:

…in some cases, you want to collect just a tiny bit of information from dozens, scores, or even hundreds of users or more. Some of them are Google Docs users. Some of them are not. Either way, you don’t always want them all mucking around with the whole spreadsheet and you’re tired of telling those new to Google Docs that they don’t need to switch to Gmail just to use Docs (huh? you didn’t know that either?).

We’re really excited to bring you forms! Create a form in a Google Docs spreadsheet and send it out to anyone with an email address. They won’t need to sign in, and they can respond directly from the email message or from an automatically generated web page. Creating the form is easy: start with a spreadsheet to get the form, or start by creating the form and you’ll get the spreadsheet automatically.

Reading the entire post left me thinking of blist, DabbleDB, EditGrid, Smartsheet, Wufoo, and Zoho Creator. It must be at least a little disappointing for the smaller outfits in that list to see Google Docs generating similar technology in-house. Then again maybe this fact is just “validation of the model” as some like to say. Since Zoho Creator is really a project inside the much larger AdventNet I’m not sure its team would care much and in any case the unique thing that Zoho Creator brings to the table is that it has a GUI form builder but also an accompanying scripting language. Sites like EditGrid and Wufoo have great user interfaces, AppJet is doing great things with scripting on the web, but the hybrid approach might be entirely unique to Zoho Creator.

After many desperate, impassioned complaints, I see on the ZC blog that there is now SSL Support in Zoho Creator.

Yep, more proof of Zoho’s remarkable commitment to and support of the customers.

I could go into a tortured explanation for what knocked us off-line for several days but why waste time telling sad stories? The important thing is that we’re back. Also, I updated the re<form>ation Wufoo wrapper for Zoho Creator and hope to add support for the Dabble DB format soon. Here are some improvements over what I last described:

• New option to generate and copy/paste static source code for any form configuration
• New option to choose the calendar theme
• New option to specify success message upon successful form save
• Simplified configuration form
• Fixed most IE CSS problems
• Added dynamic generation of title element text based on the Zoho form label

Here are a couple new examples.

Apologies for comments at LoZC being buggered up the past few days. The admin email alias hosted at GoDaddy was temporarily failing, preventing new comments from being brought to the admin’s attention, sending them to a black hole of sorts. I think it’s fixed now and the recent comments are now live on the site.

OK our wufoo-ifier is in a little better shape now. Improvements include:

• Theme preview — updates the Wufoo theme each time you select a new one from the dropdown
• Logo switcher — offers several icons to replace the “Wufoo” logo in the upper left hand corner.
• Multiple calendars — supports many date or date/time pop-up calendars on the same form
• Improved style for required fields — asterisks denoting Required fields now better match Wufoo’s style (still broken in Explorer)
• Better support for IE, Safari, and Opera

Just as an aside, I needed a way to dynamically generate a callback to Calendar.setup in a way that most browsers would support and AppJet came to the rescue in about 10 lines of code:

/* appjet:version 0.1 */
/* apject:server */
var qs = request.params;
response.setHeader('Content-type', 'text/javascript');
page.setMode("plain");
print(raw(
"""Calendar.setup({inputField : """ +
"\"" + qs.inputId + "\"" +
""", ifFormat : """ +
"\"" + qs.formatString + "\"" +
""", showsTime : true, button : """ +
"\"" + qs.buttonId + "\"" +
""", singleClick : true, step : 1});"""
));

Generating a callback with client-side JavaScript can go something like this:

//...
var queryString = '?inputId=' + escape(inputId) +
'&formatString=' + escape(formatString) +
'&buttonId=' + escape(buttonId) +
'&noCacheIE=' + escape(noCacheIE);
calScript.id = 'calendar-setup-' + jsonIds++;
calScript.src = 'http://calset.appjet.net/' + queryString;
calScript.type = 'text/javascript';
calScript.charset = 'utf-8';
// etc., etc..

No fuss, no muss. More on the woofuifier later.

ZC2WF can render most Zoho Creator forms using any of Wufoo’s 50 CSS themes. Here are a few examples using some forms from Zoho Creator’s Sample Applications:

• post profile Modern Office theme, redirects to itself
• New Recipient Firenze theme, redirects to itself
• Add Donations Pistachio theme, redirects to itself
• Add New Donor Garden Pool theme, redirects to a Red Cross page

I say “most” forms because the service uses a modified version of Zoho Creator’s JavaScript Client API which means it won’t render any File Upload fields (since the Client API itself does not support these) and any client-side Deluge scripting (including the hiding and showing of fields) set up within the “native” ZC form won’t be available in this skinned version of the form.

Another current limitation is that although Wufoo themes have great support for instructions per-field to help the user, Zoho Creator doesn’t include its instructions for the user in the JSON export of its form definitions. If the ZC team can add this info to the export, I’ll happily add it to the service.

Here’s a zip archive of the relevant files, they can be hosted anywhere without too much hassle. Feel free to experiment with your own forms and I’ll post here as more features become available.

Update: I notice the calendar pop-ups aren’t working in Safari and Opera. I’ll fix that soon…

Awhile back someone in the Zoho Creator forum asked how to best design a ZC application and I wrote back to the effect that since there isn’t much design involved, it’s best just to suffer through the documentation and play with the interface until you get something close to what you thought you wanted. I’ll now amend that sentiment slightly by stating that it can’t hurt at least to try and write Deluge code that does not induce nausea.

Version 0.01 of Zyndafeed used a kludgy series of shows and hides in conjunction with 50 statically defined form fields to support up to 50 photo uploads. This worked OK from the outside but the implementation is embarrassing and in any case prompting the user with the option to fill out up to 50 text fields on one page is probably ridiculous in the first place.

Version 0.02 takes a different approach (and reduces signficantly the size of our Deluge script) by providing a separate form devoted to image uploads. A one-listing-to-many-photos relationship is established by inserting a listing’s unique_id as a non-unique identifier for rows in the Photos table. When it comes time to generate the feed, any image in the Photos table with a listing id that matches a listing’s unique id gets appended as a a child node. With this change not only are we providing Ted and the BuggyRocket customers with more flexibility (remember that we need to support at least 25 uploads per listing) but we’re also adhering closer to the DRY principle.

Design considerations when hacking out a Zoho application? It can’t hurt. In any case we’ll see what Mark thinks of this new version when he gets a chance to try it out and reply. Some ideas for more features include a monolithic view of listing data instead of views spread over 5 separate screens and some useful filters on whatever views are in place.

The AppJet homepage says type some code into a box, and we’ll host your app on our servers which pretty much sums up what they allow you to do.

This app reads JSON from this Zoho Creator app and converts the values to call the Google Chart API.

Here’s the source.

I got an email from Mark this morning saying he was in his first ice storm and that there’s one branch “above the power line that I’m concerned could fall and leave us offline for awhile” so who knows when we might hear from him. Best of luck, Mark!

This incomplete version of Zyndafeed has some interesting characteristics, most of which aren’t particularly flattering, but at least we can fix the first two:

1. Photos in the wrong place (fix in next version)

Currently the form labeled New asks Ted how many photos he needs to upload (with a maximum of 50) and then dutifully renders only that number of upload fields for him to complete. Now that Mark has clarified the requirements to support a range beyond 25 photos, leaving them here is pretty ridiculous. In the next version we’ll break them out into their own form. We’ll also fix the 50 photo fields that are currently hindering attempts to edit the existing records in the Main view.

2. Photos published in XML feed (fix in next version)

We still need to render a new XML element for each photo before we have compliant custom XML feed. After we get #1 squared away this shouldn’t be a big deal.

3. Non-intuitive form behavior (won’t fix)

Even after we do make photos a separate part of the application, Ted may still suffer at the hands of some non-intuitive (albeit innocuous) application behavior — if he fills out a few photo fields at the bottom then later hides those fields, then submit the form, any data in those hidden image upload fields would still be sent.

4. Unfortunate code (won’t fix)

ZC’s simple back-end is sometimes a limitation. We’ve managed to commit more than a few unspeakable crimes against all that is holy in the land of law-abiding script writers:

1. The lack of dynamic field definition means that our only way of supporting up to 50 image upload fields is to statically define each field up front then show or hide the required number of fields as appropriate.
2. The lack of one-to-many relationships marches us down the ill-advised path of generating our own unique-ids.
3. Both the inability to add a new record from a script and the inability to update existing records (without also creating a new row) make our table “design” and the scripting required to maintain it unwieldy to the point where we hope we don’t have to maintain it too much. Oh well, at least part of any necessary maintenance can be done via the GUI and if it gets much more complicated we actually will check the source into Subversion.

I’ll update again when the next version is published; separate photo functionality and a complete XML feed should present something Ted could conceivably use to cover the listing entry duties of his job.

We’ll also take a look at how Zoho CRM might hook into the process Mark needs to enable.