Variables and Widgets Guide

LotusWeb's variables and widgets feature allows you to create dynamic, reusable SQL queries with user-friendly input controls. This guide covers everything you need to know about using variables effectively.

Overview

Variables in LotusWeb use a simple {{variable_name}} syntax that gets automatically detected in your SQL queries. When detected, variables appear as input controls in the query toolbar, making your queries interactive and reusable.

Basic Variable Syntax

Adding Variables to Queries

Simply wrap any variable name in double curly braces:

SELECT * 
FROM orders 
WHERE status = {{order_status}}
  AND created_at >= {{start_date}}
  AND total_amount >= {{minimum_amount}}

Variable Names

Must contain only letters, numbers, and underscores
Case-sensitive ({{Status}} and {{status}} are different variables)
Automatically converted to friendly labels (e.g., min_age becomes "Min Age")

Variable Types

Text Variables

Purpose: String values, text input
Usage: WHERE name = {{customer_name}}
Output: Automatically quoted for SQL safety
Example: Input "John" becomes 'John' in the query

Number Variables

Purpose: Integers and decimal numbers
Usage: WHERE price >= {{min_price}}
Output: Raw number, no quotes
Example: Input "99.99" becomes 99.99 in the query

Date Variables

Purpose: Date values with calendar picker
Usage: WHERE created_at >= {{start_date}}
Output: ISO date format (YYYY-MM-DD)
Widget: Always uses date picker (no input/select option)

Widget Types

Input Widgets

Best for: Free-form text and number entry
Available for: Text and Number variables
User experience: Simple text input field

Best for: Predefined lists of options
Available for: Text and Number variables
Configuration: Custom list or SQL query options

Static Options Format

Simple values (one option per line):

active
pending
completed
cancelled

Value and label pairs (using | separator):

active | Active
pending | Pending  
completed | Completed
cancelled | Cancelled

SQL Query Options

Dynamic dropdown options populated from database queries:

Single column query (value = label):

SELECT status FROM orders GROUP BY status ORDER BY status

Two column query (first = value, second = label):

SELECT user_id, email FROM users WHERE active = true ORDER BY email

Query requirements:

Must return 1 or 2 columns
If 2+ columns, first is used as value, second as label
Results are cached for performance
Queries executed with current data source and search path
Built-in "Test Query" button validates before saving

Date Picker Widgets

Automatic: All date variables use date picker
User experience: Calendar interface
Output: Always ISO date format

List Variables (Multi-Value)

Variables can be configured to accept multiple values, which is useful for SQL IN clauses and similar multi-value patterns.

Enabling List Mode

Open the variable settings panel
Check "Allow multiple values" on any text or number variable
The widget automatically switches to a multi-value input

Widget Behavior

The widget type depends on whether the variable has dropdown options configured:

Tag Input (no dropdown options) — A chip-style input where users type values and press Enter to add them. Each value appears as a tag with an X button to remove it. Supports both text and number types.
Multiselect (with dropdown options) — A multi-select dropdown that lets users pick multiple values from the configured static or SQL-query options.

How Values Are Stored

List variable values are stored as comma-separated strings internally and automatically split into individual values at query execution time. For example, entering tags active, pending, and completed stores "active,pending,completed" and expands to three separate parameter values.

Example: Filtering with IN Clauses

SELECT *
FROM orders
WHERE status IN ({{statuses}})
  AND region IN ({{regions}})

Variable Configuration:

statuses: Text, Dropdown with static options (active, pending, completed), Allow multiple values enabled
regions: Text, Input, Allow multiple values enabled — users type region codes as free-form tags

Default Values for List Variables

You can set a comma-separated default value for list variables. For example, setting the default to active,pending will pre-populate the tag input with two chips or pre-select two options in the multiselect dropdown.

Variable Settings Panel

Accessing Settings

Add variables to your query using {{variable_name}} syntax
Variables automatically appear in the toolbar
Click the "Variable settings" {x} icon in the toolbar
Settings panel opens on the right side

The settings panel has two tabs:

Help Tab - Shows when no variables are configured, contains syntax examples and usage information
Settings Tab - Shows when variables exist, allows configuration of variable types, widgets, labels and defaults

Variable Persistence

When you save a query, all variable configurations are saved with it:

Variable types (Text, Number, Date)
Widget types (Input, Dropdown)
Labels and static options
Default values

What is NOT saved: The actual values users enter in the widgets. Each time the query loads, widgets start empty unless you set default values.

Pro tip: Set default values in the variable settings if you want the query to auto-run with meaningful values when loaded.

Advanced Usage Examples

Multi-Filter Dashboard Query

SELECT 
  DATE(created_at) as date,
  status,
  COUNT(*) as order_count,
  SUM(total_amount) as total_revenue
FROM orders 
WHERE status = {{order_status}}
  AND created_at BETWEEN {{start_date}} AND {{end_date}}
  AND total_amount >= {{min_amount}}
