Archive for the ‘Data Processing’ Category
May 4, 2008
I don’t know if many of you will remember me, but I was invited to be a guest-blogger here on Land of Zoho Creator last fall. After one post, I managed to disappear off the face of the Earth for a while, but hopefully I’m back to regular blogging and will be able to post a bit more frequently.
A while back, Pete shared a great tip for generating a direct link to edit a specific record in a Zoho Creator. This was a great tip, as this is a feature sadly lacking in Zoho Creator by default. The only drawback with this is that it only works for records that are publicly accessible.
Unfortunately for people storing sensitive information, such as e-mail addresses, it is not usually a good idea to make the records public. In particular, I’m thinking about an application that stores member, subscriber, or client information. Since this information can’t be made public, there’s no way for the user to be able to update his or her own information when an address, e-mail address, or other information changes.
However, if you’re willing to do a little bit of Deluge scripting, there is a way to get around this progromatically. All you need is the ability to have one unique field for each record (such as an e-mail address), or a combination of fields (such as a username and phone number/address).
Once your main “add a record” form has been created, create another duplicate form to use for updating records. This new form should have all of the same fields (at least, the ones you want users to be able to edit). Although you could put this functionality directly into the main form, your users won’t be able to save the record, or you might end up with duplicate data.
You’ll want to make your identifier field(s) the first field that will be filled out in the “update” form, and you may want to add a “Note” field to explain to people how this will work.
Add a “On Add -> Validate” script to your application. You can do this from from the Script page. Click on the “Validate” link under “On Add.” Drag a “Fetch Records” code block into the code section of the page and click the “edit” button to customize the code.
Select the form that holds the data you will be checking against (the primary form that holds the original data).
Enter a variable name to use for the record you select (this can be anything you want).
Finally, specify the criteria you will be using to select the record that matches the information the user has entered into the field(s) in your form (The “Criteria field” tab contains the field names for the record you are checking, and the “Input field” tab contains the field names for the currently open form). Set the criteria to select records where the criteria field(s) matches the input field(s). This can get a little confusing, since the field names will be the same, so pay attention to what you are doing. Be sure that the field(s) you are checking cannot possibly contain any duplicate values, or you may find that people are updating records other than their own.
After you’ve fetched the record containing the unique value(s) that your user has entered, you can use a series of “Update record” code blocks to update the fetched record with the new information that the user has submitted.
To make things easier for your user, you may want to pre-load their information after they have entered the unique value(s) to identify themselves. To do this, add an “On User Input” code block to the field that contains the unique value (You may want to do this for each field that contains user information if you don’t have a single unique field for each user).
Drag a “For each record” code block into this section, and use the same criteria to specify the matching record. Inside this code block, use a series of “Set variable” code blocks to set the input fields to the values in the matching record.
This way, your user will be able to see the values that were previously entered. It has the added benefit of ensuring that any fields the user doesn’t change don’t get replaced with “empty” data.
That’s all there is to it. It does take a little bit of coding to work, but it can be well worth it if you need to allow users to change information in a recordset that they don’t otherwise have access to.
This kind of functionality can be useful for updating general information, but can be particularly useful for people who use Zoho Creator to handle registrations for classes or events. Users will be happy knowing that they can change their minds later and update their information whenever they need to.
Incidentally, this same type of functionality can be used with the “show/hide” field codes. If you want to make certain fields in your form only available to “members,” you can use the “For each record” code block to validate the information they enter into one field to determine which fields are available for editing.
I hope you find this tip useful.
December 13, 2007
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.
November 19, 2007
This is the third post in a series detailing a real-world example of using Zoho Creator as a small business software platform. If you’re interested in catching up, here are the previous posts:
False Assumptions
When we last left Ted, we said the next step was to publish a prototype that would let him enter a customer’s real estate listing and automatically post it to Google Base once it was ready. And a few days later in a tangentially related post about ZCrIS, I mentioned we’d be throwing ZCrIS into the mix of technologies for this case study. As it turns out both of those statements were lies, lies, lies.
We’re actually going to use Oodle instead of Google Base in our example because Oodle’s feed requirements are much simpler, allowing us keep our focus on Zoho Creator instead of Google Base’s API and relatively complex feed requirements. Furthermore we won’t need ZCrIS because as it turns out Oodle will actually come and get our XML feed from us (after we email them to tell them about it), instead of us having to submit it to them. It took a few iterations before this simple approach became apparent but our prototype in its current state is both fairly straightforward and entirely self-contained within a Zoho Creator application.
Meet Zoodle
The current prototype, nicknamed Zoodle, includes a couple unique features:
- A multi-part form: We break up one long record of real estate listing data into four smaller forms. Contrast this approach with the proto-prototype that implemented one big form.
- A custom XML feed: Oodle requires its users to submit a feed using its own custom format. We manage to export a feed that matches this format directly from Zoho Creator.
We provided a diagram depicting a high-level take on the BuggyRocket customer service process in an earlier post, but let’s look at a slightly modified version that shows how the Zoodle-to-Oodle interaction fits in:
Zoodle will let Ted enter the listing data he gets from a BuggyRocket customer as it becomes available and only publish it for Oodle to pick up once it’s complete. Other features include:
- Listing detail page: Although it’s not the slickest looking thing in the world, we do manage to provide a “detail page” served directly from Zoodle. We also provide an image URL in the same fashion.
- Private application with some public pages: Ted’s use of Zoodle requires his authentication but the detail page, image URL, and Oodle XML feed are all publicly available on the web.
- Gradual approach to form completion: Through the use of several forms that are attached to the main “Essentials” form, Ted can easily fill out the parts of a real estate listing that he currently has data for and come back to finish the rest at any time before publishing.
- Republishing: Data can be edited, overwritten, and republished at any time.
Given our current understanding of the business need, Zoodle is probably OK as a conversation starter for enabling BuggyRocket’s information system. But we can’t rest on our laurels, Zoodle is only intended as a prototype so Mark can check in with our efforts and see where we need to go from here. Look for Mark’s comments on this very subject in an upcoming post.
November 14, 2007
Technology Made Wicked is a new blog authored by Jynn, a software engineer in Hyderabad. The blog’s maiden post is a nice example on quickly integrating ZC RSS feeds with Google Maps via the Pipes feed processor:
Zoho creator is used to update movie info using the UI or even Bulk upload and Google maps is used to overlay the data.
I also used the cool technology of Yahoo Pipes to basically transform the Feed generated by Zoho Creator.
I’m looking forward to more from Jynn in the way of Zoho Creator mashups. Back in August LoZC posted a similarly brief experiment, sans Pipes and suffering from a somewhat glaring lack of decent street address validation.
September 6, 2007
Update: This approach won’t work because of a new bug (I believe introduced in the Sept 1 production update) in the way ZC sendmail handles multiline text fields. See the comments to this post for more information. Thanks to Suganya, this is now fixed!
Goal:
Programmatically add a new record to a Zoho Creator application.
Ideas:
The example application asks the user to select a fruit. After the user submits the form, the selected fruit is saved. In addition, after a few moments a new record is created in the Activity Log that shows which user saved a fruit and at what time they saved it.
Blabber:
Lately there have been several requests in the Zoho Creator forum that ask for the ability to programmatically add a new record. Although the feature is not currently supported in Deluge (the ZC team reports that it’s on the way), by using sendmail() you can still get it done provided your application is in no hurry to see that new record written to the corresponding form.
The example application pretty much demonstrates all you need to know. If you look at the Deluge source you can see that the first form is simply calling sendmail() in its on success {} block to write a new record to the second form using that second form’s More Actions >> Import Data >> By Email functionality. It’s a pretty mundane demonstration and is designed to write a new record to a form within the same application but in July I wrote about this technique in more detail and demonstrated how you can use it to effectively send information from one or more applications to a separate application.
The major drawback with this technique (other than it being a shameful hack) is that of course there is a sometimes not-so-brief delay between the time your application sends its emailed record to import and that record actually showing up in the database. If you can deal with the drawback though, other possibilities open up — for instance how about an application or applications that present the form interfaces in several different languages but save all that form data to a single view (along with a field indicating which language was used, for filtering purposes)? That way you could support multilingual applications that all write to the same place for easier data processing, management, etc.
Related Links:
Talking to Zoho Creator with Email
ZC Weirdness (potential bugs in the system):
None! Unfortunately it sometimes takes awhile for the emailed record to actually show up, but that’s just the nature of the beast.
August 18, 2007
Goal:
Encrypt and decrypt data in a Zoho Creator application.
Ideas:
We have two example applications this time:
- The first one demonstrates how you might encrypt, store, and decrypt U.S. Social Security and credit card numbers in a ZC application. It’s a barebones web page just to give you an idea of practical usage. You’ll only be able to successfully decrypt the records encrypted with your passphrase, the others will display as garbled text. I made a quick screencast of me putting it through its paces:
- The second example is about as simple as you can get in Zoho Creator, one form with one field, but on the whole this is more complex than the first example because the embedding web page is designed to help you encrypt and decrypt data for any Zoho Creator form field you care to designate. You need to be pretty familiar with the ZC GUI and configure several parameters to get it working; it’s mainly here to demonstrate the guts of what is going on and to help you build more domain-specific applications of the idea. The screencast shows me filling out the form and doing some encryption:
Blabber:
In both example applications, all encryption-related activity is taking place inside web pages that load a modified version of John Walker’s JavaScrypt tools. Take a look around that site if you’re interested in the implementation details.
No Web Server Necessary
You don’t actually need to access the example web pages on the LoZC web server, you can download them and run them from your own machine’s desktop. Walker says:
…to have any degree of security, it is essential that all processing be done on your computer, without involving any transmission or interaction with other sites on the Internet. A Web browser with JavaScript makes this possible, since the programs embedded in these pages run entirely on your own computer and do not transmit anything over the Internet.
So if you’re especially paranoid, you can download the web pages to your own machine, examine the source, load them from there, and be next to certain that no sensitive data is being transmitted during the encryption or decryption process.
The fact is that whether or not you take the extra-paranoid step of making local copies, all the encryption/decryption activity is happening in your own browser. The only data that Zoho Creator or the rest of the internet for that matter will ever see is the encrypted version of your data.
You can also feel confident in the quality of the encryption that is taking place. JavaScrypt uses AES 256-bit keys. AES provides “a cipher approved by NSA for encryption of TOP SECRET information”.
All that being said, JavaScript as a technology has its own inherent security issues so caveat usor.
Finally, local copies of these pages work great but if you’re using Internet Explorer it’s going to give you a security warning before the JavaScript will execute. Once you disable the warning or approve the page for local use, IE should work fine.
Clunkiness
There is no Zoho Creator API (yet) so getting our encrypted data into our ZC applications is clunky. Each time we want to save a record with some encrypted data, we’re locally creating encrypted data, embedding a ZC form in an HTML iframe that is preset to load that encrypted data, and finally asking the user to manually submit the ZC form. Yuck.
I suppose if you really wanted to hide the Zoho Creator form from your user, you might be able to do something with your own copy of the ZC HTML form, a hidden iframe, and polling one of the export format links to make sure the data made it to the ZC database, but that’s really too much of a hack. Even for me.
Once an API does become available there will likely be better ways to facilitate field-level encryption in ZC and in fact it’s not hard to imagine the Zoho Creator team someday providing “native” client-side encryption functionality in a future release of the ZC GUI. Until then, if you need support for encrypted fields you can use an approach like the one described in this post.
Data Bloat
Note that the encryption web page communicates with Zoho Creator forms by sending preset values through the query string. Given Internet Explorer’s 2048 character limit for GET requests and the inherent size increase that comes along with our hex-based encryption, as a general rule you probably shouldn’t try to encrypt any data that is longer than about 780 characters.
Private Applications & Private Links
If the ZC application you’re trying to access with the encryption pages is set to Private, make sure you’re actually logged in to ZC before you try to use them. Otherwise they won’t work because the links required to configure the encryption won’t be accessible to you.
Also note that Private Links, e.g.:
http://creator.zoho.com/username/form/1/dejgODOeBwhvEsE...
won’t work either. ZC does not seem to support sending in preset form field values in the query string with this kind of URL.
Related Links:
JavaScrypt and Gmail, Encrypt This! Firefox Extension
Here’s a ZIP file of all the HTML and JavaScript used in the example pages.
ZC Weirdness (potential bugs in the system):
Form presets sent through the query string don’t seem to work for Drop Down fields (i.e., when field cc_type was defined as a Drop Down, its value was not being set in the Account Information example application, even though the query string was trying to set it. So even though the external web page uses an HTML dropdown, I had to change the ZC application so that Credit Card Type was of type Single Line.
Also there is the Private Links issue mentioned above but possibly that one is by design.
July 23, 2007
Goal:
Create a view which shows the total occurrences of a value instead of listing each entry.
Ideas:
The example application asks the user to select a User ID along with a fruit selection entry for that User. Once the data is submitted, the Multiple Entries view below will display the individual entry while the Total Occurrences view shows the tally of entries for each User.
Blabber:
Although there’s currently no “GROUP BY” functionality available in Zoho Creator, if you know the possible values you want to group together in advance you can create a separate form to store that data. The trick is that you have to pre-populate that form with the “0 records” before publishing your application. Since ZC doesn’t have a way for form events to add records to secondary forms, saving an individual record in the Multiple Entries form can’t trigger adding a record to the Total Occurrences form; currently in ZC you are only allowed to update existing records.
So for the example application, 5 records were added to a Total Occurences form before it was made Public: 5 values for the User ID column (ID001..005) and 5 0s for the Entries column to hold the gradually growing number of entries.
If you save or copy the application you can see how each form was structured and how the on success {} event was used to update the total each time a new record was added.
Related Links:
Totalling Entries?
ZC Weirdness (potential bugs in the system):
None!
