Filtering Data in Views

You can create views with complex criteria and filters using AND/OR operators or a combination of both using Deluge Script. Some examples are given below:

  1. How do I Filter views based on date criteria?
  2. How do I Filter views based on complex criteria?
  3. Is it possible to display only those records that belong to a user (i.e. added by the user)?
  4. How can I create a view in Zoho Creator which shows only the current record?
  5. Is it possble to create a view using the getWeekOfYear() function to display records belonging to that specific week?
  6. How do I add a custom filter to my view, using scripting
  7. How do I set a custom filter to search for records where a certain field is not empty. For example, "Field does not contain [blank]"

1. How do I Filter Views based on Date Criteria

a. Show records that expires in the next seven days

You can use the following criteria in your view to fetch records ending in the next 7 days, based on the Expiry date in the form. In the sample code given below, Expiry is the name of the date field in the Sample form which stores the date on which a record expires. To add the criteria to the view definition, select the required view from the Script tab.

list  "Sample View"
{
    show  all  rows  from  Sample [(Expiry <= (zoho.currentdate  +  '1W:0D:0H:0M:0S') 
                                    && Expiry >= zoho.currentdate)]
    (
        Expiry
    )
    
}

b. Show records started in the last two weeks

Assume you have a form named Sample with a date field that captures the StartDate of a project. To display the records started before 2 weeks, you can add the following criteria to the view in script mode:

list  "Sample View"
{
    show all rows from Sample [StartDate > (zoho.currentdate - '2W')]
    (
        StartDate
        .....
    )
    
}

In the above statement, zoho.currentdate returns the current date and 'W' stands for week. Refer the topic Operators in Deluge Scripting , for more information on the date & time operators.

c. Show records started on the current date

Assume you have a form named Sample with field StartDate. If you want to view all the records whose StartDate is the current date, you can create a filter using the zoho.currentdate function, as shown below:

list  "Sample View"
{
    show all rows from Sample [StartDate == zoho.currentdate]
    (
        StartDate
        .....
    )
    
}

d. Show records related to the current week

Assume you have a Date field in your Form called Date_1 which stores the date when the Employees enter their TimeSheet. Add another field to your Form say DateInfo to store the week and the year of the date. This is achieved by writing On User Input script to the Date_1 field, as given below.

To update the DateInfo field with the week and year of the Date_1 field, write 

//to get the week and year entered in the form and update it in the Dateinfo field

input.Dateinfo = (((input.Date_1.getWeekOfYear())) + "") + (input.Date_1.getYear());

In the view, specify the criteria as given below. 

list  "Sample View"
{
     //to get the current week and year and compare it to the date entered in the form    
     show all rows from Sample [DateInfo == (((zoho.currentdate.getWeekOfYear())) + "") 
                                   + (zoho.currentdate.getYear())]
    (
        Name
        .....
    )
    
}

2. How do I filter views based on complex criteria?

Criteria which fetches records whose Date field is not empty and Name field contains "a" (or) Name field starts with "bell"

list "Check View"
  {
  show all rows from Check [((Date_Field is not null && Name.contains("a")) || Name.startsWith("bell"))]
  (
  Date_Field as "Date Field"
  Name
  )
  filters
  (
  Date_Field
  )
  }

3. Is it possible to create a view to display only those records added by the user?

To enable users to view and edit only their own records, you can add the following criteria given in bold below, to your view definition. The view definition can be modified from the Script tab. In the following sample code, the Adder_User is a field name used by Zoho Creator to store the login user name for each record added.

list "test View"
  {
      show all rows from test [Added_User == zoho.loginuser]
  (
      name
      address
      Email_1 as "Email 1"
      Email_Notification as "Email Notification"
  )
  }

 

4. How can I create a view in Zoho Creator which shows only the current record?

To create a view which shows only the current record,

  1. Create a hidden field in your form to identify the current record based on some value. For instance, let us create a hidden field named TXT with default value as "new". When a new record is submitted, the TXT field will hold the value "new"
  2. To update the TXT field of the previous record, with value as "old", write an on add -> validate script as given below. The on add -> validate script is executed when a new form data is submitted. The script will fetch the record whose TXT value is "new" and update it with value as "old".

    on add -> on validate script 

     on  add
        {
            on validate
            {
                if (count(Form1[TXT == "new"])  >  0)
                {
                    dat  =  Form1  [TXT == "new"];
                    dat.TXT = "old";
                }
            }
        }
  1. Create a view with criteria as [TXT == "new"] to display only the current record, as given below. 

    list  "Current Record"
    {
        show  all  rows  from  Form1 [TXT == "new"]
        (
            Name
            Number
            City
        )
    }

    Refer the sample application Show current record.

 

5. Is it possible to create a view using the getWeekOfYear() function to display records belonging to that specific week

The getWeekOfYear() function can be used to view records whose date field belongs to the current week. To do this,

1. Create a Formula field in your form with the following expression as given below, where Date_1 is the name of the date field in your form. You can also use the Added_Time field instead of your Date field, based on your requirement. The formula expression will return the week number.

Date_1.getWeekOfYear()


2. Specify the Advanced View criteria as given below, where Formula_1 is the name of the formula field added above.

Formula_1 == zoho.currentdate.getWeekOfYear()

 

6. How do I add a custom filter to my view using scripting?

Custom Filters are a set of named criteria that allows you to select only specific entries in a view. Default filters will be created for single-select, multi-select and date fields. You can create custom filters with AND/OR operators or a combination of both, using Deluge Scripting. In the following code, a custom filter named "Diploma/PostGraduate" is created with the required criteria. This filter will be listed in the Filter list box in your view. 

list "Application Form View"
        {
        show all rows from Application_Form
              (
              Name
              DOB
              EducationalQualification
              Experience
              )
       filters 
        (
        
        "Diploma/PostGraduate" : ((EducationalQualification == "Diploma" && Experience == "2 years") 
|| EducationalQualification == "PostGraduate")
        )
}

7. How do I set a custom filter to search for records where a certain field is not empty. For example, "Field does not contain [blank]"


If the field is of type "Text", you can set the Filter condition in the Free flow scripting of the View as given below and Save the script. Specify the required 'FilterName" and the "Fieldname".

"FilterName" : (Fieldname not equal to "")

If the field is of type "Number", you can set the Filter in the Free flow Scripting of the View as given below and Save the script. Specify the required 'FilterName" and the "Fieldname".

"FilterName" : (FieldName is not null)

 

Related Links:

Deluge Reference -> Built-in Functions
Deluge Reference -> Operators
Deluge reference -> Functions