By the (Auto) Numbers

Goal:

Create an “autonumber” field to display in a data entry form.

Ideas:

The example application will fill in a sequentially new 7-digit Case Number and 3-digit Sales Order ID each time the Data Collection form is loaded.

Blabber:

While Zoho Creator doesn’t currently have any built-in facility (that I know of) to safely, automatically generate custom unique identifiers for your forms each time they load, you can create a separate form to hold a single record to use for this purpose.

Here’s the way it works:

1. The autonumber form is created and then manually supplied with a single record by the administrator. This single record contains the starting value(s) for the autonumber(s).
2. The data entry form is created and configured to request the latest value from the autonumber form each time a new data entry form is loaded.
3. After the data entry form acquires the latest value, it adds 1 to it to create a new value.
4. The data entry form updates the single autonumber form record with the new value (latest value + 1).
5. The data entry form uses the new value as the unique identifier for the current record.

There is also some Deluge script in the on validate{} method for the Store Autonumber form that prevents more than one record from existing:


if (count(Store_AutoNumber[(auto_number_case != 0 && auto_number_sales != 0)]) > 0)
{
alert "The seed record already exists. If you wish to reset the starting values please either: " +
"
(1) Delete the existing record and recreate it with new values or ” +
“
(2) Update its existing values in the View using the Bulk Edit feature.”;
cancel submit;
}


Warning: I’m not sure how ZC handles locking so the method used in the example application may well introduce a race condition where two users may pull up your form at the same moment and end up with copies of the same “unique” id. That would be bad so I’m not sure how dependable this solution is for critical applications. It will probably work OK for non-critical applications. Maybe someone from the ZC development team can comment.

Also note that ZC already generates a unique identifier value for every record you save in any form and stores it in a column called ID that you can access in your applications and display in your views as needed.

Related Links:

using autonumber

ZC Weirdness (potential bugs in the system):

None!

ZC SkyServer

Goal:

Manipulate images through a Web service and save them.

Ideas:

The example application wraps around the SDSS’s Image Cutout service to provide a way to search for and save images throughout the known universe.

The user can pick a famous landmark as a starting point, add various tools and markers to the image, pan to other locations, zoom in and out, and save the image for later reference. Here are a couple quick examples:

Blabber:

This application is really just a wrapper around a mechanism for varying the GET requests to the skyserver and a facility for saving the images thus created. The SDSS provides a lot of its own interfaces and tools (worth checking out) so it’s best to think of this ZC application as a simplified user interface to basic images, plus a facility for saving those images.

In creating this application a few limitations in the current Zoho Creator interface came up that presented some challenges:

• page real estate: Lack of support for more than two columns in a form can require users with smaller screens or browser dimensions to scroll a lot.
• page format hacks: Limitations in mark-up tools required a few hacks to insert HTML via Deluge plaintext fields (”Add Notes” in the graphical editor). This may not even be supported in the future as the XSS vulnerabilities are fixed.
• form reloads and browser state: Reloading the form can sometimes lead to unexpected behavior. If you reload the ZC SkyServer page in your browser after picking some options, you may get an unexpected image or a form state that no longer accurately represents the image you’re viewing.

Once Zoho Creator supports its own API there will be room to create our own interfaces to utilize the services it provides. Until that time it takes some effort but you can usually still manage to present the form fields you need, even if the result is not the most elegant.

In case you would like to create your own version of this app, you can download the SDSS data loaded into the locations form as a comma-delimited file.

Related Links:

Sloan Digital Sky Survey

ZC Weirdness (potential bugs in the system):

There was initially an issue with Internet Explorer and the Decision Checkboxes used in the form but it was quickly resolved thanks to another example of great support from the ZC team.

Talk With Suganya J

I’m happy to announce that in the coming days LoZC will be conducting and publishing an interview with Suganya, Deluge Script’s Lead Architect.

Please forward your own questions for Suganya to me in the form of comments to this post or in an email and then next Tuesday I’ll send them along and run her responses here at LoZC, unedited, as soon as she gets back to me.

