Introduction to the Query Builder

Overview

The Query Builder tool enables Creators Also known as Unqork Users, or Designer Users; is anyone who is inside the Unqork platform. and Administrators to target specific data captured by their applications. Query Builder is similar to a SQL SQL (Structured Query Language) is language used in programming and designed for managing data contained in a relational database system. builder tool in that you create a logic statement to retrieve specific information from a database. The information can then be retrieved into a module using a Plug-In component.

Query Builder is optimized for accessing data stored against Data Model schemas, reducing the need to build complex data workflows for targeting and retrieving data.

Benefits of the Query Builder Tool

Retrieving data from an unstructured or large database can be a slow and ineffective process. In Unqork, utilizing Data Models and the Query Builder tool enables you to efficiently retrieve and analyze data using SQL-like queries A query lets you retrieve, access, manipulate, and delete data in a database.. With the Query Builder, you build queries using logical and comparison operators on data fields. You can then reference these queries in a Plug-In component to retrieve data to display in a Table or Grid component.

Benefits include:

  • A simple-to-use interface for building and refining queries.

  • Quickly build queries using logical and comparison operators to retrieve specific data without the need for complex data workflows.

  • Optimize data retrieval time by targeting only the data you need.

  • Easily refer to prebuilt queries using the Plug-In component in modules.

Navigating the Query Builder Tool

The Query Builder tool is located at the Workspace level of your environment. The Query Builder is located under Data Elements to the left of the Workspaces page.

The Query Builder tool requires Designer Administrator access to use.

Accessing the Query Builder Tool

To access the Query Builder from the homepage:

1. Navigate to and select a Workspace.
2. From the left-side menu, under Data Elements, select Queries.

A static image displaying a Workspace, the Queries button is highlighted.

Navigating the Queries Page

The Queries page displays a list of all active and archived queries in the workspace. From this page, you can create a new Query and review, modify, or delete an existing one.

A static image displaying the Queries page.

 

Setting

Description

1

+ Create Query

Click to open the Create New Query page.

 

Queries

Displays a list of queries that exist in the workspace.

2

Active Queries

Sorts between active queries and archived queries.

  • Active Queries: Lists queries available to Creators in the workspace. Active queries be modified or archived to the Archived Queries list.

  • Archived Queries: Lists queries not available for use in Plug-In components or the workspace, but they can be modified or restored to the Actives Queries list.

3

Name

The name of the query. Click to view the query's logic page.

4

 Associated Data Model / Module

The associated Data Model or module the query is using. Click to open the Data Model or module on a new page.

5

Type

Displays if the query is targeting submission data from a module or Data Model.

6

Created

The date and time when the query was created. This value is permanent and cannot be changed.

7

Last Modified

The date and time the query was last edited. This value changes whenever a query's logic is modified.

8

Actions

Click the Archive button to display a confirmation modal A modal is a window that appears on top of the content you are currently viewing.. Click Yes, Archive to move the query to the Archived Queries list. Archived queries can be modified, but cannot be used in a Plug-In component.

To restore an archived query, set Active Queries to Archived Queries, then click the Restore button in the row of the query you want to restore.

Navigating the Create New Query Page

A static image displaying the Create New Query Page.

 

Setting

Description

1

Last saved

Displays the date and time the query was saved. If it is a new query, then it displays never saved.

2

 Name*

Enter a name for the Query.

*This field is required to create a Query.

3

Query Type*

Choose to query a Data Model or Submission Data.

*This field is required to create a query.

4

 Data Model / Module*

The associated Data Model or module the query is using. Click to open the Data Model or module on a new page.

*You must select a Data Model or module to create a query.

5

Add Rule

Creates a rule object inside the group object. The rule applies when querying the Data Model or module. Rules contain query parameters and logical operators that filter the Data Model or module's records.

6

Add Group

A creates a group object inside of the original group. Each group can apply a different set of logical operators and contains its own rules. Groups can contain another group.

7

Logical Operators

Logical Operators are used to combine or manipulate the group conditions in a query. The Query Builder supports the following Logical Operators:

Not: Used to give the opposite result. For example, returning results for all clients that do NOT live in Canada.

And: Filters results on more than one condition. For example, returning results for all country names AND customer names that start with the letter "S".

Or: Filters results on one or more conditions. For example, returning results for all clients from Canada OR Mexico.

8

Field

Searches against record or data fields. Data Model data fields are defined in the model's schema. Submission Query data fields are defined in the module's submission data.

The Query Builder tool's Submission Query fields are determined using the last 1,000 submissions. Modifying field IDs in a module might result in the older ID becoming unavailable in the Query Builder tool.

