What is Zoho Creator | The Basics| Design & Share Custom Forms |Using prevalidated Form Fields | Statless Forms |
Now that you are exploring zoho creator yourself, you might have grasped more than what you bargained for. During this learning curve you may come across certain use-case scenarios & would like to see them work. Here is a working copy of one such request that visited my inbox.
UPDATE: Oct 2011
After the introduction of Aggregate Records, by ZC displaying unique list got even simpler.
Using the same example as below, lets display distinct list of countries using this option.
- Use a Dropdown field element in your form and name it – Distinct Country list
- In the Script section > Form > On Add > On Load
- Under Data Access> Aggregate Records > Drag and drop into the code builder
- Drag and drop “Add item” element into the code builder
- Select the drop down list element “Distinct Country list” — From Step 1
- Select the Countrylist-variable — from Step 4 (Aggregate record screenshot)
- Save script, and check in live mode if the distinct list is displayed in your form
The Assignment :-
- Registration form has Country & State fields.
- Both have to be pre-populated drop down fields ( ie… zero manual entry. User has to pick from list.
- >Based on the Country name, (Pre-populated list), the State Names ( of respective country) should be displayed in the second drop down.
Click here to View the Working demo ( Tab Name – LIST ACTIONS)
Forms & views Used –
Counry_states Form ( & view) – Single line field
Registration Form ( & view) – Single Line & Drop down
| Registration Form | Country – Sates (add new country & state list here) |
 |
 |
Use Single line fields and Drop down fields to design the forms. Leave the default “Option1, Option2, Option3″ in the drop down fields. We will be dealing with them shortly at Deluge script.
Donot use pick list/ lookup field
Question:-What happens when you use a look-up field?
Answer:- It will show the country names as “n” occurances. Eg:- India, India, India, India, USA ,USA, USA, USA.
We don’t want that.. all we want the list to show is – “India”& “USA”
In other words, we want to avoid any duplicate entries in the drop down list.
Form:- Country_States
This form is used to “collect” the country name & state name. So there is absolutely no scripting involved. Do take a look at the Country States View in the DEMO link
Form:- Registration Form
You can have as many fields as you want. Since this is a tutorial post, I am limiting the fields to Name, Country & State
In the demo, you will notice that, “Country” - does not have duplicates & “State” list changes dynamically based on the country name.
The Deluge Script involved.
//——–THIS POPULATES THE COUNTRY LIST WITH UNIQUE VALUES——–
Country_list = List();
Cntry = Country_states [(Country is not null)];
Country_list.addall(Cntry.Country.getall());
Country_unique = List();
for each country in Country_list
{
if (!Country_unique.contains(country))
{
Country_unique.add(country);
}
}
for each Ctry in Country_unique
{
County:ui.add(Ctry); //USE CLIENT FUNCTION — ADD ITEM
}
| CODE | YOU CAN FIND THIS AT | THIS LINE OF CODE MEANS |
| Country_list = List(); | LIST MANIPULATION (LM) – CREATE LIST | |
| Cntry = Country_states [(Country is not null)]; | DATA ACCESS – FETCH RECORDS | collect the rows where state is not null in Country_States_view |
| Country_list.addall(Cntry.Country.getall()); | LM- ADD ALL |  |
| Country_list=List(); | LM – CREATE LIST | |
| for each country in Country_list | LM – FOR EACH ELEMENT
<expression> = country |
|
| if (!Country_unique.contains(country)) | IF CONDITION | You can otherwise Type —> country_unique.contains ( country) is false
Checks if the <expression> value is present ( true) or not ( false) |
| Country_unique.add(country); | LM – ADD ITEM | If false, then the value is added to the list — now, you have a unique list |
| for each Ctry in Country_unique | LM – FOR EACH ELEMENT
<expression> = ctry |
|
| County:ui.add(Ctry); | CLIENT FUNCTION – ADD ITEM | Now, each list item is added to your drop down box |
On Registration form – > Country Drop down – > User Input
//——THIS POPULATES THE ~~STATES~~ OF THE SELECTED COUNTRY———–
States_list = List();
States_rec = Country_states [Country == input.County];
States_list.addall(States_rec.State.getall());
for each State in States_list
{
State:ui.add(input.State);
}
| CODE | YOU CAN FIND THIS AT | THIS LINE OF CODE MEANS |
| States_list = List(); | LM – CREATE LIST | |
| States_rec = Country_states [Country == input.County]; | DATA ACCESS – FETCH RECORDS | |
| States_list.addall(States_rec.State.getall()); | LM – ADD ALL | Exactly similar to Above pic – Enlarge and see. Be sure to get the state names & check the box for return field values as list |
| for each State in States_list | LM – FOR EACH ELEMENT
<expression> = State |
|
| State:ui.add(input.State); | CLIENT FUNCTION – ADD ITEM | Select the state-drop down list — You will see this list getting popupated based on country name |
UPDATE: Oct 2011
After the introduction of Aggregate Records, by ZC displaying unique list got even simpler.
If you found this post to be of some use, please spare a moment to drop a comment.
Your appreciation is my motivation
What is Zoho Creator | The Basics| Design & Share Custom Forms |Using prevalidated Form Fields | Statless Forms |
Hello i just need to know some information regarding the older posts just couple of months back on your website (workinginsaudiarabia.blogspot.com) there was a calender through which anyone can go back and see the older posts from when the blog started from 2007 plz give me information if i want to go back and review the older posts from 2007 (blog started) where should i go.
I'm a newbie of zoho. I practiced your tutorial on "Zoho Creator Tutorial – Unique list values, dynamic pick lists & dependent list values" but have different (wrong) result. Though (for me) I followed the instructions faithfully.
Hi Valsky,
Thanks for dropping by.
Could you plese share, what you were trying?
May be if i can take a closer look, I might be able to help
Best Regards.
Hi, I try to do this with my forms, but can‘t..
I have a Esp_SubEsp form with the following fields:
- Clave
- Especialidad
- SubEspecialidad
And have a Registration Form with the fields
- Especialidad
- SubEspecialidad
I want that ‘SubEspecialidad‘ field changes depending the ‘Especialidad‘ selected
Follow the instructions but can‘t make it work… could you please help me?
Thanks!
Eloisa
Eloisa,
I guess the Esp_SubEsp form holds the actual list of data for – Especialidad & SubEspecialidad
And at the Registration form’s >Add > Onload
you must include the code to add the Especialidad values (… Country code.. in the above example)
Next, On Registration form – > Especialidad Drop down – > User Input
include the code for specific SubEspecialidad values (… states .. in the above example)
Hope this helps.
Let me know if u need further assistance.
Hi Deepa,
Yes, it makes so much more sense to be able to compress duplicates from one table into single selections in another table. My version isn’t working yet, but it will!
I just want to check something with you from your script above:
There are places like line 3 “Country_list.addall(Cntry.Country.getall()); ”
Where the capital letter “C” is used, like “Country”
Then later the word country is written with a small “c” as in line 5:
“for each country in Country_list”
I think that is where I’m falling off using your method in my different tables.
Hi Dylan
Hope all is well.
to clarify:
The “C” in Country_list is a list-variable
whereas “c” in for each country–> is the individual list item from the Country_list
This workaround was used when ZC had not yet introduced the “aggregarte records” option. Now, displaying unique list even got simpler
I have updated the post, explaining how to use the aggregate record function.
Hope this helps
Thank you so much for this tutorial – especially for the update for using aggregate values! This has saved me lots of time figuring out dependent lists!
Deepa,
Well done on this. The Oct 2011 update pointed me at just what I needed — after much flailing around trying to get a conditional-based list on a multi-select checkbox field.
You should get that update prominently at the top of this post — you do a better job of having a page that addresses the fundamental problem I had than Zoho.
All the best,
John Richter
Hi John, Amin
Thanks for the appreciation
I have moved the update to the top of the list
Freel free to let me know if u want me to address a problem that you are facing with ZC scripting. Will be glad to post the tutorial
Many thanks for the offer for help — make take you up on that at some point for sure. Our App is coming together, but I’m sure we’ll encounter some difficulties as we put it to use.
For anyone picking this up, the reference to ‘example above’ is now ‘example below’ (in the original deluge script variant).
Hi John,
No worries
Glad that all is good.