Versions Compared

Old Version 7

changes.mady.by.user Maxson Chen

Saved on

compared with

New Version Current

changes.mady.by.user Aadrian Zarin

Saved on

Key

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

Table of Contents

Introduction

English
Advanced Form Row Data Data Store is an extended version of the default Form Data List Data Store. It allows you to add in Filter Conditions in a guided and friendly manner.

Advanced Form Data Data Store Properties

...

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 a specified column is equal to the specified value.

    The following is the list used:
    Image Added

    Example returns all rows containing "Test Item 3" in the "name" column.

    Image Added

    Result:

    Image Added

  • Not Equal

    Info
    titleNot Equal

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

    The following is the list used:
    Image Added

    For example exclude all rows containing "Test Item 3" in the "name" column.

    Image Added

    Result:

    Image Added

  • Greater Than

    Info
    titleGreater Than

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

    The following is the list used:
    Image Added

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

    Image Added

    Result:

    Image Added

  • Greater Than Or Equal

    Info
    titleGreater Than Or Equal

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

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

    Image Added
    Result:

    Image Added

  • Less Than

    Info
    titleLess Than

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

    The following is the list used:
    Image Added

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

    Image Added

    Result:

    Image Added

  • Less Than Or Equal

    Info
    titleLess Than Or Equal

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

    The following is the list used:
    Image Added

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

    Image Added
    Result:

    Image Added

  • Like

    Info
    titleLike

    The "Like" operator is used for pattern matching and has 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 Added

    Wildcard at both ends

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

    The following example returns all rows where a string of the "description" column contains "50".

    Image Added

    Result:

    Image Added

    Wildcard at beginning

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

    The following example returns all rows where the "description" column ends with "0". 

    Image Added

    Result:

    Image Added

    Wildcard at ending 

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

    For example, return all rows where the "description" column starts with "D".

    Image Added

    Result:
    Image Added

    Single character wildcard

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

    For example, return all rows where the "description" column contains "T__0" (3rd & 4th characters can be anything).

    Image Added

    Result:

    Image Added

  • Not Like

  • In
  • Not In
  • Is True
  • Is False
  • Is Null

    • Is Not Null

    Info

    • Info
      titleNot Like

      The "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 Added

      Wildcard at both ends of the value

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

      For example, exclude all rows with its "description" column containing "50".

      Image AddedBorder

      Result:

      Image Added

      Wildcard at the beginning of the value

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

      For example, exclude all rows where the "description" column ends with "0". 

      Image Added

      Result:

      Image Added

      Wildcard at the end of the value

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

      For example, exclude all rows where the "name" column starts with "D".

      Image Added

      Result:
      Image Added

      Single character wildcard

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

      Image Added

      For example, exclude all rows where the "description" column contains "T__0" (3rd & 4th characters can be anything).

      Result:

      Image Added

    • In

      Info
      titleIn
    titleIn & Not In examples

    • "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

    • results based on a list of specified values. For example, the following filters

      for "

    Type

    • Item Name"

    that belongs

    • belong to "

    Powdered

    • Test Item 1" and "

    Glazed

    • Test Item 3". 

      Warning
      titleAttention

      A ";" must be added in between items

    in

    • of specified value for it to work. (Item1;Item2;Item3)

    Image Removed

    • Image Added

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

    Image Removed

    • Image Added

    • Not In

      Info
      titleNot In

      "Not In"

    on the other hand works the opposite to

    • is the inverse of "In", it filters out values that

    does

    • do not match the provided list

    list

    • of values.

      The following is the list used:

      Image Added

      In this example,

    type of "Powdered" and "Glazed"

    Image Removed

    • item_name "Test Item 3" & "Test Item 1" are used.

      Warning
      titleAttention

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

      Image Added

      Resulting in:
      Image Added

    • Is True

      Info
      titleIs True

      The following is the list used:

      Image Added

      Example will return all rows with specified columns that are true

      Image Added

      Result:

      Image Added

    • Is False

      Info
      titleIs False

      The following is the list used:

      Image Added

      Example will return all rows with specified columns that are false

      Image Added

      Result:

      Image Added

    • Is Null

      Info
      titleIs Null

      Return row of a specified column value that is NULL

      The following is the list used:
      Image Added

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

      Image Added

      There are no NULL values in column "Description" resulting in:

      Image Added

    • Is Not Null

      Info
      titleIs Not Null

      Return row of a specified column value that is not NULL

      The following is the list used:
      Image Added

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

      Image Added

      Result:
      Image Added

    Resulting in:
    Image Removed

    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 Aggregate Query

    Figure 4: The configurations shown in Figure 3 will produce the following sample result.

    NameDescription
    Group By

    Add grouping clause/function to the eventual data set. This can be used together with Aggregate Fields above.

    In figure Figure 3, the "amount" field will be summed up by "claimant", shown in per record row.

    Aggregate Fields

    This field will be displayed once any number of columns has been added into to the Group By field.

    The select field is to aggregate.

    • Count
    • Count Distinct
    • Sum
    • Min
    • Max
    • Avg

    In the sample screenshot above, the "amount" field will be put into the "Sum" function, and "Count" will be applied to the "title".

    Having Conditions

    This field will be displayed once any number of columns has been added into to the Group By field.

    The HAVING clause enables you to specify conditions that filter which group results appear in the final results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. Read more at http://www.dofactory.com/sql/having

    ...

    NameDescription
    Expression Columns

    An additional column can be added in this expression columns column 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.

    Sample App


    View file
    nameAPP_kb-dx8_advancedFormDatalistDataStoreOperatorExample.jwa
    height250