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

Compare with Current View Page History

« Previous Version 4 Next »

Instruction

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.

Advanced Form Data Binder Properties

Configure Advanced Form Data Binder

NameDescriptionScreen (click to view)
FormSource form to retrieve data from.

Figure 1: Configure Advanced Form Data Binder


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



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


Advanced

Filter

NameDescriptionScreen (Click to view)
Filter Conditions

Filter Conditions

NameDescription
Join Type
  • And
  • Or
Field

Field ID. (e.g. title)

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

Figure 2: Advanced > Filter

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

NameDescriptionScreen (Click to view)
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.

Figure 3: Advanced > Aggregrate Query


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

NameDescriptionScreen (Click to view)
Expression Columns

Additional column can be added in this expression columns. This is especially useful when you need to perform additional computation on multiple columns.

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



Custom Checkbox/Radio Button Value

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




  • No labels