Database view with list field

[Dominik9]

Hi Experts

We have a table (Table A) in which support groups are referenced in a list field. To be able to make proper evaluations we had the idea to create a database view. We want a DB View in which one entry is created for each support group from the list field. An example how we imagined this:

Example table A has 1 record with 4 support groups. The DB View would then have 4 rows as follows:

Record 1; Support Group 1;
Record 1; Support Group 2;
Record 1; Support Group 3;
Record 1; Support Group 4;

Unfortunately in DB Views "CONTAINS" and "LIKE" is not supported in the Where clause. However, I found an article in which this is done in a BR (Solved: Database View - List Field - ServiceNow Community). The problem is that in our case around 250'000 records are first created and would then be filtered by the Business Rule. This obviously has a huge performance impact which we would like to prevent.

Does anyone have an idea how we can solve this? Is the DB View even the right approach?

Regards

Dominik

[Max_MS]

I got another error: `instr` is not allowed in the where clause

[-O-]

Without seeing how the view has been defined, I can't say much.

Probably referring to some custom field not by its (real DB) name, but by its alias.

[Max_MS]

Thanks for your response, I figured out why, I used underscore to link prefix and field, should be a dot (.)

[-O-]

You're most welcome, sorry I couldn't help more.

As for . vs. _, indeed "advanced" query interpretation mode "kicks in" only when the . separator is used.

There is a KB article about this too, but I can no longer find it.

[Felippe do Bem]

I had a similar requirement and the proposed solution worked perfectly! Thank you so much!