Formula to set a fixVersion given a due date

[Eric Brown]

I have been experimenting with formulas and I find them super useful.
We use the Gantt view to do our planning and I would like to have a column that calculates the fixVersion based on the Gantt due date. 

In other words we move a task on the Gantt chart and the fixVersion is determined by checking if the task due date falls between the fixVersion start/end dates.

I know the MAP function will allow me to iterate through the versions set on an issue, but is there a way to iterate on the versions on a project?

In pseudo code that would be:

for issue x

foreach version v in project y:
     if( x.due_date > v.start_date and x.due_date < v.end_date )
           set version column to v

Looking through the doc I found variable references only for Issues and Structure.

[Stepan Kholodov _ALM Works_ · Accepted solution]

Hello @Eric Brown 

If I understand correctly, issues might be assigned to different versions and you want the Formula column to show a specific version or versions whose Start & Release dates time period matches the Due Date of issues. Please let me know if I'm missing something.

In this case, you can try this formula:

fixversions.FILTER($.startdate < duedate AND $.releasedate > duedate)
The formula will compare the issue's Due Date to the date range of its versions and return the list of matching versions.

If you need further assistance, please feel free to contact us directly at support.almworks.com and we'll get back to you shortly.

Best regards,
Stepan Kholodov
ALM Works

[Eric Brown]

Hi @Stepan Kholodov _ALM Works_ ,

Sorry if I was unclear. 

An issue has only a due date set, and no fixversion.

What I would like to do is to set the fixversion by iterating on the project's fixversions and finding the one corresponding to the due date. Your example formula filters on the fixversions associated with the issue. What I am looking for is a way to filter on the project's fixversions.

The use case is a user dragging a task to the right in a Gantt chart. This sets a new Due Date. The formula would automatically calculate the corresponding new Fix Version.

[Stepan Kholodov _ALM Works_]

Thank you for sharing more details. Something like can't be done in a straightforward way - the Formula column gets access to Versions through the field in issues, so for it to return any version there should be an issue with versions assigned to it for reference. It can be a parent issue of issues that you move on the Gantt chart or some specific issue in the structure that has all possible versions in it, for example.

We'll be glad to look into your setup and recommend a solution. Please contact us directly for proper support.

Best regards,
Stepan Kholodov
ALM Works

[Eric Brown]

Ok, thanks for looking into this Stephan.

If I understand correctly it would be possible to create a 'reference issue' which has all the relevant version assigned? Then could I access this specific issue from a formula? (say the hey is XXX-1000, and the issue is somewhere in the structure)

I will mark your answer as accepted regardless. This is not a huge blocker for us and I can probably to an offline sync with a python script.

Thanks!

[Stepan Kholodov _ALM Works_]

Yes, you can access versions from a specific issue in the structure. But this issue should be either a direct parent or an ancestor of issues with Due Dates - the Formula column can only access the issues' values through the parent aggregate function. For example, you can place the issue with versions at the top level of the structure and add tasks underneath it; then you'll be able to use a formula like this:

with x= parent{fixversions}:
if type="task": x.FILTER($.startdate < duedate AND $.releasedate > duedate)

If the issue is not a direct parent and tasks are located lower in the hierarchy, add the #level modifier and specify the exact level of the issue.

Best regards,
Stepan Kholodov
ALM Works

[Dinesh Radhakrishnan]

Hi @Stepan Kholodov _ALM Works_ ,

I have the exact use case as you described in your initial reply. But the formula doesn't seem to work. Can you help advise where I might be going wrong?

Context: I have release versions set up through the year. One release for each month. I am looking to automate so that when a team member changes the due date on a ticket, the fix version gets updated to the release for that corresponding month. 

Thank you very much in advance. 

[Stepan Kholodov _ALM Works_]

Hello @Dinesh Radhakrishnan 

We would need to know more details about your setup to be able to advise anything. The Formula only works inside Structure, so it can't be recognized in any other place including another app's configuration. Please reach out to us directly at support.almworks.com and we'll get back to you shortly.

Best regards,
Stepan Kholodov
ALM Works