How do I disaggregate a list of @mention users in a table field?

[Rey Muradaz]

I'm building a rollup report that will collect Action Items from tables in a number of child pages into a single table at the parent.  One of the columns is the Owners field.  We use the @mention functionality to add the Conf users to that field so they will be notified when they are tagged.  

Example (including the ' before @ in the example so I don't actually create @mentions - they're normal user-pill mentions in the actual data)

Row 1 Status: Done  Owners: '@John Smith '@Davey Jones '@Fred Flintstone
Row 2 Status: In Progress  Owners: '@John Smith '@Fred Flintstone
Row 3 Status: Done  Owners: '@Davey Jones
Row 4 Status: Not Started  Owners: '@John Smith '@Davey Jones

I'd like to be able to use the Pivot Table functionality to show each user in that field and the number of times they are mentioned, like this:

User          Not Started     In Progress   Done
John Smith         1               1          1
Davey Jones        1               0          2
Fred Flintstone    0               1          1

The problem is I can't figure out how to disaggregate that list of users in the Owners field.  I could potentially go through and insert a semicolon between each of the entries in the underlying child pages, but that seems kind of silly when Confluence knows that they are distinct values for display purposes.  But I'm not able to identify the mechanism that Confluence uses to do that identification, so I don't know how to identify the delimiter that would separate the names.  

Is there a way to break down that list using SQL or some other means in the Table Transformer?  

[Stiltsoft support · Accepted solution]

Hi @Rey Muradaz ,

The @ symbol is not a real symbol and it is not recognized by the Table Transformer or Pivot Table macros.

Your string 

@John Smith @Davey Jones @Fred Flintstone

will be seen as

John Smith Davey Jones Fred Flintstone

I mean you'll get a string consisting of 6 separate words with a space as a delimiter between them.

[Rey Muradaz]

That's exactly my problem.  There is no easy way for me to know that those names are actually first/last pairs instead of discrete words, even though Confluence knows that and displays them as @mentions (even in the Pivot Table output!).  It's very frustrating that it knows this information but I can't access the magic way that it knows it to be able to use it as well.  

Does this mean that my only alternative is to add (redundant) semicolons between the @mention pills? That's kind of disappointing, but if that's what I have to do, I will manage.  

Thanks for the reply - I look forward to your next response!

[Stiltsoft support]

Hi @Rey Muradaz ,

Yes, you are right - unfortunately, the possible solution is to use "visible" separators or place every mention in a separate cell (row).

[Rey Muradaz]

I was able to add the semicolon successfully to create the breakouts.  Now my question is whether there's a way to force the report to remove the @mention pill wrapper?  Some of the names come in as plain text and some still show up with the fancy formatting.  Any thoughts on this?

[Stiltsoft support]

Hi @Rey Muradaz ,

If for the first time a specific @mention was the only mention in the cell, then it will be shown as a real @mention in your aggregated table.

If for the first time a specific @mention wasn't the only mention in the cell and the delimiter worked, then it will be shown as plain text.

I've created a ticket in our backlog regarding this issue and added a link to this thread - once the feature is implemented, we'll get back here and notify you.

[Rey Muradaz]

Thanks for clarifying how this works, and for creating an item in the backlog.  I appreciate your responsiveness!  

[Stiltsoft support]

Hi @Rey Muradaz ,

The fix regarding this question has been just released.

[Rey Muradaz]

Thanks - it looks great! :)