question Database View Where Clause for List Fields in Developer forum

Database View Where Clause for List Fields

lmcmanamon — Fri, 24 Mar 2017 18:49:22 GMT

I'm trying to create a where clause on a Database View, but nothing is working. Table A has a List Field that contains references to Users (like the Watch List Field on the Task Table). Table B is that User Table. I want the where clause to state "the List Field CONTAINS this User" but none of the evaluators seem to product my wanted result. Can someone help me?

Re: Database View Where Clause for List Fields

amlanpal — Fri, 24 Mar 2017 19:19:48 GMT

Hi Lauren,

There is no such operators present which operates as 'CONTAINS' or similar. But there are couple or workarounds present. One is using report and another is to use Before query Business rule. Please find the relevant threads below:

1. CAN WE USE "CONTAINS" OR "LIKE" OPERATOR IN WHERE CLAUSE OF DATABASE VIEW

2. Database View Table Where Clause to contain NULL

I hope this helps.Please mark correct/helpful based on impact

Re: Database View Where Clause for List Fields

amlanpal — Sat, 01 Apr 2017 16:35:58 GMT

Hi lmcmanamon,

If I have answered your question, please mark my response as correct so that others with the same question in the future can find it quickly and that it gets removed from the Unanswered list.

If you are viewing this from the community inbox you will not see the correct answer button. If so, please review How to Mark Answers Correct From Inbox View

Re: Database View Where Clause for List Fields

lmcmanamon — Tue, 04 Apr 2017 16:33:08 GMT

I'm sorry, Amlan, but your comment didn't answer my question.

Re: Database View Where Clause for List Fields

nayanawadhiya1 — Tue, 04 Apr 2017 17:32:53 GMT

Hello Lauren,

Refer this may be it will help you -

ServiceNow Admin 101: Observations on Database Views, Part II

Re: Database View Where Clause for List Fields

Nithya V Sivara — Thu, 30 Nov 2017 22:14:52 GMT

Hi Lauren,

I am having the same issue. were you able to fix the issue?

thanks,

Nithya

Re: Database View Where Clause for List Fields

JC S_ — Tue, 29 Oct 2019 12:44:42 GMT

Were you able to make this work?

Re: Database View Where Clause for List Fields

Michel Donnat — Thu, 21 Jan 2021 22:36:30 GMT

Yes, by using a where clause of this type:

INSTR(prefix1.a_list_field ,prefix2.sys_id) > 0

Where:
a_list_field is a List of type referenced by prefix2.