SQL Query

Figure 1: Create A New Module

1. Module Name: Give a name to your module.
2. Select "SQL Query" module and click Create.

Module for building SQL SELECT only statements to query the data to use by various modules in your application.

Figure 2: Selecting Fields

Click "+ Select Fields" button to select fields from a database table.

Figure 3: You can only select and add one field at the time.

Figure 4: Basic Example of Field Selection from one table.

Adding a WHERE or Filter Condition to the Query

In some cases, the amount of data from a raw selection can be overwhelmed and can cause various performance problems. In practical application, you will want to restrict or filter your data selection to a certain condition. To accomplish this, you have to add the WHERE or a custom filter to your select query. The WHERE keyword support the following filter types:

  • Link Fields - Filter by linking Primary Key to Foreign Key fields (PK--FK) between 2 or more tables
  • Form POST/GET Field - Filter by comparing the POST/GET Form or URL field variable to the table field.
  • Parent Query Field - Filter by comparing the field variable from a Parent Query to the Child(Inner) Query.
  • Static Values - Filter by comparing to Static or Fixed values.
  • Logged-In User - Filter by the logged in User session.
  • User TimeStamp - Filter by the user logged in time.
  • Click "Where(Custom Filter)" button to add a filter fields from a database table

    Figure 5: WHERE with a POST/GET Field Variable

    POST/GET Field Variable is use when comparing the Form posted field variable or a Hyperlink passed variable with data from a database table field. Form posted or Hyperlink passed variables can be compared within one table or can from one table and compare with fields in another table.

    Figure 6: Form POST/GET Variable Example

    WHERE or Filter Condition with a "LIKE" operator

    Form Post Field with a "LIKE" operator is often used with Data Select/Search Forms. User will only need to enter part of the text being search and the query will return all matching results based on which of the following option being used. Posted Field variable with the "LIKE" operator can be used in the following options:

    Figure 7: "LIKE" operator with Option 1

    (1) %{PostedVar} - % matching anything before the {PostedVar}

    Figure 8: "LIKE" operator with Option 2

    (2) {PostedVar}% - % matching anything after the {PostedVar}

    Figure 9: "LIKE" operator with Option 3

    (3) %{PostedVar}% - % matching anything before and after the {PostedVar}

    Figure 10: "LIKE" operator query Example

    See example configuration of LIKE operator.

    WHERE or Filter Condition with a "Parent Query Field" type

    SQL Query with a "Parent Query Field" is often used when configuring Nested or Child Grid Tables. Parent query variable, usually its Index or Primary Key, is passed down to the Nested or Child Table SQL Query to query records matching the parent query inherited variable.

    Figure 11: WHERE(Filter) - Parent Query Field Type

    Figure 12: WHERE(Filter) - Static Values Field Type

    Figure 13: WHERE(Filter) - Logged-In User Field Type

    Figure 14: WHERE(Filter) - User TimeStamp Field Type