Advanced Form Data List Data Store

The Advanced Form Data List Data Store is an extended version of the default Form Data List Data Store in Joget. It allows users to add filter conditions in a guided and user-friendly manner, providing more flexibility and control over the data retrieval process.

Configure advanced form

To set up an external directory, go to Data, then select Data Store. 

Fields to Configure:

• Select Source of Data (Data Store): Choose Advanced Form Data.

• Form: Source form to retrieve data from.
• Joins Form Data Table
  • Form Data Table Name: Target table to join with.
  • Field: Target table field to join with.
  • Join Field Id: Parent field ID to join with.

Sample SQL
In the configuration example shown, the SQL query could look like this:

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

Advanced

The configuration includes three main sections: Filter, Aggregate Query, and Expression Columns.

Filter

The Filter section allows users to define specific conditions for data retrieval, ensuring that only relevant data is fetched. Users can set various operators and values to filter the dataset according to their needs.

Fields to Configure:

• Filter Conditions: Define filter conditions for the data set.
  • Join Type
    • And
    • Or
  • Field: Field ID (e.g., title).
    
  • Operator
    The following operators are available:
    • 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: Excludes rows where the column value equals the specified value.
      
      The following is the list used:
      
      For example, exclude all rows containing Test Item 3 in the name column.
      
      Result:
      
    • Greater Than: Matches rows where the column value is greater than the specified value.
      
      For example, return rows with a price column value greater than 400.
      
      Result:
      
    • Greater Than Or Equal: Matches rows where the column value is greater than or equal to the specified value.
      
      For example, return rows where the price column value exceeds or exceeds 400.
      
      Result:
      
    • Less Than: Matches rows where the column value is less than the specified value.
      
      The following is the list used:
      
      For example, return rows where the price column value is less than 400.
      
      Result:
      
    • Less Than Or Equal: Matches rows where the column value is less than or equal to the specified value.
      
      For example, return rows where the price column value is less than or equal to 400.
      
      Result:
      
    • Like: The operator is used for pattern matching and has numerous use cases. The % wildcard represents any sequence of characters, and the _ wildcard represents a single character.
      
      
      • Wildcard at Both Ends: Returns all rows where the selected field column value contains the specified characters anywhere within.
        For example, Returns rows where the description column contains 50.
        
        Result:
        
      • Wildcard at Beginning: Returns all rows where the selected field column value ends with the specified characters.
        For example, Returns rows where the description column ends with 0.
        
        Result:
        

      • Wildcard at Ending: Returns all rows where the selected field column value starts with the specified characters.
        For example, Returns rows where the description column starts with D.
        
        Result:
        

      • Single Character Wildcard: Returns all rows where the selected field column value matches the specified rules.
        For example, Returns rows where the description column contains T__0 (with any characters in the 3rd and 4th positions).
        
        Result:
        

    • Not Like: The operator excludes patterns or characters from rows. The % wildcard represents any sequence of characters, and the _ wildcard represents a single character.
      
      

      • Wildcard at Both Ends of the Value: Exclude all rows where the selected field column value matches the specified value.
        For example, it excludes rows where the description column contains 50.
        
        Result: 
        
        Wildcard at the Beginning of the Value: Exclude all rows where the selected field column value ends with the specified characters.
        For example, it excludes rows where the description column ends with 0.
        
        Result: 
        
        Wildcard at the End of the Value: Exclude all rows where the selected field column value starts with the specified characters.
        For example, it excludes rows where the description column starts with D.
        
        Result: 
        

      • Single Character Wildcard: Exclude all rows where the selected field column value matches the specified rules.
        For example, it excludes rows where the description column contains T__0 (with any characters in the 3rd and 4th positions).
        
        Result: 
        

    • In: It can be used as a category filter, where a specified column value matches any value in the provided list of values.
      
      The following is the list used:
      

      For example, the following filters for Item Name belong to Test Item 1 and Test Item 3. 
      Must be added (;) between items of specified value to work. (Item1;Item2;Item3)

Result: 

• • • Not In: Is the inverse of In. It filters out values that do not match the provided list

      of values.

      The following is the list used:
      
      This example uses item_name Test Item 3 and Test Item 1.
      Must be added (;) between items of specified value to work. (Item1;Item2;Item3)
      Result: 
      
      
      
    • Is True: Matches rows where the column value is true.
      
      The following is the list used:
      
      Example will return all rows with specified columns that are true.
      
      Result: 
      
    • Is False: Matches rows where the column value is false.
      
      The following is the list used:
      
      Example will return all rows with specified columns that are false.
      
      Result:
      
    • Is Null: The return row of a specified column value is null.
      
      The following is the list used:
      
      For example, return rows with a column description value that is NULL.
      
      Result:
      
    • Is Not Null: The return row of a specified column value is not null.
      
      The following is the list used:
      
      Example return rows with column Description value that is not NULL
      
      Result:
      
  • Value: The filter value.
• Extra Conditions: Additional HQL conditions for filtering the data set.
  
  Syntax Query
  Start your filter name with e.customProperties. followed by the field id (i.e. title)
  

  HQL is accepted
  You may even narrow your data set by an operator such as LIKE.

  

  Examples

  • Exact Match

    e.customProperties.title = 'Trip'

    SQL

  • Using Hash Variable
    

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

    SQL

  • Using Userview Key
    

    e.customProperties.category_id = '#userviewKey#'

Aggregate query

The Aggregate Query section enables users to perform aggregation functions like count, sum, min, max, and average on selected columns. This section is useful for grouping data and applying conditions to filter group results.

Fields to Configure:

• Aggregate Fields: 

  This field will be displayed once any columns have been added to the Group By field.

  The select field is to aggregate.

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

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

• Group By: Add grouping clause/function to the data set.
• Having Conditions: Specify conditions to filter group results. For more information, see http://www.dofactory.com/sql/haven.

Expression columns

The Expression Columns section allows users to add additional columns using Hibernate Query Language (HQL) for computations on multiple columns. This feature provides flexibility in creating custom computed fields within the dataset.

Fields to Configure:

• Expression Columns: Add additional columns using Hibernate Query Language (HQL) for computations on multiple columns.
  • Example 1 - Cast column to data type long
    

    CAST(price AS long)

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

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

  • Example 2 - Concatenate multiple columns into one
    

    CONCAT(first_name, ' ', last_name)

    first_name ||' '|| last_name

• Custom Checkbox/Radio Button Value: Define a custom record ID to pass over to column actions, defaulted to ID.