Versions Compared

Old Version 9

changes.mady.by.user Maxson Chen

Saved on

compared with

New Version 10

changes.mady.by.user Maxson Chen

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Figure 2: Advanced > Filter

NameDescription
Filter Conditions

Filter Conditions

NameDescription
Join Type
  • And
  • Or
Field

Field ID. (e.g. title)

Image Modified

Operator

  • Equal

    Info
    titleEqual

    Return all rows where specified column is equal to the specified value.

    The following is the list used:

Image Removed

  • Image Added

    Example returns all rows containing "

flying donut

  • Test Item 3" in "name" column.

Image Removed

  • Image Added

    Result:

Image Removed

  • Image Added

  • Not Equal

    Info
    titleNot Equal

    Exclude all rows where specified column is equal to the specified value.

    The following is the list used:

Image Removed

  • Image Added

    Example exclude all rows containing "

flying donut

  • Test Item 3" in "name" column.

    Image Added

    Result:

Image Removed

  • Image Added

  • Greater Than

    Info
    titleGreater Than

    Return all rows where value of specified column is greater than specified value 

    The following is the list used:
    Image Modified

    Example return rows where "price" column value is greater than 400.

    Image Modified

    Result:

    Image Modified

  • Greater Than Or Equal

    Info
    titleGreater Than Or Equal

    Return all rows where value of specified column is greater than or equal to specified value

    The following is the list used:
    Image Modified
    Example return rows where "price" column value is greater than or equal 400.

    Image Modified
    Result:

    Image Modified

  • Less Than

    Info
    titleLess Than

    Return all rows where value of specified column is lesser than specified value 

    The following is the list used:
    Image Modified

    Example return rows where "price" column value is lesser than 400.

    Image Modified

    Result:

    Image Modified

  • Less Than Or Equal

    Info
    titleLess Than Or Equal

    Return all rows where value of specified column is lesser than or equal to specified value 

    The following is the list used:
    Image Modified

    Example return rows where "price" column value is lesser than or equal 400.

    Image Modified
    Result:

    Image Modified

  • Like

    Info
    titleLike

    "Like" operator is used for pattern matching and have numerous use cases:

    Warning

    "%" will be used as a wildcard to represent any sequence of characters, and the "_" represents

    a single character.

    The following is the list used:

Image Removed

  • Image Added

    Wildcard at both ends

    Return all rows where selected field column value contains the specified "words" anywhere within.

    Following example return all rows where string of "

name

  • description" column contains "

vcd

  • 50".

Image Removed

  • Image Added

    Result:

Image Removed

  • Image Added

    Wildcard at beginning

    Return all rows where selected field column value ends with the specified characters.

    Following example return all rows where "

name

  • description" column ends with "

n

  • 0". 

Image Removed

  • Image Added

    Result:

Image Removed

  • Image Added

    Wildcard at ending 

    Return all rows where selected field column value starts with the specified characters.

    Example return all rows where "

name

  • description" column starts with "

Jo

  • D".

Image Removed

  • Image Added

    Result:

Image Removed

  • Image Added

    Single character wildcard

    Returns all rows where selected field column value matches the specified rules

Image Removed

  • Example return all rows where "

name

  • description" column contains "

t

  • T__

t

  • 0" (3rd & 4th character can be anything).

  • Image Added

    Result:

    Image Added

Image Removed

  • Not Like

    Info
    titleNot Like

    "Not Like" operator is used for excluding patterns or any characters from rows.

    Warning

    "%" will be used as a wildcard to represent any sequence of characters, and the "_" represents

    a single character.

    The following is the list used:

Image Removed

  • Image Added

    Wildcard at both ends

    Exclude all rows where selected field column value matches the specified value.

    Example exclude all rows with its "

name

  • description" column containing "

vcd

  • 50".

Image Removed
Image Removed

  • Image Added

    Wildcard at beginning

    Exclude all rows where selected field column value ends with the specified characters.

    Example exclude all rows where "

name

  • description" column ends with "

n

  • 0". 

Image Removed

  • Image Added

    Result:

