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 |
//——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.
Hi,
I’m in the process of creating a ORDER and QUOTE forms. I have a list of my products and uploaded the same to my account in Zoho Creator. The problem I’m facing now is, I have 4 columns in my product list (mentioned below)
1. Category (with 3 category)
2. Product Code (46 Codes)
3. Product Name (46 Products)
4. Unit Price (46 price)
Now, when my customer select one from the drop down of “Category”, the “Product Code” drop down should show only the code associated with the selected category. Likewise when the “Product Code” is selected the “Product Name” & “Unit Price” should automatically populate in their row.
So how do we do this? Please help me!
Thanks,
Narthan
Hi Narthan
Thanks for dropping by,
what you request is entirely doable
(Assuming… that you know to use the script builder and make use of the aggregate records function element & Fetch Data element)
Step 1 : Form load >
//Declare list variable to hold distinct values for CATEGORY
// use aggregate records to collect distinct values from Field — Table as needed
// leave the criteria section blank at this point
// Add the distinct list’s values to the drop down list field in your form
STEP 2
Category >on Userinput
if input.category !=”-Select-”
{
//Declare list variable to hold distinct values for PRODUCT CODE
//use aggregate records and set to collect distinct values of Product code
//IMPORTANT.. use the criteria section and do the following
//— Category = input.category– (ie, set the Quote form’s category field to match the category value from the order form—-
//Add the distinct list’s values to the product-drop-down list in your form
STEP 3
Product code >on Userinput
if (input.product_Code!=”-Select-“) && (input.category !=”-Select-“)
{
// use fetch record to collect record data corresponding to the user-chosen category and product code
//Update field values accordingly
}
Hope this helps
Let me know if you need further assitance
Best Regards
DeepaGovind
Thanks for the distinct drop down list code. I used it and working fine. I have attended ZOHO workshop at Chennai during 2014. I think you have participated. I am from AP Govt Engg. DEPT.
Namaste NArasimha sir
Thanks for dropping by. Yes, It was great to meet and interact with you in person.
Trust all is well.
The above code is on the older version of Zcreator ( Zc has grown by leaps and bounds) but the logic remains the same. Now there are filters in lookup fileds that you can use to acheive the same result without using code.
Happy coding !!!