Business rule not working with database view

[mlklein]

I created a database view to join 3 tables for querying via the ODBC driver. The numerous exclusions I needed did not work via the Where clause in the views so I created a business rule to execute BEFORE Query with conditions that exclude data for specified fields (like unit = TAMUG). But when I run a report to test the rule on this database view, it doesn't work.  Does the rule not filter the data unless you script it?  Or is querying via a report not a good test?

[Jon Barnes]

I don't think BRs run on Database Views but could be wrong. maybe we can help get the where clause working?

[mlklein]

I thought BR before queries were functional based on this http://www.snc-blog.com/2014/08/07/a-view-on-database-views/

[Jon Barnes]

Yes you are right. they do run. may need to see your DB view, and BR to see if we can see anything.

[mlklein]

The working database view is this:

 

pm_project        100          pj            where (pj.state != '3' && pj.state != '7') &&

(pj.company != '8102e72f6f992100cfd1247cbb3ee474' && pj.company != '86b4d9006f70a100b5919e0cbb3ee4fd' && pj.company != 'd99f9e4a6fa0a100cfd1247cbb3ee4b9')

planned_task     100          pt            where pj.sys_id=pt.parent

 

project_status    300          pstat       where pstat.project = pt.parent

 

The part I couldn't get working was the field pt.top_portfolio != ''4c1462c36f1fe280b522db3bbb3ee42c' && pt.top_portfolio!='c72e96c5db9e7240f63af236bf9619a2').

 

I tried using the actual display value as well as using top_portfolio as part of pm_project (pj.top_portfolio).  I kept getting this error:

Unknown column 'pj.top_portfolio' in 'on clause'.

 

The field, top_portfolio, is in the attached graphic.

What am I missing with this field to get the data filtered in this view?