Get Data from Database

This article explains the features of the "Get Data from Database" block, which allows you to fetch data from an external or organizational database and use it within your Clappia app.

Use Cases

1. Field Inspection Tracker: Retrieve inspection details for pre-filled forms based on asset IDs.
2. Employee Management: Populate employee information (e.g., email, department) by querying based on Employee ID.
3. Customer Feedback: Pull customer-specific data such as order details before collecting feedback.
4. Inventory Management: Retrieve stock details dynamically for specific products during submission.

Adding the Get Data from Database Block

The "Get Data from Database" block works within the Design App phase and connects Clappia to an external database. Once configured, it fetches data from the database dynamically when the app is used.

Editing the Block

Click on the "Get Data from Database" block and start editing on the right panel.

Basic Configuration

Label

The label is the name of the block that the end user sees in the app.
Example: "Fetch Employee Details" or "Get Stock Info".

Description

The description is the additional information provided to the end user, displayed below the input area. You can also leave this blank if not necessary.

Select Database Type

Choose the type of database you want to connect to from the dropdown.
Supported options: MySQL, PostgreSQL, AzureSql

Database Host Name

Enter the hostname or IP address of your database server. This is typically provided by your IT team or database administrator. For example:

• Hostname: database.example.com
• IP Address: 192.168.1.1
  

Port

Specify the port number for your database connection.
Example: 3306 (MySQL) or 5432 (PostgreSQL).

Username and Password

Provide the login credentials required to access your database. These are assigned by your database administrator. Ensure the account has read permissions for the required tables.

Database Name

Specify the name of the database where your data is stored. The name must match exactly as it appears in your database. For example, if your database is called employee_data, you must enter employee_data.

Authenticate

Click "Authenticate" to test the connection. If successful, additional fields will appear for query configuration.

Query Configuration

Once authentication is successful, you can define the query to fetch data.

Example of a database table:

Query

Write an SQL query to fetch data from the database. Use conditions to filter results dynamically.
Example:
SELECT * FROM employee_table;

Ensure that your query retrieves only the required data.

Output Fields

Add the column names from your database query as Output Fields. These must match the exact column names in the database.

• If only certain columns are mentioned in the Output Fields, only those specific values will be fetched and displayed when the "Get Data" button is pressed in App Home.
• Example: If the query retrieves multiple columns (age, email, department), but only age and email are listed as Output Fields, the app will display only these two values for the user to select from.

Using Output Fields in Other Blocks

‍
Once Output Fields are defined, corresponding output variables are automatically generated. These variables can be used to display specific values from the database in other fields of the app.

Steps to Use Output Variables

1. Add a Calculations & Logic block to your app.
2. In the right panel of the Calculations & Logic block, go to the Formula section.
3. Type @ followed by the field name of the "Get Data from Database" block. A dropdown will appear listing all the output variables for the defined Output Fields.
4. Select the desired output variable and save the block configuration.

Example

• If the Output Fields are age and email, their corresponding variables could be {field_name_age} and {field_name_email}.
• In the Calculations & Logic block, you can configure the formula as @field_name_age.
• In App Home, when the user selects a row from the dropdown of the "Get Data from Database" button, the Calculations & Logic field will dynamically display the value from the age column for that selected row.

This allows you to display specific data fetched from the database (e.g., age, email) in separate fields within the app.

Additional Basic Configurations

No Records Message

This field allows you to set a custom message that will appear when no records match the query you have specified.

Example: If the query does not return any data, you can display a message like "No matching records found."

Leave this field blank if you do not wish to display a message in such cases.

Required

Toggle this option to make the Get Data from Database block mandatory for app submission.

When enabled, users must interact with the block (fetch data and make a selection) before submitting the form.

If this option is disabled, the block becomes optional, and users can proceed without fetching or selecting data.

Advanced Options

Advanced Label

The Advanced Label option allows you to change the label of a field dynamically based on a condition you define. Instead of always showing the same fixed label under the ‘Basic’ tab, the field can display different labels depending on requirements of the form. Use spreadsheet-like functions such as IF, AND, OR, etc. and make use of other field variables to set your conditions. Type @ and select the field.

This is useful when the meaning of a field changes based on context, business logic, or user choices.

For example:
If you have a dropdown called Type with options “Employee Info” and “Project Info”.
The label updates based on the query chosen.

So:
– If Employee Info, show “Load Employee Info”
– If Project Info, show “Load Project Info”

Formula:

IF({type} = "Employee Info", "Load Employee Info", "Load Project Info")

This allows the same field to adapt its displayed purpose without needing multiple separate fields.

Advanced Description

The Advanced Description option works exactly like Advanced Label, but it changes the description text instead. This is useful when guidance or instructions for a field need to change depending on earlier answers.

