And/Or Logic in KnowledgeSync Queries

Here’s a question that comes up fairly frequently in the KnowledgeSync Support department:

“How do I specify Boolean logic in a query without manually editing the SQL?”

Let’s use a real-life example. Say you’ve got a query in which you wish to look for records that meet the following criteria:

• Priority must be “High” AND

• Status must be “Open”, “In Process”, or “Left Message” AND

• Response Date must be equal to today’s date AND

• Call Type must be “install” OR Call Urgency must be “A1”

In other words, your query needs to look for all of conditions one, two, and three but also for conditions four OR five.

The first three criteria are easy – you would configure them like any normal conditions on the query’s “Filters” tab and specify the “and” condition at the end of each of them

The challenge comes with criteria #4 – because it’s looking for either of two conditions – notice the “OR” in the middle of this statement.

Here’s how you would configure that 4^th criteria:

1. Choose the “Call Type” field as your 4^th filter
2. In the Column Name field, insert an open parenthesis before the “Call Type” field -- so it appears as (dbo.Call.Type.
3. Choose an operator of equal to and a compare value of install.
4. Go to the “And/Or” field for this filter and choose “Or”.
5. Choose the “Call Urgency” field as your 5^th filter.
6. Choose an operator of equal to
7. In the compare value field, key in: ‘A1’)
8. Change the “Type” value of this last filter to “Literal”

The key to this whole process is how you end your Boolean clause – in this example, the clause ends by checking for a value of “A1” but we have been careful to enclose “A1” in single quotes, we have added the close parenthesis after the ‘A1’ value, and we have changed the Type field to Literal.

Why have we surrounded the A1 value with single quotes? Since we’re adding the close parenthesis to the end of our compare value, we need to tell KnowledgeSync that the compare value is A1 – and not A1). Changing the Type of this filter to Literal is also necessary only when the compare value is a character field. By telling  KnowledgeSync to interpret the compare value “literally” KnowledgeSync will understand that the single quotes and close parenthesis are actually the end of a Boolean clause and not part of the compare value itself.

And don’t forget that the compare value can not only be a specific value (such as A1), it can also refer to another database field value, or even a calculated field based on one or more other fields in the database.