nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Table Transformer_Find and replace multiple values in 1 table from another table

Hoang Nguyen_wvmrx1

Hello,

I'm building a Confluence dashboard by pulling fields from Jira. However, those custom fields are showing alias, but not Full name. For a workaround, I used the User List macro & Table Transformer to pull the full name. So, we have 2 tables here:

Table 1 (pull from Jira fields):

Column_1 Column_2

abc123 wer456

wer456 asd789

asd789 abc123

Table 2 (pull from User List macro):

John Doe (abc123)

(abc123@md.com)

Jane Doe (wer456)

(wer456@md.com)

Sarah John (asd789)

(asd789@md.com)

And I want to replace alias from Table with Name only from Table:

Column_1 Column_2

John Doe Jane Doe

Jane Doe Sarah John

Sarah John John Doe

How do I do that? Thanks!

Accepted answers

Stiltsoft support

Hi @Hoang Nguyen ,

Here is a similar question for you to check regarding the case - please follow the described steps.

The additional step will be to transform your User List macro into the Table 2 from the example:

Wed 4-1.png

Wed 4-2.png

SELECT
T1.'Group: comrades' -> split("(") -> 0 as 'Name',
SUBSTRING(MATCH_REGEXP(T1.'Group: comrades', "\([\s\S]*\)", "g")::string, 2)->slice(0, -1) as 'ID'
FROM T1

Wed 4-3.png

All comments

Stiltsoft support

Hi @Hoang Nguyen ,

Here is a similar question for you to check regarding the case - please follow the described steps.

The additional step will be to transform your User List macro into the Table 2 from the example:

Wed 4-1.png

Wed 4-2.png

SELECT
T1.'Group: comrades' -> split("(") -> 0 as 'Name',
SUBSTRING(MATCH_REGEXP(T1.'Group: comrades', "\([\s\S]*\)", "g")::string, 2)->slice(0, -1) as 'ID'
FROM T1

Wed 4-3.png

Hoang Nguyen_wvmrx1

Thank you. It works now!

Hoang Nguyen_wvmrx1

Hi @Stiltsoft support

The instruction works great when we have 1 custom field. However, when I added another column for another custom field (with the same data type), I received this error:

And here is the script:

SELECT
FORMATWIKI("{cell:width=200px}",T1.'Summary',"{cell}") AS 'Summary',
FORMATWIKI("{cell:width=150px}",T1.'Assignee',"{cell}") AS 'Ops Readiness Lead',
T1.'Early Adopters',T1.'PS Champion Trainers',T1.'CSM(s)',T1.'Product Support Team',T1.'Customer Support Team',T1.'Support Day 1 Team',T2.'Name' AS 'Documentation Process Lead',T1.'Offering Lead'
FROM (SELECT T1.'Summary',T1.'Assignee',T1.'Early Adopters',T1.'PS Champion Trainers',T1.'CSM(s)',T1.'Product Support Team',T1.'Support Day 1 Team',T1.'Documentation Process Lead',T2.'Name' AS 'Offering Lead'
FROM T1
LEFT JOIN T2 ON T1.'Documentation Process Lead'=T2.'ID')
LEFT JOIN T2 ON T1.'Offering Lead'=T2.'ID'

Where, the 'Documentation Process Lead' & 'Offering Lead' are the custom fields (single user picker). Could you please help with this? Thanks

Stiltsoft support

Hi @Hoang Nguyen ,

The links to the example and our support portal if you are stuck were given here, in your separate question: https://community.atlassian.com/t5/Confluence-questions/Table-Transformer-Transform-ID-into-Name-for-Multi-User-Picker/qaq-p/2750955#M317778

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

jira

filter

data-center

table

table-filter

table-filter-and-charts

confluence-data-center

table-transformer

table-filter-charts-spreadsheets