SQL Lite in Forms¶

How SQLite is used in Versaa forms — device-local reference databases, how to build queries, and the builder+parser pattern.

What SQL Lite Is¶

Versaa forms can use SQLite databases (.db3 files) stored on the device as local reference data. This allows forms to: - Perform fast lookups without hitting the QL server on every interaction - Load complex classification data (ratings, CRM class levels) from a local source - Reduce strain on the SQL Server backend

SQLite databases are distributed to devices as Platform Files in Management Studio (Workflows → Variables → Platform Files → Reference Data). Developers download the .db3 file locally, edit it with DB Browser for SQLite (https://sqlitebrowser.org/), and re-upload.

Common Use Case — Awaabs Law Form¶

The Awaabs Law form moved classification data from QL to a local SQLite database (AwaabsOptions.db3). The database contains:

Tables¶

AnswerOptionList — Options with ratings and CRM classifications: | Column | Type | Purpose | |---|---|---| | aId | Integer | Primary key | | aOptionGroup | Integer | Groups related options | | qOptionNo | Integer | Display order | | qOptionText | Text | The display label shown in form | | qRating | Integer | Numeric risk rating | | qCRM1_Class_Level_1 through qCRM1_Class_Level_5 | Text | CRM classification values |

Questions — Question definitions: | Column | Purpose | |---|---| | qNo | Question identifier | | qQuestion | Question text | | qOverrideRating | Default override rating | | qAnswerOptionListGroup | Links to AnswerOptionList.aOptionGroup |

QuestionAnswers — Selected answer records: | Column | Purpose | |---|---| | qNo | Question number | | qAnswer | The selected answer text | | qRating | Rating of selected answer | | qOverrideRating | Override rating if set |

Critical Rule — Column Names Must Be `[name]` and `[value]`¶

SQLite query results must output exactly two columns named name and value (lowercase, exact). The Versaa form engine reads these two columns only. Any other column naming causes silent failures.

-- CORRECT
SELECT qOptionText AS name, qRating AS value FROM AnswerOptionList

-- WRONG — will not work
SELECT qOptionText AS label, qRating AS rating FROM AnswerOptionList

Builder + Parser Pattern¶

A powerful pattern for loading complex multi-value data into a form from a single SQLite query:

Step 1 — Build a pipe-delimited value in the SQL query:

SELECT
  qOptionText AS name,
  CAST(qRating AS TEXT) || '|' ||
  IFNULL(qCRM1_Class_Level_1, '') || ',' ||
  IFNULL(qCRM1_Class_Level_2, '') || ',' ||
  IFNULL(qCRM1_Class_Level_3, '') || ',' ||
  IFNULL(qCRM1_Class_Level_4, '') || ',' ||
  IFNULL(qCRM1_Class_Level_5, '') AS value
FROM AnswerOptionList
WHERE aOptionGroup = 3
ORDER BY qRating

Step 2 — Parse in form formulas using split():

// Get the rating (first pipe segment)
($formItem.z_SelectedValue.split("|")[0])

// Get CRM Class Level 1 (second pipe segment, then first comma segment)
($formItem.z_SelectedValue.split("|")[1].split(",")[0])

// Get CRM Class Level 2
($formItem.z_SelectedValue.split("|")[1].split(",")[1])

This pattern lets a single form item selection automatically populate rating and all five CRM classification levels.

Default Value Behaviour¶

When a SQLite lookup returns multiple rows, setting Filter = 1 returns only the first matching row. The value column is used as the pre-selected default (not name). Use this to pre-populate fields.

Stacked Queries¶

Queries can be chained — the output of one query becomes the filter input for the next. This enables complex lookups without additional back-end tables.

Getting the Tool¶

DB Browser for SQLite: https://sqlitebrowser.org/

Use this to: - Inspect an existing .db3 schema - Write and test queries before putting them into form providers - Add/edit data in the database before uploading to Management Studio