HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

Filters with AND's and OR's


#1

Filters currently provide two types of statements: ALL criteria and ANY criteria, at the very top of the Filter Requests screen.

This seems to be a problem, if I want to create even somewhat complicated filters. For instance, if I want to have a filter that shows me OPEN requests with EITHER Reporting Tag A OR Reporting Tag B, it cannot be done.

The way the system works, you cannot specify AND or OR statements anywhere below the top line. Even if you use some of the is/is not functionality, you still can’t get this type of filter going.

What type of workaround is there for this problem?


#2

To make clearer what you cannot do with filtering currently…

You cannot create a filter through the GUI that finds you the following:

Open requests with reporting tag "category A/Tag 1"
AND
Open requests with reporting tag “category B/Tag 1”

Those filters must be separate filters, because if you have multiple categories (and who doesn’t?), a request cannot fulfill ALL of the above. And if you choose ANY, you’ll get all open requests, all requests in category A/Tag 1 (open AND closed!), all requests in category B/Tag one (open AND closed!)


#3

Hi Chad,

Yes, this is something we’ll be adding in a future release. For now though, you could use the “custom where sql” condition to do what you need. I’m not sure how technical you are, but it’s relatively easy if you know a little about SQL.

For instance, here’s how to do open requests with reporting tag of A or B.

xRequest IN (SELECT xRequest FROM HS_Request_ReportingTags WHERE xReportingTag = 13 OR xReportingTag= 10)

So here we’re doing a sub select to find all the requests with reporting tag ID’s of 13 or 10. Then returning those request ID’s and making sure one matching request ID’s are in our result set.

You would just need to replace the reporting tag ID’s with ones from the HS_Category_ReportingTags table.


#4

Excellent… problem for me is I don’t have direct access to the database… Or I don’t know how to get it. I have downloaded and installed MySQL Administrator, but being an MSSQL guy up to this point in my life, I have no idea how to connect to and utilize my helpspot database.

Thoughts?


#5

Update:
I can start the MySQL Administrator and the tray monitor tells me the HelpSpotMySQL service is running, but I’m not sure how to get in and view the data…

baby steps…


#6

Update 2:
I see this in the my.ini file
[mysqladmin]
user=root
password=’@@BITROCK_MYSQL_PASSWORD@@'
port=3306

MySQL Administrator is asking me for the following fields:

Server Host
Port
Username
Password

Whaddooeyeuse?


#7

Got in using Query Browser, good suggestion.

Not sure I know the password for root, or even if I do, I’m not getting in for some reason.

I was able to browse the tables w/Select statements, though, after I got in as user.

Thanks! Now my custom filter is returning exactly what i need.


#8

Great Chad! Thanks for the assist Andrew.