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.

[Brian Lancaster]

In database view where cause you do not use a dot to get to the fields but rather an underscore. I'm note sure if it will make a difference since you are looking at a list but your where clauses should look more like this.

usr_sys_id IN (recovery_user)

[Sangeetha_Jegan]

Hi @Brian Lancaster 

   Thank you for your response.

Unfortunately, it didn't make any difference because I have tried both ways before. Also the current 'where' clause I have for user table which uses dot to walk to the field is workingv fine: usr.active=true

Thanks!

[Brian Lancaster]

Like I said I wasn't sure if this was going to make a difference but with database view you should defiantly user underscores as the correct syntax to look at the fields in the table. I have seen many posts where a database view was not working the the fix was to remove the dot and replace it with an underscore. You may want put in a support case to see if this is even possible to do a where clause when a field from one of the tables is a list.

[Bert_c1]

You are lucky, as 'active' is the name of the column in the database table. As Brian has stated multiple times '_' should be used. There are many cases where '.' won't work, specifically for task and it's child tables.  Just look at OOB database view definitions.