Populating the Actions button in a SQL Macro

[Thomas Hardin]

I am using the Bob swift SQL and Run macros to pull data and using https://bobswift.atlassian.net/wiki/spaces/RUN/pages/8486925/How+to+do+SQL+database+operations+on+lists+of+data. It has been a while, but for some reason I am not getting the button to properly display. I am getting "1" in the column.

To try and narrow things down, I removed the encoding logic to see if I could get the button to populate, but no change.

{run:id=project|replace=proj::Project Name|heading = Project Information|titleRun = Search|keepRequestParameters=true}

{sql-query:dataSource=jiradb|output=wiki|}
  select concat((select project_key from project_key where project_key.project_id = jiraissue.project),'-',issuenum) as 'Issue', 
reporter as 'Reporter',
assignee as 'Assignee',
(select issuetype.pname from issuetype where issuetype.id=jiraissue.issuetype) as 'Type', 
(select issuestatus.pname from issuestatus where jiraissue.issuestatus = issuestatus.id) as 'Status', 
CREATED as 'Created On',
UPDATED as 'Updated On',
resolutiondate as 'Resolved On',

  '{run1:id=e' || id 
  ||   '|buttonPadding=4'
  ||   '|keepRequestParameters=true'
  ||   '|titleRun=Go To Issue}'
  || '{run1}' as "Actions"

  from jiraissue where jiraissue.project = (select jiraissue.project from project where project.pname = '\$proj' and project.id = jiraissue.project )
{sql-query}
{run}

My results:

  It has been a while since using the macros, so any help will be greatly appreciated.

Thank you,

[Deleted user]

Hi @Thomas Hardin,

We tried the same which you have provided and we were able to make it work by using CONCAT next to {run1:id =e' and resplaced "||" with ,. Also had corrected the 

project.pname = '$proj'

instead of using project.pname = '\$proj'

Please see the storage format of the sample which we tried.

<ac:structured-macro ac:macro-id="3cecc7d0-26f9-4a58-9058-402c5ee9d387" ac:name="run" ac:schema-version="1">
 <ac:parameter ac:name="heading">Project Information</ac:parameter>
 <ac:parameter ac:name="replace">proj::Project Name</ac:parameter>
 <ac:parameter ac:name="titleRun">Search</ac:parameter>
 <ac:parameter ac:name="id">project</ac:parameter>
 <ac:parameter ac:name="keepRequestParameters">true</ac:parameter>
 <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
 <ac:rich-text-body>
 <ac:structured-macro ac:macro-id="426913f7-fe62-4fbe-9a1e-0f4274957320" ac:name="sql-query" ac:schema-version="1">
 <ac:parameter ac:name="output">wiki</ac:parameter>
 <ac:parameter ac:name="showSql">true</ac:parameter>
 <ac:parameter ac:name="dataSource">JIRA740</ac:parameter>
 <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
 <ac:plain-text-body><![CDATA[ select concat((select project_key from project_key where project_key.project_id = jiraissue.project),'-',issuenum) as 'Issue', 
reporter as 'Reporter',
assignee as 'Assignee',
(select issuetype.pname from issuetype where issuetype.id=jiraissue.issuetype) as 'Type', 
(select issuestatus.pname from issuestatus where jiraissue.issuestatus = issuestatus.id) as 'Status', 
CREATED as 'Created On',
UPDATED as 'Updated On',
resolutiondate as 'Resolved On',
 CONCAT('{run1:id=e' , id 
 , '|buttonPadding=4'
 , '|keepRequestParameters=true'
 , '|titleRun=Go To Issue}'
 , '{run1}') as "Actions"

 from jiraissue where jiraissue.project = (select jiraissue.project from project where project.pname = '$proj' and project.id = jiraissue.project )
]]></ac:plain-text-body>
 </ac:structured-macro>
 </ac:rich-text-body>
</ac:structured-macro>
<p>
 <br/>
</p>
<p>
 <br/>
</p>
<ac:structured-macro ac:macro-id="d596a3a1-19ac-408f-97aa-05807dc1383d" ac:name="wiki-markup" ac:schema-version="1">
 <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
 <ac:plain-text-body><![CDATA[{run:id=e123 
 |buttonPadding=0
 |keepRequestParameters=true
 |anchor=edit
 |titleRun=Edit}
 {extract-link:redirect=true}
 $page_url
 ?run_selection=run&run_edit_first={url-encode} first {url-encode} 
 &run_edit_last={url-encode} last {url-encode}
 &run_edit_extension= extension 
 &run_edit_id= id 
 &run_selection_hideEdit=false
 #$page_anchor_e id
 {extract-link}
 {run}
 {run:id=d id 
 |buttonPadding=0
 |titleRun=Delete}
 {sql:dataSource=testDS}
 delete from extensions where id = id 
 {sql}
 {extract-link:redirect=true}
 $page_url?&run_selection=run
 {extract-link}
 {run}]]></ac:plain-text-body>
</ac:structured-macro>

 Please see the below screenshot which shows the example is working as expected.

Regards,

Vijay Ramamurthy