Simple reference qualifier on lookup select box

[kim-lindgren]

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.

[-O- · 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).

[kim-lindgren]

---

Thanks a lot, it makes sense now that the Lookup value field has to be a SysID. Because room_name is a reference, you would need the primary key of the referenced table which is the SysID, not the display value. As soon as I changed it to "room_type=[very-long-sysID]" it worked like a charm.

---

@-O- wrote:

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.

---

Thanks again, I recently bought Tim Woodruff's ServiceNow Development Handbook, maybe it's time to give it a read, start avoiding some of the pitfalls of ServiceNow development and get a feel for ServiceNow best practice!

[-O-]

You're most welcome 🙂

[-O-]

Decisions should not be made based on current situation, but based on what the situation might reasonably be in the lifecycle of the solution. I mean right now/in your testing it may be that there are only 10 rooms, but would your solution work in a multinational company with 1000s of rooms?

But back to reality, could you show the form of a Room Type too?

[-O-]

So, if field "room_type" is a reference, then reference qualifier

room_type=medium_room

is almost certainly wrong. Reference fields store the unique key of the referenced record. A unique key in 99.99% of cases in ServiceNow is a GUID; something like 2776278747dca910d2689995536d43c7. So your reference qualifier should look something like

room_type=2776278747dca910d2689995536d43c7

If Room type has some field that indeed has values like small_room, medium_room or conference_room in it, then you should change the reference qualifier to

room_type.<name of the field containing room type as human readable text>=medium_room

and it should work.

You will have made use of what is referred to as dot-walking. Also describable as traversing reference fields.