JWT Calculated Field Not Searchable

[Jeremy Cupps]

Hello, I have a Jira Workflow Toolbox (JWT) calculated date-time field that I can't get to work with JQL searches.

Reproduction steps:

1. Create a JWT Calculated Date-Time Field
2. Enter a formula like the following: 
   

   min(fieldValue({issue.cf10000}, issuesFromJQL("type = 'Bug' AND project = " + %{issue.project.id} + " AND fixVersion in (" + %{issue.fixVersions} + ")")))

3. Verify that formula works correctly and is returning a value for at least some issues
4. Run a re-index
5. Run a JQL search against the new field, such as "cf[10001] IS NOT EMPTY"
6. No results are returned
7. Note that the calculated value is displayed properly in issues/search results even though it can't be searched for

I have tried background and full re-indexes multiple times with no success.

JWT version: 3.1.7

[Sergio García-Consuegra [Decadis AG] · Accepted solution]

Hi @Jeremy Cupps ,

I hope you find useful the information I'm going to share with you.

These fields are calculated each time they are read. So they are updated in real time.

They have a problem when used with expressions that depends on fields belonging to issues different from current issue, its subtasks, or issues under epic, or under its epic, which is precisely your case, since you are using issues returned by a JQL expression.

The problem is that index associated to this calculated field is only updated when current issue, its subtasks or issues under epic (in case it's an epic) or epic (in case it's under an epic) are updated. So while the calculated field always return the updated value, its index is not guaranteed to keep always updated, and for this reason JQL searches based on this calculated field can return not accurate results.

Our recommendation in order to find calculated fields is using the JWT JQL Functions, which allow you to combine JQL and Logical expressions from JWT.

In this example, I'm searching for calculated date-time (issue.cf13700) field that are not empty and from the project SP.

For this example we used the JQL function issueSelection. You can obtain more information and examples in this page from our documentation.

We recommend you trying this approach and in our most recent release, 3.1.7, which includes several bug fixes that will improve your experience.

Also,  we introduced recently we introduced our new Reindex issue action that helps keep custom fields in sync. This action is available for our Automation Rules.
 

Please let me know if you have any doubt with the proposed solution.

Thank you.

Best regards,
Sergio

[Jeremy Cupps]

Thanks @Sergio García-Consuegra [Decadis AG]. Unfortunately we plan to migrate to Cloud soon, and custom JQL functions are not available there so we would prefer to avoid that approach.

Your explanation of the problem makes sense; however, the field remains unsearchable even after running a full re-index of Jira. Is this expected, or does this indicate that there is another problem?

[Sergio García-Consuegra [Decadis AG]]

Hi @Jeremy Cupps ,

Unfortunately, that behaviour is expected, and the only workaround we can offer to you would be using our JQL functions.

Please let me know if there is any other doubt I may assist you with.

Thank you.

Best regards,
Sergio

[Jeremy Cupps]

I see. Thank you @Sergio García-Consuegra [Decadis AG] and @Alex Koxaras _Relational_ for looking into it.

[Alex Koxaras _Relational_]

Hi @Jeremy Cupps 
I didn't manage to reproduce your problem. I created a calculated date time field, but the JQL (is not empty) yield results.

You CF10000 is what field? What values does it have? In order to use that specific calculated I need to know that.

[Jeremy Cupps]

Hi @Alex Koxaras _Relational_, thanks for your quick reply. The custom field is a Date Time Picker field. I am able to search on that field directly, and I have one issue in my instance with a value for it.

[Alex Koxaras _Relational_]

I know what the custom field is. But on the formula you have a {issue.cf10000}. What is this field?

And I suppose that cf10001 which you search on the JQL is the date time field correct?

[Jeremy Cupps]

@Alex Koxaras _Relational_ {issue.cf10000} is a custom Jira Date Time Picker field (not JWT calculated field). I think I get the same result with a native field such as {issue.created}, but I'll double-check after running another re-index.

cf10001 that I referenced in "cf[10001] IS NOT EMPTY" would be the JWT calculated field using the formula in my reproduction steps.

[Alex Koxaras _Relational_]

Ahh! Sorry mate.. I got mixed up. You have totally explained this properly. Apologies.. is that time of the hour.. :/ Let me check and I'll get back to you.

[Jeremy Cupps]

No worries! I probably should've used less confusing names...

[Alex Koxaras _Relational_]

@Jeremy Cupps 

I reproduced the error, and indeed you can't use it on a JQL. However I found out that the problem lies in your code. :(

If you use an out of the box JWT expression, you will see that the calculated field is searchable via JQL. based on my research

• min(): This function returns the smaller of two numeric values.
• fieldValue(): Τhis function returns a number list with values of a given field from the given issues.

That "number list" could be the source of problem. Another could be inside the issuesFromJQL, and particularly the fix version equation.

So my question is if you can explain to me the formula calculation. You take the minimum between (theoretically) two numbers:

• A date time field
• A list of issue

But what exactly you need to compare and take the minimum?

[Jeremy Cupps]

@Alex Koxaras _Relational_ What I'm trying to achieve is to calculate the earliest value of the Date Picker field from the list of issues returned by issuesFromJQL. For example, the earliest created date of all the issues matching the JQL.

While the result is evaluated as a number when I test the formula, the value is being displayed correctly as a date in the issue view so I assumed there was no problem with my formula.

[Jeremy Cupps]

A little more info if it helps:

This example from the docs is similar to my formula in that it uses min and fieldValue with a date field (issue.dueDate), though it passes in a list of subtasks rather than issuesFromJQL (and calculates a diff with the current date):

min(fieldValue({issue.dueDate},subtasks())) - {system.currentDateTime}

https://apps.decadis.net/display/JWTSDC/Difference+between+the+current+date+and+the+earliest+due+date#expand-moreinfo

When I test the formula in the "edit expression" screen, it returns a value like "1.66335708E12". When I view the display output of the field in an issue, it is displayed in the specified date format like "16/Sep/22 3:38 PM", which is as expected.