Description:

How do I create an expression that will return the results I expect in a power filter? I created an expression and it gives me data that is unexpected.

Steps to Reproduce:

1. Home > Projects List View > Expand Filter Section > click ‘Show All’ button > click [Build Power Filter]

2. Select Project > Status Indicator = Yellow > click ‘Add’

3. Select ‘AND’ > Project > Manager = ‘User, Resource 7’ > click ‘Add’

4. Submit the expression and view the listing > results are shown as expected

5. Click on the Power Filter expression to update it

6. Select ‘OR’ > Project > Manager = ‘User, Resource 9’ > click ‘Add’

7. Submit the expression and view the listing > results are NOT shown as expected, it shows Resource 9 , with status other than yellow

8. Here is the expression created by the power filter builder:

Evaluated Expression: ( ( ( Project Status Indicator = ‘Yellow’ ) and ( ProjectManager = ‘User, Resource7’ ) ) or ( Project Manager = ‘User, Resource9’) )

Expression: ( project.status_indicator ==lookup(“INVESTMENT_STATUS_INDICATOR”, “2”) ) && (project.manager_id == lookup(“BROWSE_PROJMGR”, “5004006”) )|| ( project.manager_id == lookup(“BROWSE_PROJMGR”,”5004008″) )

We want the filter to pick all projects with Status Indicator = Yellow and then give us only those projects that are managed by Resource 7 or Resource 9. What is wrong with this expression?

Solution:

The power filter expression uses logical expressions that may need additional parenthesis for proper evaluation of the expression.

The sequence in creating the power filter affected the construction of the parenthesis.

In this case, the first expression is ‘grouped’ with parenthesis for evaluating (Status Indicator = Yellow and Manager = Resource 7)and then later the ‘OR’ condition is added, so it is searching for only records with Manager = Resource 9 and the records for Resource 7 also have a criteria of Status Indicator = Yellow.

When building a Power Filter Expression and you are modifying an existing expression, be sure to check how the expression is constructed with parenthesis. If you are building this expression and you know all the parameters, it is easier to build it in the following sequence:

Steps:

1. Home > Projects List View > Expand Filter Section > click ‘Show All’ button > click [Build Power Filter]

2. Select Project > Status Indicator = Yellow > click ‘Add’

3. Select ‘AND’ > Project > Manager = in the browse window pick all the managers such as ‘Resource 7’, ‘Resource 9’ (this is a multi-select lookup)

4. Submit the expression and view the listing > results are shown as expected

Here is the expression created by the power filter builder:
Evaluated Expression: ( ( Project Status Indicator = ‘Yellow’ ) and (( Project Manager = ‘User, Resource9’ ) or ( Project Manager = ‘User,Resource7’ ) ))

Expression: ( project.status_indicator ==lookup(“INVESTMENT_STATUS_INDICATOR”, “2”) ) && (project.manager_id == lookup(“BROWSE_PROJMGR”, “5004008”)|| project.manager_id == lookup(“BROWSE_PROJMGR”,”5004006″) )