User created boolean fields don't work in database views

[Robert Campbell]

My user created boolean fields don't work in a database view.

Table	Active	Order	Left Join	Variable Prefix	Where Clause
incident	true	100	false	i1
incident	true	200	false	i2	i1.sys_id = i2.sys_id && i2.active = 1 && (i2.priority = 1 || i2.priority = 2) && i2.u_major_incident = 1

If view's where clause doesn't have the last line in red it works just fine.  If it does have the line in red, it gives no results although it should give many.

I've tried i1.sys_id = i2.sys_id && i2.u_major_incident = 1 and that also has no results.

I've also tried using && i2.u_major_incident = true and that doesn't work either.

If I change everything from '.' to '_' I get results but not the right results.  It returns everything

(i1_sys_id = i2_sys_id && i2_u_major_incident = 1 or anything (0, true, false))

[sushantmalsure]

I see there are 2 places where you have used boolean field under this where clause.

1.  i2.active = 1 : Active field is also has value true/false

2. i2_u_major_incident and this custom one as well.

So change at both the places as true or false as per need & try testing again.

Also just a suggestion (should not impact as such) but use either '.' or '_' in entire where clause while referring to fields in view table.

[Robert Campbell]

I did all those combinations.  If I create the view w/o i2_u_major_incident but with i2_active = 1; that works correctly.  If I create the view w/o i2_active = 1 but with i2_u_major_incident = 1; that doesn't work correctly. It shows all of the records regardless of the value for i2_u_major_incident.

There seems to be some issue with using user created fields.  I tried another field just to test, i2_u_enterprise_reportable = 1 and that has the same result as i2_u_major_incident = 1.

[sushantmalsure]

just do not use 1 or 0 value in where clause against the boolean type fields, it should be true or false.

Let me know if it still dont work

[Robert Campbell]

True false doesn't work either.  1 and 0, true and false both work with oob fields but they aren't working with user created fields.