Database View Where Clause Confusion

[brandon_chamber]

Hello,

I'm trying to setup a very basic database view of the Service Catalog table (sc_cat_item). However, I'd like the view to contain only active items. When I try to setup the view, add the view table and setup a Where clause (cat_active = true), the resulting view returns all records in the table. Why is this? This seems like a simple enough "filter" clause....


Thanks,

Brandon

[Mariano Lemus]

Hi, I am trying to setup a business rule that acts on a database view, just like you suggested, but it seems I cannot even filter it at all, for example I am looking for thos which STATE = 3 (which are Closed Complete).

Can you please explain further how you would apply the script conditions in the business rule? STATE = 3 and Active = true.

Thank you

[josh_nerius]

Can you post the code you used in your Business Rule?

When you create a before query business rule, "current" represents the GlideRecord object referencing the target table (view) in this case.

You should be able to add as many query conditions to current as you need:

current.addQuery("fieldA", "valueA");

current.addActiveQuery();

etc.

I'm only guessing on your field names, but you probably need something like this:

current.addQuery("state", "3");

current.addActiveQuery();

See GlideRecord - ServiceNow Wiki (addActiveQuery())

[nireeshareddy]

A small correction :

I just wanna add this to your reply.

If someone is still not able to get the result. You might have missed the variable prefix.

This could be the answer:

current.addQuery("variable_prefix_state", "3");

current.addActiveQuery();

[brumiou]

the workaround is great, but have you tried to set the filter cat_active = 1 ?

It works for me

[guush]

I tried this (because I don't want to make onquery business rules) but it doesn't work for me. Has anyone else successfully tried this?