Advance search custom field with !~

[Jose Linares]

We have several custom fields and we are trying to search one particular field that contains values that look like TP001, TP150 etc. I am trying to do an advance search on the field using !~ "TP" so that I dont get records that have those values but the search still produces records that contatin the "TP" value. Any ideas?

[Andy Nguyen]

Hi Jose,

If you use !~ "TP" the exact word TP will be searched for (without any characters in front or behind) hence something like TP001 does not match (thus still appears in the search result). You should use !~ "TP*" instead so that the query will search for all phrases starting with TP and exclude them accordingly.

Cheers,

Andy

[Jose Linares]

Hi Andy, yes I've tried using !~ "TP*" and that gives me no results when I know I have records that don't have the TP in the field. Jose

[Andy Nguyen]

Hi Jose,

It'd be good if you can let me know exactly which type the custom field is and provide me with several examples of its full values: which one should be returned from the query and which one should not.

Cheers,

Andy

[Jose Linares]

Hello,

It's a custom field, Text Field (single line) called CCSDItemNum.

Examples of values that are in the field are TP015, TP061, TP075 or blank. We want the query to return records where the CCSDItemNum field is blank.

Thank you

Jose

[Udo Brand]

Hi Jose,

if you want those issues where the field has no value, use

CCSDItemNum is empty

Cheers,

Udo

[Jose Linares]

That does work thank you but what happens if we start entering in values of TD001 etc or some other variation of letters and numbers and we only want the ones that start with TP?

[Udo Brand]

you could create a filter (filter1) that shows the issues which starts with TP like

CCSDItemNum ~"TP*"

and then use a negation of that filter

not filter = filter1

[Jose Linares]

no answer found as of yet.

[Andy Nguyen]

Hi Jose,

I think you're not consistent with what you want to achieve hence it's very hard for us to understand and help you with your request.

At first you said you'd like to exclude values starting with TP so I believe CCSDItemNum !~ "TP*" should work.

Later you said you want empty values so, as Udo suggested, CCSDItemNum is Empty should work. If I'm not mistaken, however, what you really want is values not starting with TP or empty values. If that's the case, CCSDItemNum !~ "TP*" OR CCSDItemNum is Empty will work.

Just now you said you only want values starting with TP, then I think CCSDItemNum ~ "TP*" should be enough. If you still want empty values, then just append OR CCSDItemNum is Empty.

I know you may want to experiment with different JQL queries but, if you change your request all the time, we may be confused and cannot follow. Please be consistent with one request first and we'll work out the others. For your information, the only operators available for Text Field (single line) are ~ (text), !~ (text), is (empty), and is not (empty). You may use * or ? to enhance the queries but remember not to put them at the beginning of the text parameters.

All the best,

Andy

[Jose Linares]

Thank you for your professional advice on how to request assistance. I will figure it out. You may close out this thread.