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 @Bert_c1 

   I completely agree that "_" is the way to go with respect to dot walking to specific fields in database views, no denying that. Since it didn't do anything with respect to achieving my requirement, I had said that it didn't make any difference. But best practice wise, I take yours and @Brian Lancaster's point.

Thanks!

[Bhuvan]

@Sangeetha_Jegan 

This is not officially supported in database view table but you can use below logic.

Create database view with below conditions,

In this example, I have used a custom table 'u_travel_documents' where 'u_user_list' is a glide list data type and looking up records  in sys_user table.

Define database view and create view tables for 'u_travel_documents' and 'sys_user'.

Where clause for 'u_travel_documents' table 

td_u_user_list != ''

Create a business rule on database view table, select 'Advanced' When is 'before' and run on 'Query'

Below is the script that does a glide record of view table wherein it looks for 'sys_id' of user glide list from custom table and  push it to array.

(function executeRule(current, previous /*null when async*/ ) {
	var answerArr = [];
	var gr = new GlideRecord('u_travel_documents_user');
    gr.query();
    while(gr.next()){
		if(gr.td_u_user_list.indexOf(gr.usr_sys_id)>-1){
			answerArr.push(gr.sys_id.toString());
		}
    }
	current.addQuery('sys_id', 'IN', answerArr.join(','));
})(current, previous);

Below is a sample for testing. I have created 2 records in 'u_travel_documents' table with 5 user IDs in one record and 2 user IDs in another record.

It creates 7 records in the database view table as expected,

Please use below thread for reference,

https://www.servicenow.com/community/platform-analytics-forum/database-view-list-field/m-p/1262510/p...

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 ?

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

Thanks,

Bhuvan

[Sangeetha_Jegan]

Hi @Bhuvan  

  Thank you so much for providing with actual-tried and tested example to solve my issue with business rule. One of the other approaches I tried (apart from different 'where' clauses in the view tables I listed above) was to create a query business rule and I used exactly the same logic as you did, to filter out records from the database view. 

The problem I rain into with that approach is that, currently there are around 7000 records from sys_user table and 100 records from recovery teams table, which brings my database view to having 700,000 records. There is an in-built ServiceNow scripted query limitation that only returns first 10k records. So, my gliderecord query is not looping through the complete set of 700k records. 

There is a ServiceNow system property 'glide.db.max_view_records' that needs to be set with higher value as ServiceNow takes it as 10,001 by default. I want to know if setting this value to a higher value is a good approach to follow in order to achieve my requirement. Do you have any idea on that?

Thanks for your help.

[Bhuvan]

@Sangeetha_Jegan 

I reviewed the BR code and this would need additional filtering or change in logic. I will check and provide more information.

Thanks,

Bhuvan