Doing more with Outlook filter and SQL DASL syntax
When I returned from an overseas trip earlier this year I needed to hack through a pile of emails that had accumulated. So I explored the Outlook filter search syntax (DASL) to see what could be achieved.
(I am using Outlook 2003. I know some of this works with previous versions of Outlook.)
Firstly, I recommend you add the [Filter...] button to a toolbar in Outlook. To do this, right-mouse-click on an empty area of the toolbar area at the top of Outlook, and select Customize from the menu. Pick the Commands tab and then select View in the Categories list. On the right, scroll down the Commands list until you see 'Filter' and then drag this entry up, up and away to a toolbar spot where you'd like it. You should now have a Filter button in your toolbar at the top of Outlook. You can close the Customize dialog.
The Filter dialog is a somewhat hidden but powerful tool for finding all kinds of Outlook items. It is organised as four tabs: Messages, More Choices, Advanced and SQL. I recommend you play with this dialog if it's not familiar to you. When you apply a Filter on a folder, you will see a small legend above your email (on the right) saying 'Filter Applied' - a hint that not all items are being shown! You use the Clear All button in the Filter dialog to remove the filter so you can see everything again.
I prefer to use the Advanced and SQL tabs of the Filter dialog because you can establish very specific queries that meet your needs. You will need to check 'Edit these criteria directly' to enter SQL queries. Switch to the SQL tab and try the following.
Examples
To find email from a specific person (for example, your manager), try this:
"urn:schemas:httpmail:fromname" LIKE '%Gerard O''Driscoll%'
Note the use of double ' which escapes the apostrophe in the name.
To find all those Declined, Accepted and Tentative meeting responses, try this:
NOT " https://schemas.microsoft.com/mapi/proptag/0x001a001e " = 'IPM.Note'
This finds stuff that isn't email. I found this useful on both Sent Items as well as Inbox.
To find items that weren't sent directly to me, try this:
NOT
("urn:schemas:httpmail:displayto" LIKE '%Andrew Delin%'
OR "urn:schemas:httpmail:displaycc" LIKE '%Andrew Delin%')
This shows items where I am not on the To: line nor the Cc: line.
To show items that you haven't read:
"urn:schemas:httpmail:read" = 0
You either haven't read these items, or you have marked them Unread again.
This appears to find mail that came from outside Microsoft:
NOT "urn:schemas:httpmail:fromemail" LIKE '%microsoft%'
You will need to change the company name in this filter for your own details, and test thoroughly. It may not behave the same way.
This seems to find internal mail (from within Microsoft) which wasn't addressed to me:
"urn:schemas:httpmail:fromemail" LIKE '%microsoft%'
AND NOT
("urn:schemas:httpmail:displayto" LIKE '%Andrew Delin%'
OR "urn:schemas:httpmail:displaycc" LIKE '%Andrew Delin%')
Note I am checking both To and Cc addresses. To try this, you'll need to substitute your company name in the first clause. And test that it works.
It would be nice to find emails that have attachments. BUT:
"urn:schemas:httpmail:hasattachment" = 1
-this DOESN'T work the way you expect, because it seems in-line pictures embedded in certain email formats are considered attachments :o(
Looking for items with normal or low priority:
"urn:schemas:httpmail:importance" <= 1
Items with no flag set:
NOT "urn:schemas:httpmail:messageflag" > 0
Take care with flags, because it appears more than one attribute composes the email flag functionality. You can for example test for specific color flags being set; this tests for Purple flagged items:
" https://schemas.microsoft.com/mapi/proptag/0x10950003 " = 1
And this looks for NO color flag being set:
NOT " https://schemas.microsoft.com/mapi/proptag/0x10950003 " > 0
Beware, because I found that "NOT ... > 0" is not the same as "= 0". With the latter, you won't get the list of unflagged items you might expect.
Email sent to one of the groups (distribution lists, DLs) which I belong to:
"urn:schemas:httpmail:displayto" LIKE '%Australia Consulting%'
OR
"urn:schemas:httpmail:displaycc" LIKE '%Australia Consulting%'
This tests both the To: and Cc: address fields. Note this is the long name of the group, not the short alias name (8 characters). You could easily add more tests to make a filter that shows 'emails to Australian groups I belong to'.
To find email items you have replied to, you might attempt a field query like "Tracking Status equals Replied" which produces this syntax in the SQL tab:
"https://schemas.microsoft.com/mapi/id/{0006200B-0000-0000-C000-000000000046}/88090003" = 7
BUT I found this DOESN'T work because email tracking is usually disabled between Outlook and Exchange, to reduce sync overhead (this was the feature where Outlook recorded that you had replied to individual items). Instead, a reasonable proxy for this is to look for Inbox items which contain your name in the body of the mail (when you reply, the mail client inserts something like From: Andrew Delin in the body):
"urn:schemas:httpmail:textdescription" LIKE '%Andrew Delin%'
Putting it all together
Unread items received last month, not sent directly to me:
NOT
("urn:schemas:httpmail:displayto" LIKE '%Andrew Delin%'
OR "urn:schemas:httpmail:displaycc" LIKE '%Andrew Delin%')
AND
%lastmonth("urn:schemas:httpmail:datereceived")%
AND
"urn:schemas:httpmail:read" = 0
Non-urgent Inbox mail from inside Microsoft which is 3+ months old, not flagged, not addressed to me, and which is either 'unread' or which I didn't reply to (proxy):
"urn:schemas:httpmail:fromemail" LIKE '%microsoft%'
AND
"urn:schemas:httpmail:importance" <= 1
AND
"urn:schemas:httpmail:datereceived" < '1/02/2005 12:00 AM'
AND NOT "urn:schemas:httpmail:messageflag" > 0
AND NOT
("urn:schemas:httpmail:displayto" LIKE '%Andrew Delin%'
OR "urn:schemas:httpmail:displaycc" LIKE '%Andrew Delin%')
AND (
"urn:schemas:httpmail:read" = 0
OR
(NOT "urn:schemas:httpmail:textdescription" LIKE '%Andrew Delin%')
)
If you want to try this, update the date constant (see datereceived) to something appropriate, as well as changing %microsoft% to your company and removing my name! Please test the result thoroughly.
Views
Once you have some queries you like, you can define these so they're available in your Views drop-down list. To create a new view, use the View menu / Arrange By / Current View / Define Views / New button. Choose 'Table' and then you'll see several buttons to setup the View, including Filter - which you'll recognise as the same dialog from above. Enter your carefully crafted syntax and the filter will activate whenever you select this view on your folder. I have defined views for my main group memberships and external email so I can quickly filter my Inbox.
Other notes
Be patient with the Outlook Filter dialog. Sometimes you need to use Clear All then OK to get the full list of items to appear in your Inbox, before trying another filter query.
I found it was important to test my filter expressions carefully. Sometimes 'NOT' doesn't produce the opposite list of what you're seeing, and some fields aren't populated the way you expect (for example, there are several flag attributes - see above; there are also a number of similar-sounding email address fields to check and you may not get the results you first guess at).
A syntax reference for using DASL through the Outlook filter isn't easily found. I tried looking in the Exchange SDKs and while I got a list of attributes, it wasn't very Outlook-friendly. For example:
There's also a list of Outlook IPM message types here, if you want to search for specific mail item types (eg Meeting requests):
I did find some bits in Thomas Rizzo's book on pages 197 and 701 ("Programming Microsoft Outlook and Microsoft Exchange 2003").
If you want to do date dynamic date calculations in Outlook filters, you will need to use the today(S) function with a positive or negative offset in seconds. I have written about this here (thanks Patrick!) . Otherwise there is a fixed set of date functions like '%lastmonth' or you can enter literal dates such as <= '1/1/2005 12:00 AM'. Here is a list of these fixed date functions - for the syntax, just use the Advanced tab in the Outlook Filter dialog, and add the mail field called 'Received' (Outlook will display something like 'Received yesterday|today|tomorrow|in the last 7 days ...' etc. If you switch to the SQL tab, it will show you the syntax to use.
%yesterday
%today
%tomorrow
%last7days
%next7days
%lastweek
%thisweek
%nextweek
%lastmonth
%thismonth
%nextmonth
I hope the above helps. If you try any of the above ideas, please be sure to test the results thoroughly.
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm
Comments
- Anonymous
May 20, 2005
Hi,
Tried to find a way (sql filter) to only show emails (and journal items, like phone calls and documents done) which are older than i.e. 3 months (accepting to put the actual date minus 3 month). Could not manage it. The idea behind is to know the contacts, where last contact is older than i.e. 3 month.
Any solution would be great - Thanks Pet
PS: E-mail items would refer to SENT DATE;
Journal items to START Date
PPS: Already would be happy if only the SENT DATE would work ... - Anonymous
June 01, 2005
The comment has been removed - Anonymous
July 26, 2005
Ich bin schier sprachlos... Outlook ist ja manchmal zugegebenermaßen ein schwer zu reitendes Pony und... - Anonymous
August 07, 2005
Here's an update to my previous blog entry on Outlook SQL DASL syntax.
I had some great feedback from... - Anonymous
June 18, 2007
PingBack from http://www.indiangeek.net/?p=194 - Anonymous
June 29, 2007
PingBack from http://catholicgeek.stblogs.com/2007/06/29/gtd-and-outlook-views/ - Anonymous
July 17, 2007
FYI: To get the filter button (isn't on the tool bar by default and right-clicking a search folder doesn't show the SQL tab in Outlook 2007) folow these steps on - Anonymous
August 30, 2007
PingBack from http://www.fishbrains.com/posts/217 - Anonymous
July 15, 2008
PingBack from http://austen.digestvidsonline.info/scriptarchivesoutlookbyreceiveddate.html - Anonymous
January 21, 2009
PingBack from http://www.keyongtech.com/1761147-birthday-dasl-filter - Anonymous
January 22, 2009
The comment has been removed