Data Filtering/Search in Calem

In Calem Enterprise R11 to be available in March 2018, there are three ways to query a data list in Calem. They are listed below by ease of use for end users. 

• Free text search – it is simple to use. Calem will search a fixed set of fields in the data list.
• List search row – it is intuitive to use. The search row shows in the list view so you can search for a specific field. 
  
• Search form – it is an advanced search in Calem. You can search for data fields in a list. You can also create a search and save it for use later.

1. Free Text Search

Free Text Search is a convenient tool for filtering data in the grid. You can enter a free text and have the grid filtered based on the input. All free text fields (such as notes and descriptions) including customized fields and selective lookup fields (such as asset number, item number) are filtered. When instant search is turned on, the grid is filtered without "Enter" key. The help icon displays fields that are searched. You may see Admin Guide to add more lookup fields to the search based on your business needs.

Free Text Search supports wild card (%) search.

There are a few ways to do advanced search with free text:

• Search for a pattern by wild card (%). You can use wildcard (%) to separate words so text in that order is found. For instance, searching for 'nice%phone' will match "This is a nice plus screen phone", but not "This plus screen phone is real nice" since "nice" must come before "phone".
• Search for multiple key words separated by space. For instance, type in 'nice phone' will match text with both words regardless of the order. So, both "This is a nice plus screen phone" and "This plus screen phone is real nice" will be matched.
• Search for key words separated by ' or ' (space + or + space) will cause an or search being applied. For instance,you can search 'nice or phone" to match text including either "nice" or "phone".

2. List Search Row

List search row is embedded in the list view as the first row when shown. It allows for quick search at field level so it is intuitive and fast. It is better than free text search when dealing with large data sets (tens of thousands of records).

 Here are the highlights of the list search row:

• An "Enter" key will launch a search.
• The list search row can be toggled by the search row button in the list menu bar.
• Click "Search clear" button in the list toolbar clears the search list row fields.
• You can specify operators such as equal sign. For example, in the screenshot above, "=A004670" will find asset with asset number being "A004670".
  • The signs can be "=", ">", "<", ">=", "<=".
  • The signs can be applicable to date, number and text fields.
  • The search values of date and date time fields should be date values in the local edit format.
• The advanced expressions defined in free text search is supported.

 2.1 Add Fields to List Search Row

A field to search might not be present in a list view. Right-click by mouse on the caption row of a list to show the full list of fields. Click a field to show in the list. The field is now searchable by List Search Row.

• For instance, "Manufacturer" field is not present in the asset list.
• Right-click on the caption row to show asset field list (screenshot below).
• Click "Manufacturer" to show it in the list.
• Then, search the field (next screenshot).

 3. Search Form

Search form allows you to apply a previously saved filter or create a new filter.The "Search" button from a list form opens up the saved search form. Users with "Search admin" field setting to "System admin" can create searches and save as shared searches.

The saved search list screen is shown. You may click a saved search to apply or click "+" to create a new search from either my search or shared search list. 

Click "+" launches the search form. You can define search values, "Apply" the search, or "Apply and Save" the search. 

 3.1 Search Operator

Search operators are available when searching a field including equal (=), like, matching any of a list of values (IN), etc.

 3.2 Multiple Selection of Dropdown and Lookup Values

You can use "IN" operator to: a) select multiple dropdown values (control key + mouse click in Windows, or command key + click in Mac); or b) select multiple lookup values in a lookup form (have multiple lookup records checked and use "Select (1+)" to select them). See this blog for more info.

3.3 MySQL Date Function

You can use native MySQL date functions (https://dev.mysql.com/doc/refman/5.5/en/functions.html) in date fields. The format is ${sql_expr} where "${" and "}" tell Calem to use the sql_expr as is in the query. For instance, if you type in to the date field "${curdate()}", the MySQL current date function "curdate()" will generate current date (today) in the query.   The query string "${subdate(curdate(), interval 30 day)}" can be used to query a date value that is 30 days ago from today.

4. Cancel Search

It is possible to cancel search. This feature is useful when free text search or search initiated by users is taking too long. "Cancel Request" may be clicked to cancel a search. 

5. Which Search Method?

End users will find Free Text Search and List Search Row much easier to use than the Search Form. Administrators can use Search Form to create often-used searches and save them for end users to use.

Free Text Search is not as efficient as List Search Row and Search Form when dealing with large data sets (with tens of thousands of data rows). Administrators may choose to hide Free Text Search in such cases when needs arise. This can be done through form customization.

Additional resources

• How to Search a Lookup Field by List of Values
  
• Add Free Text Search to a List View
• Expression in Free Text Search
• Saved Search in Calem
• List View Customization
• User Guide and Admin Guide (customer account required)
• Calem Enterprise Training Site
  
• Calem Enterprise Blogs
  
• Calem Enterprise demo