Database view for report

[Sangeetha_Jegan]

I have to create a report on 2 tables (sn_bcp_recovery_team and sys_user). So I created a database view. The ask is that the recovery teams table has a field 'User' which is of type 'List' (holds comma separated sys_id of the users as a string) which need to be split into separate rows in the database view. For example, if there is 1 record in recovery teams table that has 7 users listed under 'User' field- comma separated, I want the database view to have 7 rows for the single record where each row shows the user and other user related information (taken from sys_user table).

I tried creating database view with both the tables but I am stuck with how to frame the 'where' clause so that I can achieve the requirement. 

1st view table is sn_bcp_recovery_team table with a variable prefix of recovery and an order of 100. My fields are plan and user. Where clause is: !isnull(recovery.user) && !isnull(recovery.plan)

2nd view table is sys_user table with a variable prefix of usr and an order of 200. My fields are company, title, location, name and sys_id. Where clause is: usr.active=true

Some of the 'where' clauses I tried for User table:

1. usr.sys_id IN (
SELECT bcpt.user FROM sn\_bcp\_recovery\_team bcpt where bcpt.sys\_id=recovery.sys\_id)

2. usr.sys_id IN (recovery.user)

None of them seem to work. Can anyone help? Is there a way to achieve this?

Thanks in advance.

[Sangeetha_Jegan]

Hi @Bhuvan  

This line in your code which does the lookup

 if(gr.td_u_user_list.indexOf(gr.usr_sys_id)>-1){

 uses entries from database view and not user table or travel documents table. After gliding into database view table (new GlideRecord(dbview)), gr points to the database view table and not individual tables. And td_u_user_list refers to the field in the database view (you can check that in the database view you created, there would be a field by name td_u_user_list). 

What this business rule does is to query the database view entries and limit it by filtering on only the needed sys_id.

[Bhuvan]

@Sangeetha_Jegan 

I checked this in my PDI and confirmed it is working as expected.

I disabled the BR after creating this post which lead to creating DB view records for all users in sys_user table. I removed the list field values in sample records and I enabled the BR again and tested it, records are created only for user IDs listed in custom table glide list fields.

Make sure you have the BR in place first before adding values in the list view so that it does not populate record for all user records in sys_user table. It is possible you created the view first and later created BR and by that time records for all users in sys_user table would have been created in DB view table. 

You can create a new custom table with list field and replicate the logic for a database view table to test this scenario. 

If this helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

[Bhuvan]

@Sangeetha_Jegan 

Did you get a chance to review this ?

Hope you appreciate the efforts to replicate this solution in PDI and provide you with detailed information. If this helped to guide you, please mark the posts helpful & accept the solution.

Thanks,

Bhuvan

[Sangeetha_Jegan]

Hi @Bhuvan  

   Your help on this issue is much appreciated. Sorry that I was not able to respond earlier because I needed to do the explaining here and hence kept postponing

Basically, the moment we create a database view with view tables and the where clause, ServiceNow creates the entries for the db view based on the configuration details of the database view. The timeline of when you have the business rule created (before dbview creation or after dbview creation) doesn't matter.

Basically, query business rule works on load where it queries the database to retrieve the entries. So ideally, the entries should be there in the database for your business rule to query and filter it.

In your case, since you have less than 10k records, your business rule has no problem of looping through all the entries of the dbview and filtering the required ones.

If you can run the same BR with more than 10k entries, that is when you will see the issue that I am currently facing.

Hope you understand!

Thank you for all the help!