You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 8 Next »

Introduction

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

Configure Advanced Form Data Data Store

Figure 1: Configure Advanced Form Data Data Store

NameDescription
FormSource form to retrieve data from.
Joins Form Data Table
NameDescription
Form Data Table NameTarget table to join with
FieldTarget table field to join with
Join Field IdParent field Id to join with



Sample

In the screenshot example in Figure 1, such configurations can be presented with the following SQL.

Sample SQL
SELECT * FROM "Claim Entry" entry JOIN hr_expense_claim claim ON claim.id = entry.claim



Advanced

Filter

Figure 2: Advanced > Filter

NameDescription
Filter Conditions

Filter Conditions

NameDescription
Join Type
  • And
  • Or
Field

Field ID. (e.g. title)

Operator

  • Equal

    Equal

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

    The following is the list used:

    Example returns all rows containing "flying donut" in "name" column.

    Result:

  • Not Equal

    Not Equal

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

    The following is the list used:

    Example exclude all rows containing "flying donut" in "name" column.

    Result:

  • Greater Than

    Greater Than

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

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

    Result:

  • Greater Than Or Equal

    Greater Than Or Equal

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

    Result:

  • Less Than

    Less Than

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


    Result:

  • Less Than Or Equal

    Less Than Or Equal

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



    Result:

  • Like

    Like

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

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

    a single character.

    The following is the list used:

    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" column contains "vcd".

    Result:

    Wildcard at beginning

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

    Following example return all rows where "name" column ends with "n". 

    Result:

    Wildcard at ending 

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

    Example return all rows where "name" column starts with "Jo".

    Result:

    Single character wildcard

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

    Example return all rows where "name" column contains "t__t" (3rd & 4th character can be anything).

  • Not Like

    Not Like

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

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

    a single character.

    The following is the list used:

    Wildcard at both ends

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

    Example exclude all rows with its "name" column containing "vcd".

    Result:

    Wildcard at beginning

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

    Example exclude all rows where "name" column ends with "n". 

    Result:

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

    Result:

    Single character wildcard

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

    Example exclude all rows where "name" column contains "t__t" (3rd & 4th character can be anything).

  • In

    In

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

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

    for "Type" that belongs to "Powdered" and "Glazed". 

    Attention

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




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


    "Not In" on the other hand works the opposite to "In", it filters out values that does not match the provided

    list of values . In this example, type of "Powdered" and "Glazed"

    Resulting in:

  • Not In

    Not 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:

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

    Attention

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

    Resulting in:

  • Is True
  • Is False
  • Is Null

  • Is Not Null


ValueFilter value
Extra Conditions

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

Syntax Query

Start your filter name with e.customProperties. followed by the field id (i.e. title)

HQL is accepted

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

Sample
e.customProperties.title = 'Trip'

A hash variable is accepted here.

Sample
e.customProperties.submitted_by = '#currentUser.id#'

Userview Key can be used as part of the condition.

Sample
e.customProperties.category_id = '#userviewKey#'

Aggregate Query

Figure 3: Advanced > Aggregrate 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 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 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 "title".

Having Conditions

This field will be displayed once any number of columns has been added into 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

Expression Columns

Figure 5: Advanced > Expression Columns

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

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

Example 2 - Concatenate multiple columns into one.

Expression
CONCAT(first_name, ' ', last_name)
Expression
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.




  • No labels