Edit a private record

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.

ZC Quicktip #1: Learn any Form’s Email Address

Every form in Zoho Creator has its own email address but if you don’t know where to look, finding it can be a little tricky. This 40 second screencast shows you right where it is:

Send Email to a Zoho Creator Form

In case you can’t or don’t want to view the video, the steps are basically:

1. Open up your application
2. Open up the form whose email address you want to learn
3. Click on the More Actions menu
4. Click Import Data
5. Select the By Email tab
6. Copy the email address that appears next to the text, “3. Email the filled data to: “

Discussion

This only works for forms that you yourself create in your own account but once you have the address you can publish it somewhere or share it with other people who may want to use the form in this way.

If the form your working with belongs to an application you’ve designated as Private, only users with whom you’ve shared the application can send email to the form, and only if they send it from the same personal email address they use in Zoho Creator.

If someone without permission to use a private application tries to email one of its forms, they will get a response email explaining that they don’t have permission.

“Modular Databases”

A user in the Zoho Creator forum named scotto recently published an intriguing example of what he refers to as modular databases for Zoho Creator:

…an app that has all the countries of the world and their regions, states, or provinces…:
http://creator.zoho.com/scotto/countries-and-states/

I have also created a sample dropdown that lists the regions according to the country selected by the user…

I used ZC’s Copy Application feature to copy scotto’s application into my own account. For those who don’t know this feature is located in the upper right corner of the main ZC screen:

At first I thought I could simply use the version I copied in any of my own apps via the Lookup interface:

But as it turns out, things aren’t so simple. All the above feature does is allow your current app to import a single column of data from one of your other apps for use as a Dropdown. If I were to do this twice, one field for Country and one for Region, I wouldn’t have access to any of the associated columns that connect the two.

For example, if my Lookup 1 imported all the countries and my Lookup 2 all the regions, there still wouldn’t be any way for me to populate Lookup 2 with the right Region data when the user selected from Lookup 1, because I don’t have access to the associating data, e.g., United States –> Alabama.

So instead I copied scotto’s application into my own account, exported its Country and Region data into CSV files on my laptop:

Then uploaded the CSV files into my copy. I modified the copy to suit my needs, in this case adding First Name and Last Name fields, to create a User Profile application.

Though truly modular applications that you could either access directly as a ZC user or copy and use “out-of-the-box” in your own account would be better, this is at least one approach to speeding up your application creation — copy an application someone else (scotto, in this case) creates and then modify it to suit your own needs.

The only big drawback I noticed in my example was that my CSV downloads did not export in alphabetical order so that, for instance, the default region for the United States is Iowa in my application, whereas it was Alabama in scotto’s original. This could be fixed with my own data massaging prior to importing it, but I wonder if there’s some way to do it within ZC itself, after import.

Thanks for the tip, scotto!