Queries

Visual DB uses queries as the foundation for data entry forms, sheets, and reports. There are two types of queries: those built using the Query Builder and custom queries. Forms and sheets must use queries built with the Query Builder, while reports can use either type. Custom queries can also be used to populate dropdown lists with available values.

Query Builder

Queries built using Query Builder have a main table and optional related tables. A related table is one that can be joined to either the main table or another related table. In forms and sheets, only the main table is updatable - fields from related tables can be displayed but not modified. If you need to update records in multiple tables simultaneously, see Master Detail.

Note

Query Builder automatically detects relationships between tables using foreign key relationships from your database. If these relationships aren’t already set up, you can add them using the “Add Relationship” button in Visual DB’s Database tab.

Using Query Builder

Start by clicking the ‘Add main table’ button and selecting a table from the list. You’ll see all available columns in the selected table. Choose which columns you want to display in your form, sheet, or report. Select columns carefully, as including unnecessary ones can impact performance.

Review and edit column aliases as needed. Forms and sheets use these aliases to generate field prompts and column names. Additionally, when using AI to automatically layout forms, it relies on these aliases to understand each column’s purpose.

After adding the main table, you can add related tables if needed. This allows you to display information from related tables directly in your forms, sheets, or reports, making them more useful and informative. To add a related table:

  1. Select any table already in the query

  2. Click the ‘Add related table’ button

  3. Choose from the list of tables that can be joined to your selected table

How to Filter Records

Your database may contain millions of records, but you often need only a subset of them in your browser for viewing or updating. To filter which records are fetched, click the ‘Add new condition’ link. Visual DB lets you build complex conditions by grouping them and specifying whether ‘all’ conditions must be satisfied or if satisfying ‘any’ condition is sufficient. You can also nest groups of conditions for more complex filtering logic.

How to Add Query Parameters

Some queries need user input to determine which records to fetch. For example, a query showing patient medications might need to know which patient to display medications for, or a query showing orders might need a date range to filter by. Query parameters let users provide these values when the query runs. To create a parameter, add a condition and click the triangle next to it to select ‘Prompt user for value’. Visual DB will then prompt for this value before running the query.

Custom Queries

While Query Builder handles most common scenarios, sometimes you need more complex SQL logic than what Query Builder can create. Custom queries let you write SQL directly, giving you full control over how data is retrieved and calculated. For example, you might need to use subqueries, complex aggregations, or conditional logic that Query Builder doesn’t support. Custom queries are particularly useful for reports where you need specialized calculations or data transformations. Note that forms and sheets must use queries built with Query Builder, not custom queries.

Translate English to SQL

In the Custom Query dialog’s English tab, you can write queries in plain English - Visual DB will use AI to translate your questions into SQL. For accurate translations, ensure your table and column names clearly reflect their purpose, as AI relies on these names to understand your database structure.

Configure Prompts

Use the Configure Prompts dialog to customize how query parameters are presented to users. In this dialog you can:

  • Rearrange the order of prompts

  • Set input field widths

  • Specify default values

  • Configure dropdown lists of available values

Troubleshooting

In PostgreSQL when you select a table you may get this error: “This table cannot be used because it has no primary keys”.

If the table does have primary keys, but Visual DB complains that it doesn’t, that’s because of a permission issue. You can fix the issue by granting the appropriate permissions to the user you are connecting as. Here’s an example:

GRANT SELECT, REFERENCES ON ALL TABLES IN SCHEMA public TO john

Replace public with the appropriate schema and john with the appropriate user id. Without the REFERENCES privilege Visual DB is not able to detect constraints such as primary keys.