It’s a chance for ZC fans and users to ask anything not suited for the ZC forum: future plans for Deluge and the ZC site, how the team develops and supports the site, whatever. Get all your burning questions answered!

RSS Feed for ZC’s Public Applications

Here’s an RSS feed of Zoho Creator’s frequently-updated Public Applications page:

Zoho Creator Public Applications

Unfortunately the Feed43 FAQ says the information will update no more frequently than every 6 hours but in any case feel free to add it to your own aggregator. LoZC is now displaying the last 5 entries directly beneath its ZC forum section.

ZC …to the Future!

Goal:

Update a Zoho Creator application on an automatic schedule.

Ideas:

The example application borrows some categories from Flexo’s Income and Expense Report to create a ZC financial report. The unique feature of this application is that it uses a free account at LetterMeLater to periodically update the report with the monthly fixed amounts (so you don’t have to) while the form fields that are intended for monthly variable amounts are left visible so the user can manually enter them each month.

Since LetterMeLater has a feature called Recurring Emails, we’ll be able to configure it to update our fixed values for us on whichever day of the month they are expected to be credited to or debited from our account.

Blabber:

For reference, here are the lists of fixed and variable fields in the example application. All are provided to either receive an automatic or manual update so we can track our income and personal expenses.

We hide the fixed fields on the user form, since those are going to be automatically populated on a schedule using a LetterMeLater account:

The example application also keeps a running total of monthly income and expenses. It’s currently set up to store those calculations through December of 2009. If you copy this application for your own use, you’ll have to seed that form with the “0 records” for each month/year combo, using the method explained in Poor Man’s GROUP BY.

BEHAVIOR

This ZC application behaves as one would expect when it comes to supporting the manual fields (the ones that are visible in the forms). For those the user is expected to log in periodically and submit their financial data. But for the automated fields (the hidden ones), the user should register for a free LetterMeLater account and schedule the payments and credits in whatever way makes sense. That way, they don’t have to remember to enter them. Here’s what a LML screen might look like as the user configures it to their next 5 upcoming paychecks:

For the example application, I used LML to schedule 19 emails related to some fictional, monthly paycheck-related payments through November as well as 129 daily payments for internet access in the amount of $3.45/day.

Related Links:

Sendmail on deadline, LetterMeLater…

ZC Weirdness (potential bugs in the system):

None!

Undocumented Deluge

There are a few methods that Deluge understands but that don’t seem to be documented or prominently featured in the Zoho Creator forum. I’m not 100% sure where they surfaced; I’ve either run across them as a result of examining an error message in the ZC application interface or caught a glimpse of a specific example from some helpful developer on the ZC team.

There might be good reason for not documenting them, using them can be a little daunting and often times there are more straightforward, higher-level ways to accomplish a similar result. For the data type converters for instance, I think most get called implicitly through assignment (e.g., maybe my_date = ‘12-Jan-2007′ implicitly performs a toDate() operation) but sometimes an explicit method call can come in handy. Here are the ones I’ve stumbled across so far:

trim()

EXAMPLE


my_string = " I have unwanted padding on both sides of me. ";
my_string.trim();


RESULT

Removes whitespace padding on the left and right side of the string, i.e., ” foobar ” becomes “foobar”.

NOTES

You likely won’t need this one too often because Deluge seems to do a good job of sensibly trimming strings at the right time without you having to worry about it. Just know it’s there if you need it.

toDate()

EXAMPLE


input.my_date_string = "17-Aug-2006";
set my_date = my_date_string.toDate();


RESULT

Takes a STRING and converts it to a Deluge variable of type TIMESTAMP.

NOTES

I’m not sure when you’d need to use this given Deluge’s other date-handling options.

toLong()

EXAMPLE


employees_billing = 8;
hours_billed_this_month = 1320;
emp_worked = (hours_billed_this_month / employees_billing).toLong();
set msg = "Average time billed per employee this month: " + emp_worked + " hrs.";


RESULT

Converts variable value from type DECIMAL to type BIGINT so just the whole number is displayed. toLong() can also just be used to convert a STRING that contains numerical characters into a value suitable for calculation.

