How to write a formula to highlight current sprint tickets

[Parveen Taj]

1. I have a structure which has feature and user stories both are linked as parent-child

2. How do I write a formula in structure to highlight only stories of current sprint in some particular color.?

[Ilia Mamontov]

Anything like that available for jira cloud? To highlight new tickets that was added to active sprint?

[David Niro]

Hi @Parveen Taj ,

For this, you will want to take advantage of the Item Properties for Sprint and the FILTER() or LAST() function to identify the Sprint you want to highlight.  Then, Markdown can be used to add the color to the returned value.

A quick example that you can use as a reference would be something like this:

IF issuetype = "Story":

""":panel[${sprint.LAST()}]{backgroundColor=yellow}"""

Hope this helps!

Best,
David

[Parveen Taj]

Unfortunately the above query is not helping.

I tried this and it works

IF ( Sprint = Sprint.LAST(),

" {panel : bgColor=#ADFF2F}CurrentSprint{panel}")

But still there is one problem, sprint.LAST() is not the one I am looking for I want to highlight only Current sprint tickets, how do I find what other methods are supported in Sprint variable for example  Sprint. Current  or sprint.active ? 

[David Niro]

Hi @Parveen Taj ,

That's interesting.  In your structure, do you see a button at the top that says "show generators" or do you see an option for "automation"?

Glad that you were able to get to a working formula.

Sounds like you will want to use FILTER() rather than LAST().  It should look something like this:

sprint.FILTER($.state = "Active")

Hope this helps!

Best,
David

[Parveen Taj]

At the top I see the button "Automation".

Yes, the above filter works, but it is applied to feature as well how do I exclude it , I tried below query but it does not work

IF( Sprint = Sprint.FILTER($.state = "Active") and issuetype != "Feature" )

[Parveen Taj]

I just got it .. I was doing something wrong at the braces.

Here is the query which Solved this problem.

IF (Sprint = Sprint.FILTER($.state= "Active") and (issuetype = "story"),

"{panel :bgColor=#ADFF2F}CurrentSprint{panel}")

[David Niro]

@Parveen Taj , glad it's working for you now!

for context on why the original formula wasn't working for you, there are some differences between Cloud and Onprem formulas.  The original formula I provided was written with Cloud in mind, but if you see "Automation", you are actually on an Onprem hosted instance.

Best,
David

[Parveen Taj]

Thanks for the clarification... Just one more thing, for now only the particular cell is in some color,  is there a way I can apply the color to  the entire row ?

[David Niro]

Hi @Parveen Taj ,

No, there is not a way to apply the color to the entire row.  For visibility, you could move the column in front of the summary so it's one of the first things to catch someone's eye.

[Parveen Taj]

In the above query it will not highlight the tickets if it is carried from last sprint to Current.

Is there a way if I can use contains 

IF( Sprint ~ Sprint.FILTER($state="Active" ))

OR

IF ( Sprint contains Sprint.FILTER($state="Active"))

[David Niro]

Hello @Parveen Taj ,

In your formula, instead of Sprint = Sprint.FILTER($.state = "active"), try:

Sprint.FILTER($.state= "Active")!= undefined

Best,
David

[Parveen Taj]

No this is not working

IF(Sprint = Sprint.FILTER(($.state = "Active")!=undefined) and (issuetype = "story"),

"{panel:bgColor=#ADFF2F}CurrentSprint{panel}")

1. Now my current sprint is "sprint4 "

2. There are couple of tickets carried from "sprint 3" to "sprint 5" even these panels are highlighted in color  , which should not be..

3. I have some tickets already created specific to "sprint 5" even these panels are highlighted in color , which should not be..