For example, using the same scenario from Advanced Label:
If you have a dropdown called Type with options like “Employee Info” and “Project Info”, you may want the description of your Database Lookup field to guide the user differently depending on what they selected.

So:
– If the user selects Employee Info, the description could say: “Load employee details from the database.”
– If the user selects Project Info, the description could say: “Load project details from the database.”

Formula:

IF({type} = "Employee Info", "Load employee details from the database.",  "Load project details from the database.")

This helps users understand what is required from them without showing unnecessarily long or irrelevant instructions.

‍

Additional Examples (Apply to Both Advanced Label and Advanced Description)

1. Showing nothing until a selection is made

For example, if you have a dropdown field called Visit Category with options “Routine” and “Urgent”, you may want the label or description of a field to remain blank until the user first selects a category.

Once a selection is made:

• If the user chooses Routine, the field will display “Routine”.
• If the user chooses Urgent, the field will display “Urgent”.

Formula (can be used in either Advanced Label or Advanced Description):

{visit_category}The label/description stays empty until the dropdown has a selected value.
After the user picks an option, the selected text (Routine or Urgent) becomes the label or description.

2. Changing label/description based on language selection

For example, if your form includes a dropdown field called Select Language with options English, Spanish, and French, you can show the label or description in the selected language.

So:

• If the user selects English, show English text.
• If the user selects Spanish, show Spanish text.
• If the user selects French, show French text.

Formula (can be used in either Advanced Label or Advanced Description):

IF({select_language} = "English", "Enter details", IF({select_language} = "Spanish", "Ingrese detalles", "Entrez les détails"))The formula returns the text for the selected language.
Only one label/description is shown at a time, depending on what the user picks in the Select Language dropdown.

‍

Important Notes (applies to both Advanced Label and Advanced Description)

1. Variables do not change
When a field is created, its variable name is derived from the label you set in the Basic tab. That variable name is what you must use in formulas, workflows, and other logic. The visible label or description shown by Advanced Label / Advanced Description does not change the variable name.

2. Submissions tab: table view vs right panel
In the Submissions area, the table view always displays the labels from the Basic tab. When you open an individual submission, the right panel shows the labels and descriptions as they appear in the form (i.e., the Advanced Label and Advanced Description applied for that submission). This keeps the submission list consistent while letting reviewers see the context-aware labels and descriptions when viewing a record.

3. Bulk Edit shows Basic tab labels and descriptions
When you need to Bulk Edit submissions, the spreadsheet you download shows the labels and descriptions from the Basic tab only. Advanced Label and Advanced Description are not applied in Bulk Edit, so keep that in mind when preparing bulk updates.

4. Some fields cannot be used inside Advanced Label/Description formulas
Certain block types do not expose a variable that can be referenced in Advanced Label or Advanced Description. If a block does not expose a variable, you cannot use it inside the formula.

Geo Address

GPS Location

PaymentGateway

Audio

Live Tracking

Signature

Code Scanner

NFC Reader

Get Data from RestApi

Get Data from Other Apps

Get Data from Google Sheets

Get Data from Database

AI Block

Text, HTML & Embedding

Attached Files

Image Viewer

Video Viewer

PFD Viewer

Code block

Progress Bar

Action Button

‍

Display this field if

Use this option to show or hide the field under specific conditions. It accepts the standard Clappia formulae, similar to conditional sections or in the ‘Calculations & Logic’ block.

1. Type @ to get a list of all the fields you have added in the app and select the relevant field or type @ followed by the field name.
2. Use these field variables to write spreadsheet-like formulae for conditional display of the field in the app.

Retain value if hidden

This option appears once a condition is set in the Display this field if option. Enable this setting if you want the field's value to be retained even when the field is hidden. This is useful for preserving user input in cases where the field may temporarily disappear based on conditions.

Example: If the field is hidden based on user selection but you still need to keep the entered value for future reference or calculations, enable this option to ensure the data is retained.

Allow value to be changed after initial submission

This option is enabled by default. When active, end users can edit the value entered in this field after creating a submission. Disable this option if you want the initial value to remain uneditable once submitted.

Block Width

Desktop View: Set the width of the PDF Viewer block in the desktop view. Options are 100%, 75%, 50%, or 25%.

Mobile View: Set the width of the PDF Viewer block in the mobile view. Options are 100% or 50%.

Using the Block in App Home

When the app goes live, users can interact with the "Get Data from Database" block.

1. Click the block (e.g., "Get Data"). The app will execute the query and fetch data from the database.
2. Users can select from the list of data from the database table. Only those columns defined in the output field will appear as options to select from.
3. Fetched data will be displayed dynamically in the specified output fields.