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.

[Gurpreet07]

For your first problem , as you want all records from task and few from incident, you need to check join for task table.

2nd problem : Assignment_group is a reference field on incident table and thus it consists sys_id.

inc_assignment_group = '<sys_id_of_hardware_group>'          

[ishank1]

Hi Gurpreet,
Thanks for your valuable time to answer my question, however, I am not worried about the joins. what I am worried about is the filtering being performed by ServiceNow is not correct. If I select

                  inc_contact_type = 'phone'

then it must show me all the records with contact_type phone only. If its not showing that appropriately, then how can I be sure that results shown after join operation are correct.

for 2nd problem, I know this way, but I don't want to go for it because of following reasons:

1) I don't want to hardcode any thing.

2) sys_id may not be known to my users all the time. so, getting it and using it will be a long procedure.

3) sys_id is a hidden field in table. I don't want my users to exploit that field directly except some special cases or groups.

Also, when you apply filters at the top of any table, serviceNow returns the query like this:

                  assignment_group.name=hardware

So, I am expecting there must be some way.

Thanks,

Ishan K.

[Gurpreet07]

1. There is no need to join tables task and incident since all fields in task are available on incident too . if its just for practice then you need to define a relation between two tables like we know that sys_id or number of the records in two tables should be same. Following filter in where clause should work.

inc_number = task_number and inc_contact_type = 'phone'                                       //check table prefix

2. The method you are using to define filter for assignment group in where clause (inc_assignment_group = 'Hardware') is also requires hard coding of group name in where clause .

For User perspective , they are not even allowed to edit where clause of database views .   Better to apply filters when creating reports from this view and there you could use name of group as well.

[ishank1]

Well Gurpreet, I think you are not able to get my question.
What I posted is when I make a database view with just one table incident, and give this condition in where clause:
        inc_contact_type = 'phone'

this where clause is not giving the right results.

So, my question is why it is not giving the right results?

for 2) don't you think that mentioning assignment groups name is far more easier that mentioning some "XYZ" Number.

Plus, I don't want my users to get into the details of finding sys_ids of there groups. that's why I am looking for some workaround here.

And its up to the administrators what kind of flexibility they want to provide to user based on his/her group/role.