---
title: "How to generate choice values using SQL"
canonical: "https://support.appfire.com/space/SUPPORT/89136592/How%20to%20generate%20choice%20values%20using%20SQL"
format: markdown
---
> Macro (excerpt)
> 
> 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.

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

# Steps

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

# 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

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

# Alternative - automatic form update

> Macro (section)
> 
> > Macro (legacy-content)
> 
> > Macro (legacy-content)

> ℹ️ **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}

```