Left outer join in Database View

[Community Alums]

Hi All,

I am trying to build a report which gives me groups which has no members in it. We do not have a custom field which will store number of members in a group, so I decided to join two tables and then create a report

Two tables are sys_user_group and sys_user_grmember.

I have put Group table with order 100 and Group member table as order 200.

Where clause in group table as Group_name NOT IN Groupmem_group_name. Group field in Group member table is a reference field.

The above where clause was my initial one, since I did not get any records, I decided to play around and was trying to atleast get the records present in both. so I modified where clause as

Group_name = Groupmem_group_name, but I got no records. I have enabled left join in Group table.

Could anyone please tell me where I am going wrong?

Thank you

[User166992 · Accepted solution]

Hello Vishnu,

You can configure database view as per below screenshot:

Also, define before query business rule on the database view table as below:

var queryStr = 'grp_active=true^grpmem_userISEMPTY';
current.addEncodedQuery(queryStr);

Best Regards,

jai