Getting a distinct list of the upcoming releases

[tal-yechye]

Hello guys,

I'd like to get the list of all upcoming releases of my project in my spreadsheet.

So the best I could reach till now was:

=JIRA("project = MyProjName AND fixVersion in unreleasedVersions() AND fixVersion ~ '*123' order by fixVersion asc", "fixVersion", 0, 500)

The problem is that the results contains many duplications.

For example:

abc 1.1.123

abc 1.1.123

abc 1.1.123

bcghd 2.1.123

bcghd 2.1.123

ggge 1.2.123

and so on.

How can I avoid these duplications and get a distinct list of release, like:

abc 1.1.123

bcghd 2.1.123

ggge 1.2.123

Regards!

[tal-yechye · Accepted solution]

Found the trick...

=UNIQUE(JIRA("project = MyProjName AND fixVersion in unreleasedVersions() AND fixVersion ~ '*123' order by fixVersion asc", "fixVersion", 0, 500))

[Nic Brough -Adaptavist-]

Ok, so this is not going to work the way you think.

There are in fact, no duplicates on your list.  What you've got are separate releases (versions) with the same name in different projects.  When you list

• abc 1.1.123
• abc 1.1.123
• abc 1.1.123

What it's actually showing you is 

• Project ABC - abc 1.1.123
• Project DEF - abc 1.1.123
• Project GHI - abc 1.1.123

Jira's versions are not cross-project - each version belongs to a project.  You will need to come up with a  way of bundling your releases together, where they are made up of a set of disparate versions from different projects.

When your people are deciding to put together a release made up of things from various projects, there's a couple of simple options - have a separate global release field, which you can stamp on to issues when you're deciding what versions of the various pieces to put in, or some of us use Epics, linked to the issues, and having their own version which we can then use as the release.

[tal-yechye]

Thank you Nick.

Why different projects?

I did set my project name...

project = MyProjName

[Nic Brough -Adaptavist-]

Because your results have three versions with the same name, they must be from different projects - you can't duplicate versions within a project.

But.  I misread the question.  Ignore the stuff I said before.

You're getting multiple results because you have multiple issues with that version on them.  Searches in Jira find issues, not versions/releases.  You're seeing abc 1.2.123 three times because the data you are asking for has three issues with abc 1.2.123 on them.

You'll need to code your spreadsheet to pivot on, summarise or de-duplicate the versions/releases.

[tal-yechye]

Yep, I guess that is the reason. I actually get all the issues here...