NOTES

This one is helpful when your results are in decimal format but you need an approximate whole number representation for subsequent calculations or display.

toString()

EXAMPLE


whole_number = (((mins.toString()).getPrefix(".")).toLong());
left_over = (((mins.toString()).getSuffix(".")).toLong());


RESULT

Stores only the left hand side of a DECIMAL value in one variable and the right hand side in another.

NOTES

Helpful when you need to isolate the right hand side of a decimal value. toString() probably has other uses, too.

toTime()

EXAMPLE


mse = mse_str.toLong();
time = mse.toTime();
stamp = time.toString();


RESULT

Converts a string that originally held the number of milliseconds since Jan 1, 1970 (epoch) to Deluge’s TIMESTAMP data type, then converts that to a STRING suitable for printing.

NOTES

You can use this one if you need to transport an exact date from one location to another as a string and later reconstitute to be of type TIMESTAMP. In the above example, mse_str would store a value representing the milliseconds since epoch as a string, e.g. “1185292219240″, and then that would be converted into a Deluge BIGINT (number) before being converted to a TIMESTAMP, then a STRING, e.g., “24-Jul-2007 02:50:19″, suitable for display.

plaintext

I should also mention than the field type plaintext gets short shrift in the documentation but is actually pretty powerful. Well-planned use of plaintext fields lets your ZC applications offer the user helpful hints and instructions and they can be updated when certain events happen to give the user context. In the application editor these are referred to as the ability to Add Notes

If I’ve missed anything here, please let me know and I’ll update the post.

Poor Man’s GROUP BY

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!

When Guests Arrive

Goal:

Log each time someone visits any of your Zoho Creator applications’ forms.

Ideas:

The example application can log user access for as many other ZC applications as are configured to talk to it.

Blabber:

One challenging aspect of using ZC for production application work is the lack of a global reporting facility for administrators. While it’s amazingly quick and easy to set up 3-4 applications to serve your users and then occasionally check the views to see when data is saved and by whom, there isn’t any single place to see who is simply visiting all your applications and viewing (loading) certain forms.

But there is a way to create one ZC application to receive and store the “hits” to all your other ZC forms, no matter which application they live in. The diagram below illustrates the basic concept:

USAGE

To create your own logging application you can select Copy Application or Save As Script in the example application and customize a copy for your own use. After you do that, take a look at the ___Log Entry   tab’s Log Entry   form to discover the email address for your specific logging application by clicking on More Actions - Import Data - By Email. You should see something like this near the bottom of the pop-up window:

That’s how your other ZC applications will talk to your logging application; make a note of the email address and don’t publish it to anyone else unless you want them to be able to send log entries to it as well. Also note that ___Log Entry is a hidden tab in the example application for aesthetic reasons. When you create your own version you’ll be able to access that tab and hide it later.

COMMUNICATION

Paste this Deluge script snippet into the on load{} routines of the forms you want to track, making sure to read the comments in the snippet so you can correctly replace default values to reflect the form you’re configuring and the email address for your own ZC logging application.

BEHAVIOR

The example exhibits 2 logging behaviors to be aware of:

1. If the user accessing an application form is that application’s administrator, no log entry will be sent.
2. If the user accessing an application’s form is an unregistered user or not logged in, the User field for that visit will contain “Public”.

DEMOS

For demonstration purposes, 4 copies of existing ZC sample applications were copied over to the landofzc home and edited so that each form’s on load {} section contains that snippet linked to above. They are: Project Tracker, CRM - mini, Help Desk - mini, and Recruitment. If you click around those applications a bit and wait a minute or so for the example logging application to catch up, you should be able to see the log entries corresponding to your form visits. Two ZC userlogins, “tzc” and “sharetactics”, have already been used as test accounts to generate some log entries.

PERMISSIONS

The example application is set as Public for demonstration purposes only; to restrict access to your own logging application, make it Private. Then if you would like other ZC administrators to be able to send their own Applications’ log events to your log, you can just Share the application with them and give them the email address.

