Database Views - How they filter data

[ishank1]

Hi all,
So, I was playing with Database Views in ServiceNow when I came across this problem. I want a database view on Incident table to filter all my rows that has contact type as Phone. This is the where clause I am using:

inc_contact_type = 'phone'.

and when I try it, the view is showing all the columns irrespective of the given condition i.e. rows with contact type phone, email web etc all are shown.

So, why the service now is not filtering the records?

PS: When I joined the above table with task table, its showing the records with contact type phone only, but I am not able to verify whether it is by some accident only or it really did applied filtering.

Other problem I am facing is, I want to filter all the records whose assignment group is Hardware. I tried below methods to filter record in Database view:

1) inc_assignment_group = 'Hardware'

2) inc_assignment_group = 'hardware'

3) inc_assignment_group.name = 'Hardware'

4) inc_assignment_group.name = 'hardware'

But none of them yield the desired result not even if join it with task table.

So, Please help me how can I achieve the above mentioned views.

Thanks in advance.

[gputker]

I got this working on a choice (TRUE/FALSE) field:

(cirel_child=eaapps_sys_id || cirel_parent=eaapps_sys_id) && eaapps_u_critical_application=1

The text in black joins two of the same tables with an OR condition.  The text in red returns only TRUE values for the critical application field.

Took a lot of tries - I'm rapt.

[Community Alums]

After hours of googling and trial and error, I managed to get filtering to work on Database Views before performing the join by setting the 'Left join' field to False. 

So if you want the inc table to select all fields where inc_contact_type = 'phone' first before joining the inc table with inc2 you need to un-check 'Left join' since Left joins will take all inc_contact_type values regardless of the filters applied.

So for the above example, you would use something like:

inc_contact_type='phone' && inc_primarykey = inc2_primarykey

and set Left Join to False.