Land of Zoho Creator

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.

I haven’t actually used Iceberg yet but have been keeping an eye on it and staying apprised via their email list since ReadWriteWeb first published an overview last year. This week I noticed they announced a major launch and are now officially supporting all manner of free and paid user accounts.

So since my interest in Iceberg feeds into a more general interest in getting the various web platforms out there talking to one another, I asked Wayne Byrne over email for some details regarding interoperability and here are the points he shared:

1. You can export any view to excel
2. You can get any web app to send data into iceberg using web services
3. Iceberg can make calls out to any app that has an api (like google, amazon, basecamp etc)
4. Oh and since iceberg makes a regular sql server database people can integrate right into the database itself

Note that Iceberg has two “versions”, hosted and local, and I think point 4 above relates to the local Windows-only desktop version while the hosted version implements a flash-based interface for users.

With Icerberg’s ability to issue outbound requests to the web I can see some interesting possibilities for integration via the Zoho Creator API and perhaps even a possibility to use an off-line desktop application developed with Iceberg that could periodically synch with an online Zoho Creator application.

Once I get familiar with the flash interface and can actually develop something that works I’ll try to post a simple demo illustrating integration with ZC.

Recently, we introduced the idea of building a community for those interested in implementing applications with Zoho Creator. So as promised, we’ve built the Zoho Creator Depot site. We are planning to showcase applications in the Gallery and hold virtual meetings at least once a month. From now on, if you want to use Zoho Creator for your business and need some technical assistance or if you are skilled at developing Zoho Creator applications, consider joining the Zoho Creator Depot.

This community will function as a co-op for the benefit of all those involved and it will encourage collaboration and sharing of applications. If you see value in Zoho Creator as a platform for business applications, join us and help us move this forward, and by all means, send us your suggestions and comments.

April 26 Update

• Thanks to all of you who have registered to join the ZCD. We’ll get in touch with you shortly.

• We have received some positive feedback since introducing the idea, and based on some of your suggestions, we have decided to make the Zoho Creator Depot a part of this blog, rather than as an independent site. So here’s a link to the page we will use to register as well as request a service: Zoho Creator Depot Form

Today I saw the annoucement of Zoho Invoice , which prompted me to write this post.  As an owner of a small business, I am seriously considering Zoho as the productivity platform for my team. In my opinion, all of the Zoho apps are well designed, but before I start spending the time required to migrate my invoicing from Quickbooks Online, my contacts from Outlook, my Online Meetings from Gotomeeting, my conversations from Skype etc., I want to make sure I gain something from this effort.

Here’s what it would take for me to take the plunge.

1. Integrated data management. I need to be able to add  information once and use it in different contexts. Let’s take Contacts. Ideally, I would create a form in Zoho Creator (or copy an existing template) and that form would then be my template for adding Contacts for Customer Management (Zoho CRM), Invoicing (Zoho Invoice), Human Resources (Zoho People), etc.  In other words, I know what contact data is relevant to me and my team and I don’t have time to customize Contact forms in four different applications.
2. Document automation capabilities. I need a good way of packaging the data I have to share those with whom I interact.  Let’s take Service Agreements. Again, I would like to manage my data with Zoho Creator and have the ability to generate renewals as .pdf documents. Ideally, I would use Zoho Writer to create the document template, and assuming it had a (Mail) Merge feature, I could  generate these agreements automatically .
3. Data visualization tools.  I need an easy way to view my information as a calendar, a map, a timeline or a list, regardless, of its nature (sales, human resources, time management, etc.). Currently, I see that the Zoho apps mentioned above all have their own calendars, reports and graphs. Again, by having the data stored in Zoho Creator and tools for displaying the data, this too could be achieved.

I’m not talking about  Grand Unification Theory here. I am stressing the importance of having a unified repository of information that can be reused in different contexts and for multiple purposes and leveraging Zoho Creator as this repository seems to make sense.

Jon Udell recently  wrote an interesting blog “Where can I subscribe to a running-shoe-replacement service ? “ which as I see it amounts to a simple way of fostering Customer Retention.

This is a business opportunity. If you’re a runner, spending $100 every six (or even three) months is infinitely preferable to injury. You’d think that shoe sellers would make it easy to do that, but they don’t. I’d happily authorize regular replacements, but nobody’s ever offered me that option.

His idea is applicable not only to athletic shoes, but to many other products and/or services that you just don’t keep track of and would gladly address when your provider sent you a friendly reminder. I can think of most of the house work I don’t do myself like termite treatment, gutter cleaning or air conditioning maintenance.

Jon writes again in Missing the cluetrain emphasizing the technology shortcomings of small businesses, so we decided to develop a solution to his problem (which I’ve captured with the cool online comic Pixton tool here) with Zoho Creator and LetterMeLater. 

If you want to try it, here’s the link:  Running Shoe Reminder . Be sure to sign up in LetterMeLater. This app will use the email you have associated with your Zoho Id to my gmail account to relay the message to LetterMeLater, which then forwards the message:

to the email you’ve specified in the form. It is currently set up to notify you 1 minute after you’ve entered the data, but surely you can envision this working at different intervals.

 This is the type of application we want to showcase in the Zoho Creator Depot.

With this, my first post,  I want to invite our readers to comment and vote on an idea we’ve been kicking around here - that of starting the  Zoho Creator Depot (ZCD).   As we currently envision it, ZCD would be a good destination if you’ve made up your mind about Zoho Creator as your platform of choice to deploy an application and you’re searching for tools, materials and advise or development help .

