10 Replies Latest reply on May 12, 2009 10:25 AM by Stu McNeill

    Use of AND and OR

    Rookie

      If in the criteria of a query you are building in the Query Designer, you use multiple ANDs and ORs, what is the order that they are processed?   For instance we have the following criteria:

      AND Current Assignment.Group.Title [tps_title] Is Equal to (A)

      AND Status.title [lc_title] Is Not Equal To (Closed)

      OR Current Assignment.Group.Title [tps_title] Is Equal to (B)

      AND Status.title [lc_title] Is Not Equal To (Closed)

      OR Current Assignment.Group.Title [tps_title] Is Equal to (C)

      AND Status.title [lc_title] Is Not Equal To (Closed)

       

      Usually this works OK for us - however we came upon one instance where the Group Title was 'C' and and the status was Closed.

       

      Marian

        • 1. Re: Use of AND and OR
          Jed SupportEmployee

          They are used sequentially, you can use the group option to change some of the ordering.

           

          --jed

          • 2. Re: Use of AND and OR
            LegoGuy SupportEmployee

            I have found the best way to visualize the queries is to see the OR statements as parenthesis.  So your query would evaluate as such:

             

            (

            AND Current Assignment.Group.Title [tps_title] Is Equal to (A)

            AND Status.title [lc_title] Is Not Equal To (Closed)

            )

            OR

            (

            OR Current Assignment.Group.Title [tps_title] Is Equal to (B)

            AND Status.title [lc_title] Is Not Equal To (Closed)

            )

            OR

            (

            OR Current Assignment.Group.Title [tps_title] Is Equal to (C)

            AND Status.title [lc_title] Is Not Equal To (Closed)

            )

            • 3. Re: Use of AND and OR
              Rookie

              I seem to be getting different answers based on whether I use Current Assignment.Group or Latest Assignment.Group.   Some incidents it seems do not have anything in the Current Assignment.Group value.   Is there some logic that happens to the Current Assignment.Group value when it changes status?

               

              Marian

              • 4. Re: Use of AND and OR
                aparker Employee

                Hi Marian,

                 

                There are specific rules about how the Current Assignment and Latest Assignment values are set, but before going into that, can you actually be sure that all processes do have a Group assignment in them. If they don't then the value will never be populated.

                 

                Andy

                • 5. Re: Use of AND and OR
                  Rookie

                  We are only using one Incident process, and the window used for Incident creation has Suggested Assignment automatically populated based on the category that the anaylst assigns to the incident.   The analyst can change that assignment, but they have to have something in that field.    I did a query in the database and found that Current Assignment does not always have data, but Latest Assignment does.

                   

                  Marian

                  • 6. Re: Use of AND and OR
                    LegoGuy SupportEmployee

                    CurrentAssignment is cleared when an object is closed.  So this will be blank for your changes and incidents once they're closed.  LatestAssignment is the most recent assignment, which is the same as CurrentAssignment as long as the incident/change is open.  Once it's closed the CurrentAssignment is blank and the LatestAssignment has the most recent assignment in it.

                    • 7. Re: Use of AND and OR
                      flallemand Apprentice

                      Also, if you use the complete action from an assignment the current assignment field is also blanked (even if your incident is still open).

                      • 8. Re: Use of AND and OR
                        aparker Employee

                        Although this action is shown on an asssignment window, it's not a good idea to do this at all. The end result is a process that has no assignment and therefore no ownership. Assignments should only be completed by the process coming to an end or it being re-assigned to another group or user.

                         

                        Andy

                        • 9. Re: Use of AND and OR
                          Rookie

                          Thanks to all who responded.    Nephi's reply helped me with this query, as well as Andy's.   I changed the query so that there was an "OR" at the first line and I changed it so it is using the Latest Assignment attribute.   That gave me the results that I was looking for.

                           

                          Marian

                          • 10. Re: Use of AND and OR
                            Stu McNeill Employee

                            Hi all,

                             

                            Just as an aside to this its worth mentioning the criteria functionality of Query Designer is being enhanced for version 7.3 (due end of July).  You will be able to customise how the criteria are grouped to give more control over how the query itself is run and make sure you get the results you're expecting.

                             

                            This will be especially useful when using ORs