Queries can also search against record details, like the record id, creation date, and more.

Submission Query record details include:

  • _id: The record's unique identifier.

  • created: The date and time the record was created.

  • deleted: The date and time the record was deleted.

  • form: The form's unique identifier.

  • formArchive: The form archive's unique identifier.

  • isRevision: If the record has a revision.

  • metadata: Data contained in the meta data array. This array can be empty.

  • modified: The date and time the record was last modified.

  • moduleArchiveIdAtCreated: The module archive's unique identifier at the time of creation.

  • moduleArchiveIdAtModified: The module archive's unique identifier after the last modification. This value can be NULL if the module has not been modified.

  • owner: The email address of the user that created the record.

  • platformVersionAtCreated: The platform version at the time of module creation.

  • platformVersionAtModified: The platform version after the last modification. This value can be NULL if the module has not been modified.

  • validationErrors: Data contained in the validationErrors array. This array can be empty.

Model Query record details include:

  • _id: The record's unique identifier.

  • created: The date and time the record was created.

  • modified: The date and time the record was last edited.

  • owner: The email address of the user that created the record.

  • archived: Records that have been archived and are no longer active.

 

9

Comparison Operator

Comparison operators test the field against the constant string or variable name.

Available comparison operators include:

  • == (Equal To): Tests for values that are equal to the string.

  • != (Not Equal To): Tests for values that are not equal to the string.

  • Contains: Tests for values that contain the string.

  • Not Contains: Tests for values that do not contain the string.

  • Starts With: Tests for values that start with the string.

  • Ends With: Tests for values that do not start with the string.

  • Is Empty: Test for values containing empty strings.

  • Is Not Empty: Tests for all values that are not empty strings.

  • Is Null: Tests for NULL values. If a field in the submission data is optiona,l and its value is left blank, then the value is NULL.

  • Is Not Null: Tests for all values that are not null.

10

Constant or Variable

Choose if the field is a constant or a variable. Constants are predefined values and variables are passed into the query from the Plug-In component's API call.

11

Constant String or Variable Name

Enter the value of the constant or variable to filter by.

12

Sort By

When the query executes, choose what field the returned data sorts by.

13

Sort Order

When the query executes, choose the sorting order of the returned data.

Create and Use a Query

This example assumes you have a Data Model and some submission records to query against. For this example, a Data Model contains the fields firstName and lastName. Let's create a simple data model query that targets the lastName field. Then, let's use a Plug-In component to execute the query and display it in a View Grid component.

What You Need

To configure a Query, you need the following:

Creating a Query

To learn more about using Schemas and Data Models, view our Introduction to Data Modeling article.

To create a query targeting a field called lastName:

1. Navigate to the Workspace page of an application that has your Data Model and records data.

Creator Administrator access is required to access the Data Model and Query Builder pages.

2. Select Queries to the left of the page.
3. Click + Create Query.
4. In the Name* field, enter Last Name Query.
5. From the Query Type* drop-down, select Model Query. The Data Model* drop-down displays.
6. From the Data Model* drop-down, select the Data Model that contains the field you want to target.
7. Click Add Rule. The Not, And, and Or buttons, and Select Field drop-down display.
8. From the Select Field drop-down, select the lastName field.

The lastName field is specific to this example, but you can choose any field that is available in the Data Model.

9. From the == (Comparison Operators) drop-down, choose operators to test the field against. See the Comparison Operator list for operator descriptions.
10. Change the Constant drop-down to Variable.
11. In the Variable Name  field, enter lastName.
12. Click Save Query. The query Id field displays next to the query Name* field. You'll use the Id to execute the query in a Plug-In component.

Improperly configured queries produce Status Code 500 - Server Error Response in the Express View Express View is how your end-user views you application. Express View also lets you preview your applications to test your configuration and view the styling. This is also the view your end-users will see when interacting with your application. After configuring a module, click Preview in the Module Builder to interact with the module in Express View. DevTools Console The DevTools Console helps you securely store, build, test, and deploy your software..

Create a Last Name Search Dashboard

Build a dashboard to search for last names using the query you built in the Creating a Query steps. To build a dashboard, create a module in the same workspace as the query.

What You Need

To configure this module, you need:

Configure the Text Field Component

This component lets end-users End-users, also known as Express Users, are the individuals accessing an application through Express View. In most cases, end-users are the customers using the product. enter last names to filter by.

1. In the Module Builder, drag and drop a  Text Field component onto the canvas.
2. In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module. field, enter searchLastName.
3. In the Label Text Label Text conveys what the input component is and what information it displays. Enter the purpose of the corresponding component or field. field, enter Enter a Last Name.
4. Click Save & Close.

