Database view showing some fields empty when they are really not empty

[mjrinker]

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:

[SanjivMeher]

Did you also add those fields to the view field under the view table?

[mjrinker]

I think so. Do you mean this, or something else?

[mjrinker]

Do you mean this, or something else?

[hkphillips42]

I had almost the exact same issue today. For whatever reason, recreating the View and removing Uppercase characters from my variable prefixes solved the problem. I also had to take out underscores from the variable prefix as well.

I've seen a few people have weird issues with database views and simply recreate them and they work. 

Good luck.

// Broken (empty fields)
orderCase.sys_id=metric.id


// Working
ordercase.sys_id=metric.id