Thursday, September 15, 2011

Lesson: Intelligent look-up fields

Lookup fields are a great part of Zoho Creator. They ensure that common data is only entered once, avoiding spelling mistakes. Crucially a look-up also relates data from one form to data in another.

Often though you will want to have the contents of one look-up field be determined by the value of another field, even another look-up field. This is where those new to Zoho often get stuck. It is one of the commonest questions asked on the Zoho forums.

So lets have a look at a scenario. In my App called 'Blog App' I have a few forms and corresponding views. Departments, Employees and the important one, Job Detail.


The Departments form simply provides a way to add new dapartments and provide a description if you want. See Fig 1.

Fig. 1 - Department Form



Each department will also have Employees and so we have a simple Employee Form to create them and relate them to a Department. See Fig. 2

Fig. 2 - Employees Form
 The Employee Form uses a simple look-up field to pull the names of the various Departments.

Finally the 'Job Detail' form. In the example below the Employee field is a simple look-up field. See Fig. 3 and displays all the employees from the Employee form


Fig. 3 - Job Detail Form - Not efficient
What we need to do is add some Deluge script to the form so that only matching Employees are added when the Department is selected.

Deluge script can be run for a form or a field in several ways. 

For Forms:
  • When a form is loaded
  • When the user submits and form a validation script can be run 
  • When the data is validated and then correctly submitted to the database.
For Fields:
  • Fields can be scripted when the user enters data
  • And again when they edit and existing record.
In the example below this deluge script is run whenever a user changes the value of the field 'Dept'



    Fig. 4 - Deluge Script to Add matching Employees to Look-up

    Lets walk through what's happening here:


    1. The script checks to see how many entries there are in the Employee Form that have the same Department as that entered into the Dept field. e.g. [Dept = input.Dept]. In Deluge script input.xyz relates to a field in this form. The check is in the form of an IF statement. In this case we are saying that if the number of (count) of matches is '0' then do 'something', if its not '0' then do something 'ELSE'.
    2. If the answer to step 1 is that there are '0' matches then we clear the Employee field and display a warning message that includes the name of the Department selected by the user. This is the 'do something' bit of code
    3. ELSE: This is the 'do something ELSE' bit of code!
    4. For every Employee in the same department as that chosen: [Dept = input.Dept] we are going to give that a shortcut name and( a variable) of X. Then for every X we have we are going to add the Emplyee name from the Emplyee form to the drop down field.
    This has the effect of filtering the Employees listed in the field to just those in the same department as that selected in the field above. Filtering isn't really the correct term, we are actually  building the Employee list, but from the user perspective it behaves and looks like the wrong names were filtered out.



    Fig. 5 - Job Details Form - Correctly matching only Emplyees in Manufacturing Dept.


    This has been demonstrated in such a s way as the code is easily readable - like most deluge scripts and is a very simple thing yet took me a little while to understand. These little tweaks take a little time to implement for SIMPLE applications mean the data is kept cleaner and is easier and faster to enter. 

    As your applications get more complex there other ways to achieve this and similar methods.

    I hope this is helpful for any Zoho newbies. I intend to build on this post.

    Thanks
    Mark

    No comments:

    Post a Comment