Hi all. Dbase Newbie here. I am trying to create the below view. I need to retrieve every user with an assigned group and what roles the groups have. I will also need data such as the department and other. I intended to further granulate my data inside a report, using this view.
However, I get no records returned in the below view.
help please
Stacylen
For sys_user my fields are department, sys_id, roles, user_name, name
For sys_user_grmember, my fields are user, group, sys_id
For sys_group_has_role, my fields are sys_id, role, group
Hi Stacylen,
I've tried the same in my PDI and am seeing the expected records when clicking the Try It Related Link
It's more customary to use prefix_fieldname rather than prefix.fieldname, but both seem to work in this case. Make sure you don't have any trailing spaces in the prefixes (prefices?). Another general troubleshooting step is to remove one of the tables to see if you can get it working with 2, then add in the third.
Also consider not including the sys_user table in this Database View. From what you've shared, you aren't doing any outer/left joins, such as if you wanted to see all users regardless of group membership, and since User is a reference field on the group member table, you can dot-walk to include or filter a list view or report on any field on the user table, so adding the table to the Database View doesn't gain you anything. This is similar to needing to see or filter on something like Group Manager - you don't need to include the sys_user_group table in the view as you can get to the Manager field from the Group reference field on sys_user_grmember.
