Skip to main content

Populating Scaffolding Select Lists Using SQL Queries

Overview

Populating a Scaffolding List Data macro with items from a database table is most easily done using the DB Options macro. The limitation is that it assumes you want all items from a single table. If that is not the case, there are two solutions:

Option 1: Create a Database View

If you have ownership of the underlying database, you can create a custom view to expose only the data you wish to appear in the list. This will put the logic on the backend and allow you to use the DB Options macro.

Option 2: Dynamically Populate Options

DB Options is just shorthand for generating List Option macros from database results. You can use the example below to set up a report within a List Data macro. The example shows how the query can both use any SQL syntax and also inject dynamic content.

CODE
{list-data:name=user}
{report-list:injected=true}

{sql-reporter:ds=myDB}
select u.user_id, u.first_name || ' ' || u.last_name user_name 
from user u
join group g on u.group_id = g.id
where g.name = '%page:title%'
{sql-reporter}

{report-body:injected=true}
{list-option:value=%row:user_id%|label=%row:user_name%}
{list-option}
{report-body}
{report-list}
{list-data}

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.