SHORTCOMINGS

Unforutnately this technique won’t work for ZC views. It only works for forms. It would also be nice to provide some views that displayed aggregate data, perhaps as charts, but the current example application fails to provide any.

Related Links:

share data between apps

ZC Weirdness (potential bugs in the system):

None!

Break a Body to Pieces

Goal:

Send a plain text (no HTML) email containing a multi-line message body.

Ideas:

The only way that seems to work for breaking up plain text email bodies across multiple lines in Zoho Creator is to use a Deluge textarea (Multi Line Drag and Drop) field as the argument for the sendmail() Message: field. The example application demonstrates this technique.

Blabber:

The issues has been raised before and a variation of it will work for HTML-based email messages, but in general this can be a remarkably frustrating task without the textarea approach in your toolkit.

Attempting to inline the string directly into the Message: field will result in the escaped newlines (”\n”) appearing as part of the string while attempting to use a regular text field will only give you the string up to the point of the first “\n” that appears.

Related Links:

Mulitselect data in emails

ZC Weirdness (potential bugs in the system):

None!

The Strings of Time

Goal:

Marshal a string representing the current time in various formats.

Ideas:

The example application demonstrates 7 variations on creating a string representation of the current time, along with Deluge code examples:

1. PT(UTC -7/-8) Stamp: Pacific time zone timestamp
2. UTC Stamp: Coordinated Universal timestamp
3. PT: Pacific time zone time
4. UTC: Coordinated Universal time
5. PT Date: Pacific time zone date
6. UTC Date: Coordinated Universal date
7. Epoch: Seconds elapsed since Jan 1, 1970

You can paste the appropriate script fragment into your own ZC application to store or display the desired format.

Blabber:

Until Zoho Creator supports time zone configuration and controls, we either need to be content with date-formatted timestamp strings that are relative to the current Pacific time zone (PST or PDT, depending on the calendar date) or roll our own code in Deluge to represent other formats.

Remember that when you store the value of zoho.currenttime in formula fields within your application, ZC is actually storing a value to the millisecond level of precision. It’s only when you try and stringify the value or ask the ZC user interface or one of the export formats to display it that the result will be represented as a date in the current Pacific time.

By having the UTC option and the option to display time as well as date, you can create applications that store the precise value of zoho.currenttime in a formula field but keep another text, etc. field handy to store a value suitable for reading by humans.

STRINGS ATTACHED

Keep in mind that when storing a string formatted to one of the UTC formats, you can’t later come back to it, stuff it into a date field, and expect to reliably use any of Deluge’s built-in date functions or Date & Time Operators. As mentioned earlier those all currently assume you’re supplying and expecting values relative to the Pacific time zone.

Related Links:

Date Variables, Time Stamp On Orders.

ZC Weirdness (potential bugs in the system):

Here’s a quick rundown of unexpected issues encountered during this exercise:

VANISHING PARENTHESES

Currently there seems to be an issue with the way ZC’s graphical editor saves Deluge parentheses. With regard to the script used to generate the tutorial, after it was saved ZC seemed to try to optimize by removing allegedly unnecessary parens but when editing and saving changes to that optimized version ZC would display errors related to missing parens. I’ve published the script apart from the ZC installation with all the necessary parens intact in case you’d like a pristine copy.

VANISHING ENTITIES

If you try to upload a script containing HTML entities, the ZC uploader will translate them into the characters they represent. This can cause problems when including text like:

foobar = "This sentence has "quoted text" in it.";

But if you paste the script into ZC instead of uploading it, this doesn’t seem to be a problem. Not a big deal, really.

PLAINTEXT VARIABLE NAMES

It seems the ZC graphical interface does not currently let you choose the variable name for form fields of type plaintext. No matter what label name I tried to give them, they reset back to the default naming scheme of “plain”, “plain1″, “plain111″, etc. Again, not a big deal. If you’re a stickler for such things you can save the Deluge script as text, rename the fields, and recreate the application by pasting in a fresh copy.

photo © Darren Hester for openphoto.net CC:Attribution-NonCommercial