Create a custom table based on CQL results of pages requiring updates

[Tom Ireland]

I'm trying to create a custom table based on results from CQL where a row is created for each page identified as requiring an update e.g. over 12 months since last modified.

I'm able to use a branch and CQL to identify the pages, however I cannot seem to create a table with a row for each page listed in the results.

I managed to get it partially working by using 'Lookup Pages', however when formatting `{{#lookupPages}}` syntax in a table row, all entries are added to a single row.

I'm finding it unnecessarily complicated and confusing to create something that is seemingly simple.

Any pointers?

Thanks.

Tom

[Aron Gombas _Midori_]

Instead of creating and re-creating a page with the most current page list, I think you should use a macro which runs the CQL search right at the time of opening the page that contains the list and displays the most current search results in a tabular format.

We described two macros that are able to do this. The result would be part of any Confluence page, for example:

Or, possibly more elegant, you could use a Confluence dashboard like this:

(Discl. these are offered by a paid and supported developed by our team. Free for 10 users!)

[Thiago P _Atlassian Support_]

Hey there Tom,

The Lookup Pages action is indeed ideal to build a list of stale pages.

Adding the results to a table on a new page is certainly tricky due to the limitations you noticed as the {{lookupPages}} Smart Value is basically a string list of results.

Sending an email with the results may be a viable option as that allows us some HTML formatting.

Here's an example:

List of pages that need updating:
<table style="border-collapse: collapse; width: auto; height: auto; text-align: center;">
<tr>
<th style="border: 1px solid black; padding: 4px;">Page Title</th>
<th style="border: 1px solid black; padding: 4px;">Last Updated on</th>
<th style="border: 1px solid black; padding: 4px;">Page Author</th>
<th style="border: 1px solid black; padding: 4px;">Author email</th>
<th style="border: 1px solid black; padding: 4px;">Link</th>
</tr>
{{#lookupPages}}
<tr>
<td style="border: 1px solid black; padding: 4px;">{{title}}</td>
<td style="border: 1px solid black; padding: 4px;">{{dateLastUpdated.shortDate}}</td>
<td style="border: 1px solid black; padding: 4px;">{{author.publicName}}</td>
<td style="border: 1px solid black; padding: 4px;">{{author.emailAddress}}</td>
<td style="border: 1px solid black; padding: 4px;"><a href="{{url}}">Link</a></td>
</tr>
{{/lookupPages}}
</table>

Give this a try and let us know how it goes!

[Richard Brown]

Hi, I came across this thread while looking for help on a related topic, where I am using the smart value {{#cqlResults}} (or {{#cqlResults}}{{title}}, to be specific). I'm not sure if I correctly understand the function of the '#' symbol in relation to smart value lists. Is this documented or explained in more detail anywhere? I think I already picked up something from your suggestion @Thiago P _Atlassian Support_ - that the {{#lookupPages}} needs to be exited with a {{/lookupPages}}. This seems to have fixed my problem, in fact :-) so thanks for that :-D

[Thiago P _Atlassian Support_]

Hey there @Richard Brown ,

Great question - you can find a brief explanation in Using smart values with sections and lists .

Long story short: instead of using the same Smart Value for multiple fields, you can use opening and closing parameters to increase readability.

Take the Pages Smart Value for instance:

• {{page.url}}

• {{page.id}}

• {{page.title}}

• {{page.parent.url}}

• {{page.parent.id}}

• {{page.parent.title}}

• {{page.author.fullName}}

• {{page.author.publicName}}

• {{page.author.emailAddress}}

Instead of repeating 'pages' multiple times, we can do this:

• {{#page}}  <-- opening parameter
• {{url}}
• {{id}}
• {{title}}
• {{parent.url}}
• {{parent.id}}
• {{parent.title}}
• {{author.fullName}}
• {{author.publicName}}
• {{author.emailAddress}}
• {{/page}}  <-- closing parameter

Regarding your specific case, you may not need that if you only have one Smart Value, you may simply use {{cqlResults.title}} and ignore the "opening" and "closing" values - make sure to test this approach though.

[Richard Brown]

Thanks for your swift response @Thiago P _Atlassian Support_ The page you linked to was just the kind of thing I was after, but had not found on my own. My rule is now working according to my intentions. I had missed out the {{/cqlResults}} to close the expression. In my case, this was somewhat tricky to debug as I had used this smart value to populate the 'Description' field in a 'Create work item in Jira' component. The result was that the rule partly failed with [Some errors] and the jira work item was not created (though the following step to send an email ran ok). The error message in the audit log was '

Error retrieving the work type fields for project/typeId -
10101/10004' which lead me to believe the issue was something other than a smart value syntax error. Next time, I guess I'll know to interpret this message as a general indication that something is failing in the component. Thanks again for putting me on the right track!