Query Builder Control

The Query Builder control enables you to define your query for scenarios where you need server-side filtering based on criteria defined at run time.

You can use it with your connectors by providing the query based on the dynamic selection. The advantage of using the Querybuilder is that it allows you to add the query to data sources for controls like Table grid and list controls where dynamic filtering is needed.

Properties

UI properties

  • Label: Label is the text that is displayed above the container box and helps the user to understand what the control represents in the micro app.

  • Properties: These properties help you customize the Label by changing the text size, weight( Light, Normal, Bold, Extra Bold, X Extra Bold), alignment (Left, Center, Right, Justified) and color of the label.

  • Type: The Type field is automatically generated depending upon the type of control used. In this case, it is a “ dhq_query_builder” type.

  • Unique Name: A Unique Name is used to uniquely identify a control in your App. Every control that is added on the screen gets an auto-generated Unique Name based on the Label name given to the control. Here you can see that there are two parts, the Raw JSON and the Query. So the unique names would be generated likewise.

  • Submit Data: This field lets you determine whether to send the value of the control or not in Workflow based on various control states. There are three conditions that could be set for this field.

    1. Always: When this condition is selected, the data in the Name Control will be submitted irrespective of the state of the Name Control.
    2. Never: When this condition is selected, the data in the Name Control will never be submitted irrespective of the state of the Name Control.
    3. No When Hidden: When this condition is selected, the data in the Name Control will be submitted if the Name Control is visible and not submitted when the Name Control is hidden .
  • Required: When you select the required option, the field becomes a mandatory field and the user can not submit without filling it.

  • Read Only: This restricts the use of the control, the user will no longer be able to edit this field.

  • Hidden: The hidden option helps you to hide the control from the user. This might help to perform some computation you don’t want the user to see or if you want to show certain fields only when a specific condition is met/not met.

  • Caching: When the caching option is selected and if a LOOKUP formula is applied to this control, the value that the LOOKUP returns will be cached and the user will be able to see this value in the field even when the user accesses the microapp later with no connectivity. This option is required when you want your app to be accessible in offline mode as well and when getting the latest data from the sheets is not critical.

  • Hide Label: This option hides the label of the field from the view.

Properties

schema

  • Database: Is the database type to send the query. It can be SQL or NoSQL. When you select the NoSQL type you get the option to add the Dynamic Operators.
  • Theme: Specifies the color scheme or the theme for the composite control.
  • Schema: This is the JSON format for the fields available to use in the query builder.

A sample of the Schema is added here for a Product data use case.

[
   { "label": "Product Name",
     "value": "ProductName",

   "enum": [
          {  "label":"Sandwich",
             "value":"Sandwich"
          },
         {
           "label":"Burger",
           "value":"Burger"
         },
        {
          "label":"All",
          "value":""
         }
      ]
},

{ "value": "ProductID",
  "label": "Product ID"
},

{ "value":"Price",
  "label":"Price",
  "type":"number"
},

{ 
   "label": "Prod Quantity",
   "value": "Quantity"
}

]

Dynamic Operators

Dynamic Operators enables you to Add operators which are not present in Query Builder control by default.
Note:

  • For String type, the control will automatically add double quotes to the value field in resolved output JSON query. For any other type, you will be responsible to provide a value that is supported in your NoSQL parameter. For example, $all expects array type in the value fields so it is your responsibility to provide array value in the value field of your query control.

The Dynamic Operators under Properties is used to provide the operators that you want to define for the type of data you use. You can customize the operators with String type supported by Studio.

Add your specific operators in the Dynamic Operator section under Properties .

Dynamic operators

Whenever you run the form and plan to create the queries using the additional operators you can see them listed out for the String type data.

dynamic operators list

In the example taken here, you can see the query that is built for the Product ID with the $Exists operator.

Action Flow

On_apply: This action is triggered when you select the query parameters and click the Apply button.

On_clear: This action is triggered when you click the clear button.

How to use the Query Builder Control?

The Query Builder Control is available under Controls > Custom menu. Add the control to your form and assign the above properties as required.

Let us take an example to display connector data in a tablegrid control on a screen based on the filter enabled from a popup screen as an action of the tablegrid control. Select the query parameters and then go back and display data in your tablegrid control. When you are configuring the Connector you would keep a dynamic variable that would append the where clause to your SQL. This is the critical part that you would be achieving with the Query builder control.

So first let us see how we would define the query to a MySQL connector. Under Connectors, select the MySQL connector and click Add Query. Add a variable to the query. You can enter the Test Value with the sample Where clause as seen in the illustration below.

Add Variable and specify your query with the necessary fields. Now, remember that where you would be adding the condition with the where clause you can add the dynamic variable that we have created. If you Run this query you can see that the Test value would be appended in the place of the variable. The Query builder would be adding this where clause when you use your application. Add a name to the query and Save it. This query would be bound to your Tablegrid control.

So now on the Popup screen when you configure the Query Builder Control your Schema field plays an important role.

Here you would specify the JSON Key-Value format that provides the fields that would be available for filtering. They support the data types String and Number currently but would shortly support more of these. Enter the appropriate JSON for the Schema under Properties.

If you want you can also add two Text Input controls that can be used to display the SQL and JSON filter generated by the use of the control. You can use the Bind Data > Controls to add the Query builder’s Query or RawJSON.

querystring demo

On the click of the Apply button, you can add an event with the on_apply Action. You can specify to navigate back to the screen with the Tablegrid control.

You would now bind data from the connector to the TableGrid control. Select the Tablegrid control > Bind data > Connectors > Select connector. Now from the connected ones you can choose the MySQL database that you had configured. Select the query and the connected Account. Add a connector name. Below the Query, you can see the dynamic variable is listed out under Fields. Here you need to provide the querybuilder.Query keyword.

Add the necessary fields and click Finish. Under Connector select the Keys to display and in the order you want. Click Save. You can run the form and view the working of the Query builder. In this example here we have added the action to navigate back to the screen with Table grid control from the Popup screen by adding the action under Action > on_apply of the Query builder.

Note: The output of the query builder control supports SQL queries currently.

To know more about how Query Builder can be useful when using the NoSQL Connectors, you can refer to this article here.

Preview