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

[Laszlo Balla]

Rewinding a little bit - what is it actually that you would like achieve, i.e. what is the expected business outcome? What does this mean exactly:

---

To be able to make proper evaluations

---

Your answer might help generating further ideas 🙂

[Dominik9]

Hi Laszlo

Thanks for your respond. 

We would like to create reports. A simple example could be which support groups are referenced from how many records of table A (e.g. support group 1 is referenced from 10 records of table A). But here it should also be possible to drill down, so that you can jump directly into the support group or the record of table A.

[Laszlo Balla]

Gotcha. I'm not sure how to do it with a Database View... but: if the number of custom tables is not an issue, maybe you could just create a new, standalone (i.e. not extended) table for this purpose, with the columns you need (i.e. reference to Group, Reference to task, String for table name, etc.)? Then have an async business rule on Task to add/delete entries in this table whenever the assignment group changes. This is just a rough idea, but might be food for thought?

[Dominik9]

Hi Laszlo

Thank you for your idea. That would certainly be an option for a solution if we have no success with the DB View. However, we would like to prevent a new custom table if possible.

Best regards

Dominik