Database view not working when the common field taken is of type glide_list

[Shreyas Khade]

Tried creating a database view between two tables wherein the field that served as a primary key was of type glide_list.

ie Field is table A is of type glide_list which references table B. The requirement is to fetch other fields of the same record into the database view.

the where clause written for this was : base_u_location = ref_sys_id

Here table with suffix  'base' has u_location as a glide_list field type that references another table location. After clicking on try it some of the columns get populated while some get populated with 'empty'. Any possible issues known ?

PS: Have tried all the permutations and combinations of the where clause possible 

[Bert_c1]

Hi Shreyas,

I expect the record set will only contain records when the 'u_location' field only contains a single value. As far as seeing fields that are 'empty', my guess is those fields do not have any value in the corresponding table. Seems easy to check by querying the table using some field from the database view. There could be ACLs preventing seeing some field values, but then the a simple query on the table should also result in the same.

[Shreyas Khade · Accepted solution]

After having thoroughly investigated the issue. We saw that the issue of the database view not throwing up the values in the fields and showing empty was because all the letters that are used to create an Alias for the table under database views are supposed to be in lowercase and recommended by Servicenow. We were initially using a camel case kind of alias due to which the issue was seen