Configure the Button Component

The Button component triggers the Plug-In component to use the query with the searchLastName  Text Field component's value as its input.

1. Drag and drop a Button component Icon Button component onto your canvas, placing it below the searchLastName  Text Field component.
2. In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module. field, enter btnSearch.
3. In the Label Text Label Text conveys what the input component is and what information it displays. Enter the purpose of the corresponding component or field. field, enter Search.
4. To the left of the component's configuration menu, click Actions.
5. Set the Action Type to Event.
6. In the On Click field, enter plugGet. You'll build the plugGet Plug-In component in the next step.

A static image displaying the Button component's Actions settings.

7. Click Save & Close.

Configure the Plug-In Component

This Plug-In component targets the searchConfigs endpoint using the Post Request Type. You'll refer to the Query Id created during the Creating a Query steps.

1. Drag and drop a Plug-In component onto your canvas, placing it below the btnSearch Button component Icon Button component.
2. In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module. field, enter plugGet.
3. In the Canvas Label Text Canvas Label Text indicates the purpose of the corresponding field or component. For non-input components, the Canvas Label Text isn't end-user facing, and only appears in the . field, enter plugGet.
4. In the Data Source URL field, enter fbu/uapi/searchConfigs/queryId/execute, where queryId is the value displayed on the query's parameters page.
5. Set the Request Type to Post.
6. In the INPUTS table, enter the following:

Property ID

Mapping

searchLastName

lastName

A static image displaying the Plug-In component's Data settings.

7. Navigate to the Plug-In component's Actions panel.
8. In the Post Trigger field, enter dwfSet. You'll build the dwfSet Data Workflow Icon Data Workflow component next.
9. Click Save.

Configure the Data Workflow Component

The Data Workflow component retrieves the data from the plugGet Plug-In component and sends it to a Hidden component so the ViewGrid component can reference it.

1. Drag and drop a Data Workflow Icon Data Workflow onto the canvas, placing it below the plugGet Plug-In component.
2. In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module. field, enter dwfSet.
3. In the Label Text Label Text conveys what the input component is and what information it displays. Enter the purpose of the corresponding component or field. field, enter dwfSet.
Configure the Input Operator

The Input operator brings the data from the plugGet Plug-In component into the Data Workflow component.

1. Drag and drop an Input operator onto your Data Workflow Icon Data Workflow canvas.
2. Configure the Input operator's Info window as follows:

Setting

Value

Component

plugGet

Required

Yes

Source

Default
Configure the Output Operator

The Output operator sends the data to the hiddenRecords iconHidden component to store it.

1. Drag and drop an Output operator onto your Data Workflow Icon Data Workflow canvas.
2. Configure the Output operator's Info window as follows:

Setting

Value

Component

hiddenRecords

action

value
3. Connect the output port (right) of the Input operator to the input port (left) of the Output operator.
4. Click Save.

Your completed Data Workflow looks like the following:

A static image displaying the dwfSet component's settings.

Configure the Hidden Component

The Hidden component stores the data retrieved from the Data Workflow component. The ViewGrid component uses the Hidden component to reference the query data.

1. Drag and drop a iconHidden component onto the canvas, placing it below the dwfSet Data Workflow Icon Data Workflow component.
2. In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module. field, enter hiddenRecords.
3. In the Label Text Label Text conveys what the input component is and what information it displays. Enter the purpose of the corresponding component or field. field, enter hiddenRecords.
4. Click Save & Close.

Configure the ViewGrid Component

The ViewGrid component displays the data stored in the records iconHidden component.

1. Drag and drop a Dropdown Component Icon ViewGrid component onto the canvas, placing it below the hiddenRecords iconHidden component.
2. In the Property ID A Property ID is the unique field ID used by Unqork to track and link components in your module. field, enter vgRecords.
3. In the Label Label Text conveys what the input component is and what information it displays. Enter the purpose of the corresponding component or field. field, enter vgRecords.
4. In the Action field, enter null.
5. In the Inputs table, enter the following:

id

required

hiddenRecords

☐ (unchecked)
6. In the Display table, enter the following:

id

 formula

heading

id

 

Record ID

firstName

 

First Name

lastName

 

Last Name

A static image displaying the View Grid component's Input and Display settings.

7. Click Save.
8. Save your module

Preview your module in Express View Express View is how your end-user views you application. Express View also lets you preview your applications to test your configuration and view the styling. This is also the view your end-users will see when interacting with your application. After configuring a module, click Preview in the Module Builder to interact with the module in Express View.. You'll see the following functionality:

Best Practices

  • Query Builder is most effective when querying Data Models with large sets of data.

Resources