Create page from template using Run and SQL macros

Hi, first up I'll qualify my ignorance by saying that I'm not a developer but, hey, at least I'm trying!

I've been tinkering with our newly acquired Run and SQL macros with the aim of achieving the following page-creation scenario, similar to the example provided on this Plugin wiki page:

1) Retrieve JIRA issue Summary field to screen via SQL plugin, for user verification

  • Run macro passes the JIRA reference supplied by the user into the query:
{run:id=ref|replace=ref:TC-:Reference:text-required|titleRun=Retrieve}
|*Title*|{sql-query:dataSource=JIRADB|output=wiki|table=false}
select Summary from jiraissue where pkey='$ref'
{sql-query}

2) Display Summary on screen and offer to create page from template

  • Run1 macro is nested within Run macro runs a CLI command like:
{cli:profile=confluence|showcommand=true|panel=true}-a addPage --space CONF --parent @home --title $... --file templates\\test.txt --noConvert --labels test1,test2 --replace{cli}
Page created: [$...]

My questions are:

  1. The value initially stored in $ref doesn't seem available to the second Run1 macro because the page is refreshed. Can I keep it persistent?
  2. How could I turn the value returned by SQL (the JIRA Summary) on the first run (Run) into a variable passed to the CLI title parameter on the second run (Run1)?
  3. Do I need to investigate doing this using a Groovy script instead?

My next questions will probably be how to then crystallize various returned SQL values within a template sourced from a file...

All assistance appreciated.

3 answers

1 accepted

  1. You need one of the keep parameters - see documentation
  2. There are 2 options.
    1. Use replace and render to put it into a variable that can be used in the second macro
    2. Go to a script using techniques similar to How to generate choice values using SQL
  3. Perhaps - scripts allow you to do lots of things with a bit more complexity and control (security) needs. Second choice once the easier options don't meet requirements or are too cumbersome.

Thanks for the pointers, Bob

Hi Adam:

I am trying to create a similar solution, were you able to finally complete this? If so would you be willing to share the results with me?

Sincerely,

Craig T. Hylwa

Hi Craig,

Yes, I came up with something that is not exactly the most elegant solution but seems to work. It's quite complex, because it prompts the user to enter and review details about the issue number before they proceed. It also retains JIRA as the source of truth - there is a non-editable section of the release note (defined using a User Macro) that updates according to the details in JIRA.

I've attached the source of the page and the User Macro. Bear in mind that coding is not my gig, so I'm sure it could be improved.

Basically this all uses a combination of the Run, SQL, CLI and replace-and-render macros, a Confluence User Macro, and an XHTML file that contains the template.

The process is:

  • present user with an input box for the JIRA issue they would like to create a templated page for
  • after user input, present a summary of the JIRA issue information for review
  • after user proceeds, use the previously input issue number in a new routine
  • the new routine replaces placeholders in the template with content from JIRA and the User Macro, creating a page in a defined space
  • a link to the newly created page is then presented so the user can proceed to edit

I found the easiest way to create the XHTML template was to design a page in Confluence and then save the source to the file, replacing certain parts with a placeholder.

What I have learned from this is that, while it's all possible to do, I should learn how to write a script!

(pagesource.txt) (usermacro.txt)

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Posted Jul 10, 2018 in Confluence

We want to see the templates you've created in Confluence!

Hi Community, Jessica here from the Confluence Product Marketing team!  July’s community challenge is all about sharing pictures  — and as an extension of our first post on what ...

602 views 20 11
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you