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.


Links


Referenced Add-ons


Steps


  1. Design a page with the run macro as normal with a few dummy choices
  2. Create a choices sql select statement that produces a set of rows where each row is a text field of the form: value:choice: - value is the replacement value that is needed and choice is the choice text that the user will see
  3. Setup the sql-query macro on a page to run the choices sql
    • Use table=false
    • Verify the result of the sql-query macro is a colon separated string of values and choice text
  4. Copy the groovy example and replace the run and sql portions with your definitions
    • Note that the $ in front of replacement variables used must be escaped in groovy by the **
{groovy:output=wiki}
import com.atlassian.renderer.v2.RenderMode

def renderMode = RenderMode.suppress(RenderMode.F_FIRST_PARA)

def choiceSql = "select distinct(year) || ':' || 'Report ' || year || ':' as choice from reports order by choice desc"

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

def choices = subRenderer.render(choiceMacro, context, renderMode)

def runMacro = """
{run:id=dynamic|autorun=true|replace=report:2010::select::${choices}}
{sql-query:datasource=testDS}
select * from reports where year = \$report
{sql-query}
{run}
"""

out.println runMacro
{groovy}




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


If the choices only need to be updated occassionally, a non-dynamic approach might be best. Use the run and sql macro similar to above to run the choices sql and then manually cut and paste into the page with the results. This alternative does not need the groovy macro.

{run:titleRun=Generate choices}
{sql-query:datasource=testDS|table=false} select distinct(year) || ':' || 'Report ' || year || ':' as choice from reports order by choice desc {sql-query}
{run}




Alternative - automatic form update


If the choices need to be more dynamic or the page is heavily used and you are concerned about dynamically using groovy on the page, then this is a better alternative. However, it does require a few more parts. The basic idea is to use the groovy code to generate a form page. The form page can be used directly or it can be included in another page. So, up to 3 pages are involved:

  • form page that will be automatically updated
  • generate page that administrators use to generate the form page
  • user access page that includes the form page

Steps

  1. Follow steps #1 - #5 as before
  2. Create a form page with a title of your choosing
  3. Create a generate page
    • Intended only for administrators or automation
    • Modify the following example script with your specifics including form page title

      {run2:titleRun=Update form page}
      
      {groovy}
      import com.atlassian.renderer.v2.RenderMode
      
      def renderMode = RenderMode.suppress(RenderMode.F_FIRST_PARA)
      
      def choiceSql = "select distinct(year) || ':' || 'Report ' || year || ':' as choice from reports order by choice desc"
      
      def choiceMacro = "{sql-query:datasource=testDS|table=false} ${choiceSql} {sql-query}"
      
      def choices = subRenderer.render(choiceMacro, context, renderMode)
      
      def runMacro = """
      {run:id=dynamic|autorun=true|replace=report:2010::select::${choices}}
      {sql-query:datasource=testDS}
      select * from reports where year = \$report
      {sql-query}
      {run}
      """
      
      def title = context.getPageTitle() + " - form"
      def spaceKey = context.getSpaceKey()
      
      def page = pageManager.getPage(spaceKey, title)
      
      if (page != null) {
      page.setContent(runMacro)
      println "Form page '${title}' updated."
      } else {
      println "Page '${title}' not found."
      }
      
      {groovy}
      
      {run2}
      


  4. Use the generate form button to update the content of the form page
  5. Optionally, add autoRun=true so automation can render the page on a schedule to keep the form current automatically




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.


{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}