An advanced technique for using the run macro with dynamically determined options

Release 2.0 of the run macro allows more user entry field types including radio buttons, checkboxes, and select lists. These input types require choices to be defined. This how to demonstrates using SQL to dynamically generate the choice values and choice text. Choice values are used for replacement in the rendered body and choice values are what the user sees in the user interface. In many cases these are the same.

Steps

Performance considerations

Dynamically querying the database each time the page is rendered may not be desirable in some cases. There are some alternatives based on similar techniques.

Alternative - cache

The easiest thing to do is to use the cache macro to cache the results of the choices macro - choose the cache parameters appropriate for your situation. Use the following choiceMacro definition in the script above. In this example, the choice text is only queried the first time used in the day (cache macro default)

def choiceMacro = "{cache} {sql-query:datasource=testDS|table=false} ${choiceSql} {sql-query} {cache}"

Alternative - manual

Alternative - automatic form update

Special characters in choice text

If data from the query contains embedded commas or colons, more care is required in the scripts to provide the appropriate quoting of the values.

Generated form page markup

{run:id=dynamic|autorun=true|replace=report:2010::select::2010:Report 2010:2009:Report 2009:2008:Report 2008:2007:Report 2007:}
{sql-query:datasource=testDS}
select * from reports where year = $report
{sql-query}
{run}