This article demonstrates the use case to generate choice values from a table for users in a form using Run macro and display the results from another table as the output.
Use Case:
The SQL database has two tables called "Zone" and "Rules". Users are allowed to select a "zone_id" from the zone table and then use the "zone_id" as a foreign key to select the rules associated with that "zone_id". In the Zone table, "zone_id" is the primary key and "Demog" is the foreign key in the Rules table. Please see below the structure of Zones and Rules tables.
Zone
Zone_ID | Zone |
---|---|
200 | Stanislaus |
201 | Brower |
202 | Alton |
Instructions
This example makes use of the below-given apps.
The below groovy code provides users with a form that has a select list dropdown to select the user choice value of zone id. This zone id is then passed to the rules table which is displayed upon hitting the Run Button.
{groovy:output=wiki} import com.atlassian.renderer.v2.RenderMode def choiceSql = "select distinct concat(zone_id, ':', zone_id , ':') from zone" def choiceMacro = "{sql-query:datasource=yoga-sql|table=false} ${choiceSql} {sql-query}" def choices = subRenderer.render(choiceMacro, context, RenderMode.suppress(RenderMode.F_FIRST_PARA)) def runMacro = """ {run:id=dynamic|autorun=true|replace=report:2010::select::${choices.replaceAll(" ","::")}} {sql-query:datasource=yoga-sql} select * from rules where demog = \$report {sql-query} {run} """ out.println runMacro {groovy}
The below groovy code adds row formatting on the SQL output using CSS in SQL for Confluence:
{groovy:output=wiki} import com.atlassian.renderer.v2.RenderMode def choiceSql = "select distinct concat( zone_id,':', zone_id,':') from zone ;" def choiceMacro = "{sql-query:datasource=yoga-sql|table=false} ${choiceSql} {sql-query}" def choices = subRenderer.render(choiceMacro, context, RenderMode.suppress(RenderMode.F_FIRST_PARA)) def runMacro = """ {run:id=dynamic|autorun=true|replace=report:2010::select::${choices.replaceAll(" ","::")} } {sql-query:datasource=yoga-sql|rowStyles= ,background-color:lightblue,} select * from rules where demog = '\$report'; {sql-query} {run} """ out.println runMacro {groovy}
Modify the SQL data source, table names, and columns as per the instance.