Image Removed

  • Image Added

    Wildcard at ending 

    Exclude all rows where selected field column value starts with the specified characters.

    Example exclude all rows where "name" column starts with "

Jo

  • D".

Image Removed

  • Image Added

    Result:

Image Removed

  • Image Added

    Single character wildcard

    Exclude all rows where selected field column value matches the specified rules

Image Removed

  • Image Added

    Example exclude all rows where "

name

  • description" column contains "

t

  • T__

t

  • 0" (3rd & 4th character can be anything).

    Result:

Image Removed

  • Image Added

  • In

    Info
    titleIn

    "In" can be used for category filter where a specified column value matches any value of the provided list of

    values. The following is the list used:

Image Removed

  • Image Added

    "In" Operation can be used to filter result based on a list of specified values. For example, the following filters

    for "

Type

  • Item Name" that belongs to "

Powdered

  • Test Item 1" and "

Glazed

  • Test Item 3". 

    Warning
    titleAttention

    A ";" must be added in between items of specified value for it to work. (Item1;Item2;Item3)

Image Removed




  • This will result in rows with type that matches the provided list of values. Resulting in the following:

    Image Modified

"

  • Not In

" on the other hand works the opposite to "In", it filters out values that does not match the providedlist of values . In this example, type of "Powdered" and "Glazed"

Not In

Image Removed

Resulting in:
Image Removed

  • Info
    titleNot In

    "Not In" is the inverse of "In", it filters out values that does not match the provided list

    of values.

    The following is the list used:

    Image Modified

    In this example, type of "Powdered" and "Glazed" are used.

    Warning
    titleAttention

    A ";" must be added in between items of specified value for it to work. (Item1;Item2;Item3)

    Image Modified

    Resulting in:
    Image Modified

  • Is True

    Info
    titleIs True

    The following is the list used:

    Image Modified

    Example will return all row with specified column that is true

    Image Modified

    Result

    Image Modified

  • Is False

    Info
    titleIs False

    The following is the list used:

    Image Modified

    Example will return all row with specified column that is false

    Image Modified

    Result:

    Image Modified

  • Is Null

    Info
    titleIs Null

    Return row of specified column value that is NULL

    The following is the list used:
    Image Modified

    Example return rows with column "name" value that is NULL

    Image Modified

    Result:

    Image Modified

  • Is Not Null

    Info
    titleIs Not Null

    Return row of specified column value that is not NULL

    The following is the list used:
    Image Modified

    Example return rows with column "name" value that is not NULL

    Image Modified

    Result:
    Image Modified

ValueFilter value
Extra Conditions

Additional condition(s) for filtering the data set. HQL is expected here.

Info
titleSyntax Query
Start your filter name with e.customProperties. followed by the field id (i.e. title)
Image Modified
Info
titleHQL is accepted

You may even use an operator such as "LIKE" to narrow down your data set.

Image Modified

Code Block
titleSample
e.customProperties.title = 'Trip'

A hash variable is accepted here.

Code Block
titleSample
e.customProperties.submitted_by = '#currentUser.id#'

Userview Key can be used as part of the condition.

Code Block
languagesql
titleSample
e.customProperties.category_id = '#userviewKey#'

Aggregate Query

Figure 3: Advanced > Aggregrate Query

...

NameDescription
Expression Columns

An additional column can be added in this expression columns using Hibernate Query Language (HQL). This is especially useful when you need to perform additional computation on multiple columns.

Example 1 - Cast column to data type "long".

Code Block
titleExpression
CAST(price AS long)
Code Block
titleExpression
SUBSTRING(CAST(dateCreated as string),1,10)   // To extract the date from the datetime database column
Code Block
titleExpression
CAST(e.customProperties.sales_price AS long) - CAST(e.customProperties.price AS long)

Example 2 - Concatenate multiple columns into one.

Code Block
titleExpression
CONCAT(first_name, ' ', last_name)
Code Block
titleExpression
first_name ||' '|| last_name
Custom Checkbox/Radio Button Value

Define custom record ID to be used to pass over to column action. Defaulted to ID.