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

[Hoang Nguyen]

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!

[Stiltsoft support · Accepted solution]

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:

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

[Hoang Nguyen]

Thank you. It works now!

[Hoang Nguyen]

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

[Stiltsoft support]

Also check the initial example once more: https://community.atlassian.com/t5/Confluence-questions/Merging-two-tables-with-SQL-to-transform-ID-into-Name/qaq-p/2427125

Pay attention that we refer to the columns coming from the T1 table without the T1 prefixes ('Col 1' but not T1.'Col 1' and so on).

If in the internal SELECT it is still allowed, then in the external SELECT you can't refer to T1.'Col 1' and so on: the table was modified and it is not a T1 anymore.

So, compare your query with the example once more. You use prefixes everywhere.

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)'...