GROUP BY DATE(created_at), status
ORDER BY date DESC

Variable Configuration:

order_status: Text, Dropdown with static options: "active|Active", "pending|Pending", "completed|Completed"
start_date: Date (automatic date picker)
end_date: Date (automatic date picker)
min_amount: Number, Input with default value "0"

User Analysis Query

SELECT 
  u.email,
  u.created_at,
  COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= {{registration_date}}
  AND ({{user_email}} IS NULL OR u.email LIKE '%' || {{user_email}} || '%')
HAVING COUNT(o.id) >= {{min_orders}}
ORDER BY order_count DESC

Variable Configuration:

registration_date: Date, default value "2024-01-01"
user_email: Text, Input with label "Search Email", default value ""
min_orders: Number, Input with default value "1"

Dynamic Category Analysis Query

SELECT 
  c.name as category_name,
  COUNT(p.id) as product_count,
  AVG(p.price) as avg_price
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
WHERE c.id = {{category_id}}
  AND p.active = {{product_status}}
GROUP BY c.id, c.name
ORDER BY product_count DESC

Variable Configuration:

category_id: Number, Dropdown with SQL query: SELECT id, name FROM categories WHERE active = true ORDER BY name
product_status: Text, Dropdown with static options: "true|Active", "false|Inactive"

Best Practices

Naming Conventions

Use descriptive names: {{start_date}} not {{d1}}
Use underscores for multi-word variables: {{min_amount}} not {{minamount}}
Be consistent across related queries

Default Values

Always provide sensible defaults for a better user experience
Use common filter values (e.g., "last 30 days" for dates)
Consider empty/null defaults for optional filters
Set defaults if you want queries to auto-run - widgets start empty unless defaults are configured

Widget Selection

Use Static Dropdowns for:
- Status fields with known values
- Boolean-like choices (Active/Inactive)
- Small, fixed lists that rarely change
Use SQL Query Dropdowns for:
- User lists, category selections
- Dynamic lookups from database tables
- Lists that change frequently
Use Input fields for:
- Free-form text search
- Numeric thresholds
- Custom values not in predefined lists
Enable "Allow multiple values" for:
- SQL IN clause filters (e.g., multiple statuses, regions, or IDs)
- Any parameter where users need to select more than one value

Query Design

Design queries to handle empty/null variables gracefully
Use {{variable}} IS NULL OR patterns for optional filters
Test queries with different variable combinations

Security Features

Parameterized Queries

All variables are sent as prepared statement parameters
No string interpolation - prevents SQL injection attacks
Values are properly escaped based on variable type

Type Safety

Text variables are automatically quoted
Number variables are validated as numeric
Date variables use ISO format validation

Safe Defaults

Empty variables default to NULL in SQL
No direct database string concatenation
All queries go through Lotus's security layer

Troubleshooting

Variables Not Appearing

Check syntax: Must be exactly {{variable_name}}
Check name: Only letters, numbers, underscores allowed
Refresh editor: Sometimes requires re-typing the variable

Static options format: One option per line
Custom options: Either value (doubles as value/label) or value | label syntax
Empty lines: Remove empty lines between options
SQL queries: Use "Test Query" button to validate before saving
Query columns: Must return 1 or 2 columns (value, label)

Date Variables Issues

Widget type: Date variables always use date picker (no input/dropdown)
Format: Outputs ISO date format (YYYY-MM-DD)
Timezone: Uses browser's local timezone for date picker

Set a variable to use a Dropdown widget in Variable Settings
Click the "Configure options" button next to the dropdown widget selection
Choose between "Custom list" or "From SQL" in the configuration modal

Custom list: Text area for entering static options (one per line)
From SQL: Text area for SQL queries with syntax highlighting
Test Query: Validate SQL queries and preview first 3 results before saving
Live preview: Shows how options will appear in the dropdown
Error handling: Clear error messages for invalid queries or syntax

← Previous Page Dashboards

Variables and Widgets Guide

Overview

Basic Variable Syntax

Adding Variables to Queries

Variable Names

Variable Types

Text Variables

Number Variables

Date Variables

Widget Types

Input Widgets

Dropdown Widgets

Static Options Format

SQL Query Options

Date Picker Widgets

List Variables (Multi-Value)

Enabling List Mode

Widget Behavior

How Values Are Stored

Example: Filtering with IN Clauses

Default Values for List Variables

Variable Settings Panel

Accessing Settings

Variable Persistence

Advanced Usage Examples

Multi-Filter Dashboard Query

User Analysis Query

Dynamic Category Analysis Query

Best Practices

Naming Conventions

Default Values

Widget Selection

Query Design

Security Features

Parameterized Queries

Type Safety

Safe Defaults

Troubleshooting

Variables Not Appearing

Dropdown Options Not Working

Date Variables Issues

Configuration Modal

Accessing Dropdown Options Configuration

Modal Features