Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,298,503
Community Members
 
Community Events
165
Community Groups

Table Transformer - Multiple conditions one calculated field

Hello,

I would like to run multiple checks on issues by using multiple conditions (CASE-WHEN-THEN) in Table Transformer macro while using the same calculated field to present the results of these checks as a summary. And I would like to know how to combine multiple conditions with the same calculated field.

I would like something like this (if it worked):

CASE
WHEN FLOOR(DATEDIFF(DAY,'Created',"today")) > 16 THEN "Aging issue"

WHEN FLOOR(DATEDIFF(DAY,'Updated',"today")) > 14 THEN .+ "\nUpdated > 10 days" 
END AS 'Checks',

 

Thanks a lot,

3 answers

1 accepted

Hi @Emil ,

I can suggest using the following query structure for your case:

Sat 2-1.png

SELECT 'Issue', 'Created', 'Updated',
FORMATWIKI('Checks Created' + " \n" + 'Checks Updated') AS 'Checks'
FROM (SELECT *,
CASE
WHEN 'Checks Created' IS NULL
THEN "-"
ELSE 'Checks Created'
END AS 'Checks Created',
CASE
WHEN 'Checks Updated' IS NULL
THEN "-"
ELSE 'Checks Updated'
END AS 'Checks Updated'
FROM (SELECT *,
CASE
WHEN DATEDIFF(DAY,'Created',"today") > 16
THEN "Aging issue"
END AS 'Checks Created',
CASE
WHEN DATEDIFF(DAY,'Updated',"today") > 14
THEN "Not updated issue"
END AS 'Checks Updated'
FROM T*))

Sat 2-2.png

As you can see, here we have the three levels of the SELECT function.

The first one that is internal is used to run different columns through different conditions and the result is written into separate columns as well ('Checks Created' and 'Checks Updated' for my example).

The second SELECT is used to replace empty cells with some character ("-" for my example). If your conditions on the previous step are built in a way that there will be no empty cells, you may miss this step.

The third external SELECT is used to combine all your result columns into one master column ('Checks' for my example).

Hope this helps or at least gives you some further ideas how to transform your tables.

Hello @Katerina Kovriga _Stiltsoft_ ,

Thank you very much. I really-really appreciate your answer and the fact that you have replied so quickly. The code is working very well and it will help a lot. :-)

I've just made slight modifications and try to understand the code in the meantime.

First modification was that I've moved the "\n" and the "-"s to

THEN "- Aging issue"

and 

THEN "\n - Not updated issue". 

This way I guess there is only line break and bullet points before the lines in Checks field if the Checks Created and Checks Updated fields are not NULL.

 

And the second modification was that I've replaced the "-" with "" when 'Checks Created' IS NULL or 'Checks Updated' IS NULL. This way no bullet points appeared in Checks field when these fields were NULL.

WHEN 'Checks Created' IS NULL
THEN ""

and 

WHEN 'Checks Updated' IS NULL
THEN ""

 

So, finally this was my code with the slight modifications:

SELECT 'Issue', 'Created', 'Updated',
FORMATWIKI('Checks Created' + 'Checks Updated') AS 'Checks'
FROM (SELECT *,
CASE
WHEN 'Checks Created' IS NULL
THEN ""
ELSE 'Checks Created'
END AS 'Checks Created',
CASE
WHEN 'Checks Updated' IS NULL
THEN ""
ELSE 'Checks Updated'
END AS 'Checks Updated'
FROM (SELECT *,
CASE
WHEN DATEDIFF(DAY,'Created',"today") > 16
THEN "- Aging issue"
END AS 'Checks Created',
CASE
WHEN DATEDIFF(DAY,'Updated',"today") > 14
THEN "\n- Not updated issue"
END AS 'Checks Updated'
FROM T*))

 

Anyway, it is working very well. That was exactly what I was looking for. So, thank you a 1000 times again. :-)

Emil 

Hi,

One more question to this topic. I tried to set a fixed column width to the calculated 'Check' field but it didn't work. Actually the column width has been set to 200px but the texts {cell:width=200px} and {cell} also appeared in the field in each case.

 

I've tried it this way:
FORMATWIKI("{cell:width=200px}" + 'Checks Created' + 'Checks Updated' + "{cell}") AS 'Checks',

This way:
FORMATWIKI("{cell:width=200px}" + ('Checks Created' + 'Checks Updated') + "{cell}") AS 'Checks',

And this way:
FORMATWIKI("\"{cell:width=200px}\"" + ('Checks Created' + 'Checks Updated') + "\"{cell}\"") AS 'Checks',

 

The interesting thing that with another simple column it worked well and then the texts {cell:width=200px} and {cell} didn't appear:

FORMATWIKI("{cell:width=200px}" + 'Last Comment'+ "{cell}") AS 'Last Comment',

 

Any idea?

 

Use an additional Table Transformer - wrap it outside the first one. 

In this case all the transformations and calculations done in the internal Table Transformer macro won't be visible for the external Table Transformer macro. The external macro will work with your table as a plain one and the FORMATWIKI option won't be overloaded and give inconsistent results.

Hello Katerina,

Sorry for the late answer, I had the chance just now to try out what you wrote last time. Thanks a lot, it is working. I've used an additional Table Transformer and in the external one I could set the fixed column width with FORMATWIKI. The only thing that in Chrome the result of the different checks that were bullet points before now appears in the same line without any line breaks in the Checks field although the line breaks are still there in the query. Because actually in the picture below we should see this in the Checks field:

  • Long-running issue
  • Is the Assignee OK?

Chrome:

image.png

 

And in Firefox only the result of the second check appears, the first check doesn't for the same row in the table:

image.png

 

Do you have any idea?

Emil

Hi @Emil ,

Please check this part of our documentation - the "Preserving initial cell formatting" section.

Seems that it may help (the difference is in pluses and commas for the same FORMATWIKI query).

Thanks a lot again. It is working this way in Chrome. Although in Firefox still only the result of the second check appears but we can use Chrome.

It's a strange behaviour for Firefox, we try to support it.

Please check if you have its latest version installed, then check if the app's version is up-to-date (the current one is 8.6.1).

If the issue persists, please create a support request.

Attach the page storage format (upper right corner, menu ..., view storage format). If you don't see the option, ask your administrator to do it for you. Then we'll be able to recreate exactly your macros and queries.

As your data comes from Jira, it won't be recreated, so please take a screenshot of the Jira Issues macro (when the page is published and the macro is not wrapped into other macros). The headers should be visible.

We'll try to look closer into the issue.

Thank you very much. I will check the versions first.

Ooooh, I've changed the language of Firefox to English (US), and now it is working properly too.

Oh, yes - the Jira fields might be translated differently for different languages, didn't think about it at the moment.

Glad you solved the issue!

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

196 views 1 3
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you