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

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.