Query a choice list by it's display value

[whitewalker]

I have a table with a field "Type". This field is a choice list where choices label and value are different. Ex:

Label - Value

Email - 1

DB - 2

CRM - 3

How do I use gliderecord to query this field by it's display value ?

[Harshad Wagh]

Hi,

I dont think so you can use the display value in glide query of the choice field as this will lookup on the database value.

Even the query which gets build from the filter is like "u_choice_1=1".

question: why do you want to use display value and what are the challenges are you facing in using database value?

Can't you pass the database value when you call the script include?

please let me know in case any concerns.

thanks

Harshad

[whitewalker]

I want to use display value because I want to search items by specific field display value. Search parameters comes from user input, and they can create a request like this: give me all items where Type is "email" or "DB" or anything else, or where Type can contains words "Email, DB, CRM, etc"

[Harshad Wagh]

Here are couple of options then, as we cannot use display value in glide query for choice field.

1. Create database values as same as display values(you can have them in lower case and whatever user enter first convert those into lower case and then run a query, this should take care of users entering values like "EmAil" 

OR

2.Rather than choice use a reference field and save your all records on a specific table.

and write a glide query as below.

gr.addQuery('Type.name', 'Display value on the record goes here'); //assuming name is the display value on the reference table.

thanks

Harshad

[whitewalker]

None of this option can satisfy my request.

1. Sometimes user can create a request like this: give me all items where Type is lower/greater than 1/2/3...

2. Client requested that this field looks like a dropdown, not a lookup field.

It looks like I'll have to manually check display value after query;

something like 

gr = new GlideRecord('tableName');

gr.query();

while(gr.next())

{

if(gr.u_type.getDisplayValue()=='Email')

{

//Do Something

}

}

[Harshad Wagh]

yes then it should help to achieve you the expected result.

Thanks

Harshad