Custom sorting and ordering: how do I join (concatenate) two custom fields into a new select field

[Deleted user]

I am exploring the Jira functionality at the moment and while the searching and filtering is quite powerful, I have come across a limit in functionality around custom sorting / ordering - or a limit in my knowledge.

I need to be able to combine values from two separate custom fields that have values entered by two different work areas. I cannot combine them into one. I then want to be able to combine or concatenate them into a new custom field that has set options (like single select), so not text, so that I can apply a custom order. This would essentially allow me to run the two original fields through a matrix and set the order of the destination values by the matrix hierarchy - think risk management by combining likelihood and consequence to give an overall risk score. I want to be able to sort and order issues by this overall risk score and not just likelihood or just consequence.

I have tried to arrange these two fields into a cascade select form, but then I can only set the order by parent and then by the child. I cannot set the order of parent and child combined which is unfortunate.

I have tried using a post function (copy fields) pulling from cascade select sheets but only the child values copy across (and I think it only copied into text fields anyway though I may be wrong on this).

I have tried using Jira automation which I can only get to work for combining the two values into a text field. I used the following.

{{issue.Customfield1.value}} – {{issue.Customfield2.value}}

This is great but I cannot then set an order with text fields. I can get the automation seemingly setup (it runs and shows no error) to enter into single select fields but when I look in the issue, the field remains empty. To clarify I set the single select field to have all of the possibly options from the two original fields.

I feel like this should be easy and I may be missing something obvious. I've exhausted my abilities and need help. Is it possible to pull together values from two different fields into a select field (or equivalent) to allow for custom sorting or ordering when running filters? I want to be able run ASC or DESC by this combined 'overall risk' order with the results. Is there another way to achieve my aim?

[Deleted user]

I was able to work out a somewhat hacky solution.

01-ExtremeRisk{{issue.Likelihood.value.Certain)}}{{issue.Consequence.value.Critical)}}

This will print out "01-ExtremeRisk" in a custom text field I've called 'Overall Risk'. However I need to put together an automation for each combination of Likelihood and Consequence using a risk matrix to set the numbering order.

This way, I can now sort and filter issues by this 'Overall Risk' column (ASC or DESC) and not only by the underlying likelihood or consequence values.

If the automation into the single select fields could work, it would mean only one automation would be needed. You could then set the order of the single select in the custom field configuration.

If anyone has a more efficient solution I welcome suggestions.

[Margus _SoftComply_]

Sometimes its better to review and evaluate apps dedicated for that purpose - for server https://softcomply.com/what-are-the-best-risk-apps-on-jira/ and for cloud https://softcomply.com/comparison-of-risk-management-apps-on-jira-cloud/

rgs,

Margus

SoftComply

[Deleted user]

In case anyone has the same odd need that I have around ordering by multiple fields together (not individually or one then another - such as using risk management matrix), I have found a somewhat hacky though workable solution.

Context. I need to be able to consider two fields to sort issues by - likelihood and consequence. Together, through a risk matrix they form an overall priority (risk). It is this overall risk priority I need to be able to sort issues by, not the underlying likelihood or consequence values.

Here is what I have come up with.

Using automation, I apply this simple flow.

• When an issue has the likelihood or consequence fields changed
• And the condition that both fields are not empty
• action the following smart value (ignore the quotation marks) "01{{issue.Likelihood.value.Certain}}{{issue.Consequence.value.Critical}}"
• The edited issue is a text field I have called 'Overall Risk' (I cannot work out select fields which would be easier - see above and below)

This simply produces a "01" in the 'Overall Risk' custom text field. Less likely and lower consequence combinations get higher numbers etc. I guess I could expand to have "01-Extreme-risk" as the result rather than just a number.

To do this, I need to create an automation for all possible combinations of likelihood and consequence for each combination, I use the risk matrix to assign (prepend) a number in the appropriate order.

Then, when searching and filtering either in a project or across projects, I can use this 'Overall Risk' text field and sort alphabetically. This is what I need!

If the automation of combining both original values into a select field worked, you'd only need to set the one automation as the result would print into the select field, then assign the order in the custom field configuration. This smart field formula apparently works according to the automation audit but doesn't show up in the destination field as intended. Perhaps someone else could get this to work.

{{issue.Consequence.value}}–{{issue.Likelihood.value}}

So it's hacky and convoluted but it will work.

I welcome any suggestions to streamline this or make it more efficient.

[Elad Ben-Noam]

Hi @[deleted] 

First, I must say that your thinking is correct and also your formula. It can work on a single select field but you must be precise with the text, no extra spaces, exact chars and so, also make sure that you select the formula when entering it into the edit issue action otherwise it will empty the field's value:

Regarding ordering queries, I might understood you wrong but you can easily use "ORDER BY" any field you want, like this:

"A 1 B 2 C 3" is not empty ORDER BY "A 1 B 2 C 3" DESC

Hope it helps.

[Deleted user]

Thank you very much for taking the time to help me out. I made a slight amendment to the automation by removing the space between the curly brackets and the hyphen, and went into the destination field to remove the spaces in those so that the two connected values, when combined, perfectly match the possible values in the destination and... it is still not working.

The automation audit tells me the automation part is working properly. However when I go to look into the specific issue I have used to test this in the destination field that should be getting populated and it still remains empty. I can't think of what else to do. I've even changed from using the name of the custom fields to using the custom field IDs and still nothing. The automation has apparently worked but no end result.

And regarding ordering queries, I am unable to order in ways that I need. Searching and filtering is quite powerful, but ordering is not. Ordering is very important for me. I can only sort by one field at a time in ACS or DESC. I need to be able to include two fields, or two combined fields, when ordering results.

• I cannot order by combined cascading select field. So if I have a cascading select field that includes a parent for likelihood and a child for consequence I cannot set a sort for the combined response. I can only sort by likelihood and then consequence within that likelihood. But I need to factor in that second consequence value as being more equal since some low likelihood issues may have very high consequences.
• I cannot include specified text around quotation marks "" in an order I would like them displayed. This makes text fields unable to be used for ordering. I am aware I can add numbers like 01, 02 etc in front of the words but I am thinking of a text field where I have been able to get the automation of connecting two other custom fields into one as per the previous.
• I cannot combine two different custom fields (trying to work around the limitation of cascade select fields) with the text from those fields around quotation marks "" when ordering.

I understand this may be an edge case for Jira but it's a really important functionality I need to be able to more confidently embrace Jira at the moment.

Actually, writing this out, it occurs to me I may be able to generate a rather cumbersome hack. I could potentially setup an automation that looks for a specific combination of likelihood and consequence values, combine them into a text field and include a number in front of that. That way, I could sort this text field alphabetically and could set this order by the numbers. I was able to get the printing into a text field before via automation. But I could put a 01 or 02 in front based on the order that I want to specify...

• 01-{{issue.customFieldId=10032.value.Certain}}–{{issue.customFieldId=10033.value.Critical}}

I am not sure of the syntax around the key term I want to reference yet (like 'Certain' and 'Critical' but I'll look it up to see if I can get this to work.

I am just trying to apply a risk matrix to two fields to determine/set ordering.

Thanks again for your help!

[David Pinn]

At the risk of being self-serving, I am feel compelled to mention that "there's' an app for that."

David Pinn
ProjectBalm (the makers of Risk Register for Jira Server and Jira Cloud)