I'm working on creating a database view between two tables: sys_user_group and one of our own tables called u_duty_phone.
The fields I want to display are as follows:
From sys_user_group: Department, Portfolio, Manager, Tier
From u_duty_phone : Group, Duty phone
Here is the view tables config I'm using:
| Table | Order | Variable prefix | Where clause | Left join |
|---|---|---|---|---|
| u_duty_phone | 100 | dp | true | |
| sys_user_group | 200 | sg | (sg_name = dp_u_group) && (sg_type = '<a specific sys_id for assignment groups>' && sg_active = 'true') && (dp_u_active = 'true' && dp_u_primary = 'true') | true |
What I'm going for is all assignment groups that have a duty phone.
What this gives me is a set of records where the fields from u_duty_phone have data and the fields from sys_user_group are all empty. When I click on a record, however, the fields that showed as empty in the table are actually populated with data.
Furthermore, when I switch the order of the tables so that u_duty_phone is second, I get the opposite problem: the fields from u_duty_phone are empty and the fields from sys_user_group have data.
Also, if I set left join to false on both, I get 0 records.
Why do some of the fields appear empty in the table when in the record the are not empty?
Any help at all is greatly appreciated!
Here are some screenshots to help make sense of what I'm saying:
With the order set as shown in the above HTML table:
When I click on the first record:
When I switch the order of the tables so that u_duty_phone is second:
i duplicated your scenario and am able to see data from User Group and Duty Phone.
Here are screen shots showing the configuration and the result set:
