Complex query Business Rule

[TZRick]

Hi everyone,

I have a requirement to expand the scope of Requester/ESS Users to allow them to see not just Incidents where they are Caller/Opened By/Watch List, but also where they have management oversight of retail locations in their district.

I have the ACL done (added a new read Incident ACL alongside the stock ones), but I'm trying to build an incident query Business Rule..

Questions:   How do I add additional conditions OR'd to the stock conditions?   As a possible solution, am I able to create multiple query Business Rules to return multiple datasets...for instance, keep the stock rule active and just add another alongside?

Here is what the solution query should look like (simplified):

• ((NotNullQuery) AND (NotNullQuery) AND (Query)) OR
• (Caller=user OR Opened By=user OR Watch List=user) --> Stock incident query Business Rule

Any ideas as to how to approach this?

Thanks in advance.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

UPDATE:

I tried the following:

1.   Enabling both the stock Business Rule as well as the custom Business Rule.   The custom Business Rule worked, but when enabling the stock Business Rule, the results were again limited to stock limits.

2.   I've tried to add an OR condition to the stock Query Condition line, but am getting an error using that syntax (should work per the Wiki).

Error:   org.mozilla.javascript.EvaluatorException: Can't find method com.glide.db.conditions.QueryCondition.addOrCondition(string)

Code:  

var qc = current.addQuery("caller_id", curr_user).addOrCondition("opened_by", curr_user).addOrCondition("watch_list", "CONTAINS", curr_user).addOrCondition('encoded_query_string_here');

[jfarrer]

Before Query business rules allow you to add conditions to the query only. So if you're trying to open it up to a broader audience then you'll most likely need to modify the original rule to set up the OR condition.

You would need to add additional addOrCondition sections to it something like this:

original part of the query.addOrCondition("caller_id.manager", gs.getUserID());

If you need to go up the whole management chain you can either add a few of those to go up the to level that you need dot-walking each time or you can do a loop or something like that to assemble a list of all the managers and then check to see if the manager is in the list of user sys_id's.

[TZRick]

James Farrer

I appreciate the feedback.   It seems I updated the original post right as you were posting, so please see the update.   I have the management query worked out and operational, but now need to combine with the original.

The problem is that the original query is:

Query

- OR Condition

- OR Condition

What I am trying to accomplish is:

Query

- OR Condition

- OR Condition

- OR (Condition AND (Condition OR Condition))

Hopefully you can follow the logic and see the difficulty I'm running into.   I guess I can restate the query in a different way, but I haven't found something that works yet....so I'm stuck.

Thanks again.

[jfarrer · Accepted solution]

Sometimes when the query gets too complicated I go to a list of records and build the query with the condition builder at the top. If you include the logic from the stock business rule in your query as well then you can just add it all in one query with a simple addQuery('encoded_query_here'). It looks like that may be the best way to do it here. I know you can do the larger grouping of OR conditions that way.

[TZRick]

One more thing I tried:

(Condition AND (Condition OR Condition)) --> as encoded query

- OR Condition

- OR Condition

- OR Condition

When I did current.getEncodedQuery(), I only got the first line without the subsequent OR Conditions (i.e. (Condition AND (Condition OR Condition)) only)