Simple reference qualifier on lookup select box

kim-lindgren
Kilo Sage

I have three tables: Booking, Room, and Room type.

 

On the Room table, I have a reference field "room_type" that refers to the display value "name" on Room type. 

 

For the Booking table, I have three record producers (RP). Each RP should create a booking for a room of a particular room type. The three room types (records on the Room type table) are: "small_room", "medium_room", "conference_room".

 

On each Booking RP, there will be a unique Room type variable. The Room type variable is a lookup select box that needs a reference qualifier to show only rooms of the correct room type. I really think the qualifiers should be "room_type=small_room", "room_type=medium_room" etc, but it simply does not work. The Lookup Select Box comes back empty. If I do not enter a qualifier, I get all rooms of all types.

 

I'm sure someone will be able to spot the error. I am of course happy to provide additional information as needed.

 

Thanks.

1 ACCEPTED SOLUTION

Depending on what is the type of field "Room Name" the setup might not be correct. Option "Lookup value field" on the Lookup Select Box variable indicates which will "provide" the value  for a choice to store in the database. If "Room Name" is a reference, then Lookup value field has to be Sys ID. Only if Room Name is a field of type Choice, or String with option to display as choice, would this setting be possibly correct.

You should also specify a value in Lookup label field(s). That decides which field(s) of the table providing the choices (in this case Room type) to list in the Lookup Select Box when someone "drops-down" the Select Box. Not knowing the structure of work hub room, it is har for me to say which fields are appropriate/valid.

Lastly, if you allow me an advice, that also ties into how to "build" in harmony with ServiceNow established practices, but also generally accepted best practices, don't name fields Something Name. Unless it is really a name that is stored in that field. But even then, if you have a table Something, you should create a reference field instead and name it Something.

E.g. if you open an Incident record and look at the form/table, you will notice that it contains references to other records in other tables - like Problem (reference to table Problem) or Opened by (reference to table User) - on those fields are not called Problem Number or Opened by Name, even though that is what is displayed in those fields: the number of the reference problem record and the name of the user that opened the Incident.

Using and attribute of a record to refere to a record is also bad, because it will not be possible to differentiate between when someone references the Room Name vs the Room record (both will be called Room Name if you name references like that).

View solution in original post

11 REPLIES 11

Hm, room_type.name should work in that case, but it doesn't. I thought I didn't need to add "name" because it is the display value of the room type records. So "room_type=medium_room" but as you say it doesn't work.

 

kimlindgren_0-1673737609624.png

Maybe the SysID would work but I don't know where to see it. Just clicking on the SysID on the dictionary doesn't show anything.

 

I do know about dot-walking and SysID's, I just haven't got it all sorted in my head yet. Thanks for your patience 😅

 

 

You're welcome.

On system level things happen as in all other software: a query is executed using the names and values that are in the DB, not what is displayed. Of course one can select by the stuff that has been designated as "display value" too, but not because it has been designated as display value. The only use for the display value setting is to tell the system what to present/display to the user.

Also note that in this screen-shot the actual name of the field is not displayed, only the Label. And for sure the label of a field can be anything, totally uncoupled from the field name. In order to see the name of the field you should add column Column name to the list above. Could you do that and share again?