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

...

Name Description

Filter Conditions

Name Description

Join Type

And
Or

Field

Field ID. (e.g. title)

Operator

Equal
Info
title Equal
Return all rows where a specified column is equal to the specified value.
The following is the list used:
Example returns all rows containing "Test Item 3" in the "name" column.
Result:

Not Equal

Info

title	Not Equal

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

The following is the list used:

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

Result:

Greater Than

Info

title	Greater Than

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

The following is the list used:
Image RemovedImage Added

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

Result:

Image RemovedImage Added

Greater Than Or Equal

Info

title	Greater 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 RemovedImage Added
Example return rows where the "price" column value is greater than or equal to 400.

Result:

Image RemovedImage Added

Less Than

Info

title	Less Than

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

The following is the list used:
Image RemovedImage Added

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

Result:

Image RemovedImage Added

Less Than Or Equal

Info

title	Less 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 RemovedImage Added

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

Result:

Image RemovedImage Added

Like

Info

title	Like

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

Wildcard at both ends

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

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

Image Modified

Result:

Image Modified

Wildcard at beginning

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

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

Image Modified

Result:

Image Modified

Wildcard at ending

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

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

Image Modified

Result:
Image Modified

Single character wildcard

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

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

Image Modified

Result:

Image Modified

Not Like

Info

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

Wildcard at both ends of the value

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

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

Border

Result:

Wildcard at the beginning of the value

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

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

Result:

Wildcard at ending the end of the value

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

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

Result:

Single character wildcard

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

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

Result:

In

Info

title	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 results based on a list of specified values. For example, the following filters

for "Item Name" that belongs belong to "Test Item 1" and "Test Item 3".

Warning

title	Attention

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

Image Added

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

Image RemovedImage Added

Not In

Info

title	Not In

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

of values.

The following is the list used:

Image RemovedImage Added

In this example, type of "Powdered" and "Glazeditem_name "Test Item 3" & "Test Item 1" are used.

Warning

title	Attention

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

Image RemovedImage Added

Resulting in:
Image RemovedImage Added

Is True

Info

title	Is True

The following is the list used:

Image RemovedImage Added

Example will return all row rows with specified column columns that is are true

Image RemovedImage Added

Result:

Image RemovedImage Added

Is False

Info

title	Is False

The following is the list used:

Image RemovedImage Added

Example will return all row rows with specified column columns that is are false

Image RemovedImage Added

Result:

Image RemovedImage Added

Is Null

Info

title	Is Null

Return row of a specified column value that is NULL

The following is the list used:
Image RemovedImage Added

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

Image Removed

Result:

Image Added

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

Image AddedImage Removed

Is Not Null

Info

title	Is Not Null

Return row of a specified column value that is not NULL

The following is the list used:
Image RemovedImage Added

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

Image RemovedImage Added

Result:
Image RemovedImage Added

Value Filter value

Extra Conditions

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

Info

title	Syntax Query

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

Info

title	HQL is accepted

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

Code Block

title	Sample

e.customProperties.title = 'Trip'

A hash variable is accepted here.

Code Block

title	Sample

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

Userview Key can be used as part of the condition.

Code Block

language	sql
title	Sample

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.

Name

Description

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

...

Name

Description

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

title	Expression

CAST(price AS long)

Code Block

title	Expression

SUBSTRING(CAST(dateCreated as string),1,10)   // To extract the date from the datetime database column

Code Block

title	Expression

CAST(e.customProperties.sales_price AS long) - CAST(e.customProperties.price AS long)

Example 2 - Concatenate multiple columns into one.

Code Block

title	Expression

CONCAT(first_name, ' ', last_name)

Code Block

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

Sample App

View file

name	APP_kb-dx8_advancedFormDatalistDataStoreOperatorExample.jwa
height	250

Space shortcuts

Page tree

Versions Compared

Old Version 10

New Version Current

Key

Introduction

Advanced Form Data Data Store Properties

Aggregate Query

Sample App

Space shortcuts

Page tree

Page History

Versions Compared

Old Version 10

New Version Current

Key

Introduction

Advanced Form Data Data Store Properties

Aggregate Query

Sample App