Versions Compared

Old Version 1

changes.mady.by.user Hugo

Saved on

compared with

New Version Current

changes.mady.by.user PT

Saved on

Key

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

...

Table of Contents

Introduction

English
borderColorgreen
borderWidth1
titleBGColor#ddffcc
borderStylesolid
titleDefinition
Advanced Form Row Data Binder is an extended version of the default Form Data Binder. It allows you to add in Filter Conditions in a guided and friendly manner.
Panel
borderColorpurple
borderWidth1
titleBGColor#ddccff
borderStylesolid
titleNew Feature

This feature has been enhanced in Joget Workflow v6 to support join, group and aggregate function.

Image Removed

Advanced Form Data Binder Properties

Configure Advanced Form Data Binder

Image Added

Figure 1: Configure Figure 1: Advanced Form Data Binder Properties

NameDescription
FormSource form to retrieve data from.
Joins Form Data Table
Info
titleNew Feature
This is a new feature in Joget Workflow v6.
NameDescription
Form Data Table NameTarget table to join with
FieldTarget table field to join with
Join Field IdParent field Id to join with



Info
titleSample

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

Code Block
languagesql
titleSample SQL
SELECT * FROM "Claim Entry" entry JOIN hr_expense_claim claim ON claim.id = entry.claim

...



Advanced

...

Filter

Image RemovedImage Added

Figure 2: Advanced Form Data Binder Properties - Advanced - > Filter

NameDescription
Filter Conditions

Filter Conditions

NameDescription
Join Type
  • And
  • Or
Field

Field ID. (e.g.

title

username)

Image Removed

Image Added

Operator
  • Equal
  • Not Equal
  • Greater Than
  • Greater Than Or Equal
  • Less Than
  • Less Than Or Equal
  • Like
  • Not Like
  • In
  • Not In
  • Is True
  • Is False
  • Is Null
  • Is Not Null
ValueFilter value
Extra Conditions

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

the

expected here.

Info
titleSyntax Query
Start your filter name with e.customProperties. followed by the field id (i.e.
title
username)
Image Removed
Image Added
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'
Hash

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

...

Image Added

Figure 3: Advanced

...

> Aggregrate Query

...

titleNew Feature

...

Image Added

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
Select

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

The select field is to

aggregatedHaving Conditions

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

Group By

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

In the sample screenshot above, the "amount" field will be summed up by "claimant", shown in per record row.

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

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

Image Removed

Figure 4: Sample result

Image Removed

Figure 5: Advanced Form Data Binder Properties - Advanced - Expression Columns

...

titleNew Feature

...

Expression Columns

Image Added

Figure 5: Advanced > Expression Columns

NameDescription
Expression Columns
Additional

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
title
Sample
Expression
CAST(price AS long)
Code Block
SUBSTRING(CAST(dateCreated as string),1,10)   // To extract the date from the datetime database column
Code Block
title
Sample
Expression
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.

Image Modified