The ZCD would include:

• A gallery of production-quality ZC components and applications.

• Documented Case Studies and Success Stories.

• A list of ZC developers  and consultants, with their credentials and areas of expertise.

• A knowledge base of different topics that apply to ZC.

• Frameworks for developing or deploying mash-ups with Zoho Creator

• A simple way to purchase previously developed components, applications or development services.

As a client of ZCD services,  you could  resell the application or component and recover part of your original investment. If you sell it multiple times  you could conceivably make some money too. And if you’re a developer,  ZCD would be a way to connect with your potential clients.

I’m just borrowing ideas from brick-and-mortar places I find useful, like Home Depot, the Weaver Street Market and Play it Again Sports. If there is enough intest in this we’ll go ahead and build some ZC forms, get a Zoho Wiki started and see where this takes us.

Those of you who visit this site directly might notice a new face in the sidebar. Although we have Lockworld to thank for some great posts about ZC and we were hoping to see more, Doug recently let it be known he was having difficulty finding time to post. Hence we invited a new face to LoZC and the face accepted — please welcome Gabriel Coch!

I’ll let Gabriel introduce himself through his own introductory post but I will point out that he’s one of those rare folk who combine technical acumen, business savvy, and a genuine desire to help people not only improve their daily lives through technology but also safeguard their communities.

In addition, Gabriel and I have been exploring over email and a couple phone conversations how we (we meaning us and YOU) can take ZC’s potential as a commercial business platform to “the next level”…though what that next level looks like is frankly unknown at this point. Part of Gabriel’s motivation for joining us, if I can presume to speak for him for just a moment, is to further this idea by harnessing a real ZC community.

I’m excited and hope you are too!

I can’t say that this code actually returns an accurate value but I was impressed that I could just copy it out of an app I wrote back in July (an app that served as the subject of this blog’s first post, incidentally) and slam it into one of the new ZC function constructs to see it “just work” by returning the string value as one would expect.

The idea is to take whatever zoho.currenttime() is reporting and express it as UTC but I suspect the ZC team has since added features native to the platform that obviate the need for it:


string calculations.CalculateUTCTime()
{
// see http://answers.google.com/answers/threadview?id=560486
grab_time=zoho.currenttime;
epoch_seconds=((grab_time.toLong() / 1000)).toLong();
days=(epoch_seconds / 86400);
days_int=(days.toString().getPrefix(".")).toLong();
day_fraction=(days - days_int);
secs_left_over=(day_fraction * 86400);
hrs=(secs_left_over / 3600);
hrs_int=(hrs.toString().getPrefix(".")).toLong();
hrs_fraction=(hrs - hrs_int);
hours=hrs_int;
secs_left_over=((hrs_fraction * 3600)).toLong();
mins=(secs_left_over / 60);
mins_int=(((mins)).toString().getPrefix(".")).toLong();
mins_fraction=(mins - mins_int);
minutes="";
if((((mins_int).toString()).length() < 2))
{
minutes="0" + (mins_int);
}
else
{
minutes=(mins_int).toString();
}
secs_left_over=((mins_fraction * 60)).toLong();
secs_int=secs_left_over.toLong();
seconds="";
if(((secs_int.toString()).length() < 2))
{
seconds="0" + secs_int;
}
else
{
seconds=secs_int.toString();
}
// see: http://www.jsifaq.com/SF/Tips/Tip.aspx?id=7323
a=(days_int + 2472632);
b=(4 * a + 3);
b=((b / 146097)).toLong();
c=(((0 - b) * 146097)).toLong();
c=((c / 4)).toLong();
c=(c + a);
d=(4 * c + 3);
d=((d / 1461)).toLong();
e=((0 - 1461) * d);
e=((e / 4)).toLong();
e=(e + c);
m=(5 * e + 2);
m=((m / 153)).toLong();
dd=(153 * m + 2);
dd=((dd / 5)).toLong();
day=(0 - dd + e + 1);
mm=(((0 - m) / 10)).toLong();
mm=(mm * 12);
month=(mm + m + 3);
year=((b * 100 + d - 4800 + m / 10)).toLong();
month_str="";
if((month == 1))
{
month_str="Jan";
}
else if((month == 2))
{
month_str="Feb";
}
else if((month == 3))
{
month_str="Mar";
}
else if((month == 4))
{
month_str="Apr";
}
else if((month == 5))
{
month_str="May";
}
else if((month == 6))
{
month_str="Jun";
}
else if((month == 7))
{
month_str="Jul";
}
else if((month == 8))
{
month_str="Aug";
}
else if((month == 9))
{
month_str="Sep";
}
else if((month == 10))
{
month_str="Oct";
}
else if((month == 11))
{
month_str="Nov";
}
else if((month == 12))
{
month_str="Dec";
}
ts=((day + "-" + month_str + "-" + year + " " + hours + ":" + minutes) + ":") + seconds;
return ts;
}


A recent post from Gabriel Coch at the InfoPatterns blog steps through the use of Zoho Creator in conjunction with Toucan Navigate to show “the concentration of Fast Food Places along the I-95 corridor“:

My main objective was to accomplish this without a single line of code (no html, no javascript, no nothing) and yet provide enough functionality to answer some basic questions, hence making this potentially useful in the business world.

We’re starting up a list of folks who want to advertise their Zoho Creator skills to potential clients. Just contact me and I’ll add your info to the the page.