Partager via

Outlook SQL DASL syntax - an update

  • Article

Here's an update to my previous blog entry on Doing more with Outlook filter and SQL DASL syntax.

I had some great feedback from my previous posting, there is obviously a need for more information about how to "do stuff" with Outlook filters.

Special thanks to Patrick for a very useful message, which showed me how to use dynamic dates in Outlook filter queries. Previously I thought this was impossible! Patrick's trick (:o) is to use the today(S) function with a parameter in seconds (S). The parameter gives a positive or negative offset from today and can be used to check the Due Date of tasks (or any other date test you like).

Here is Patrick's example which he uses on the Tasks folder:

(
(" https://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/811c000b " = 0)

 AND

  (NOT(" https://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/81050040 " IS NULL))

 AND

  (" https://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/81050040 " <= today(864000))
)

When applied to a task folder, this shows 'not completed' tasks that have a Due Date set in the next 10 days from today.

This same technique should be useful for filtering on received date (etc) on email folders and others.

Here's another tip. Finding articles about Outlook Filter programming is tricky, but if you search for one of the following using MSN Search or Google, you will find several articles to check:

  https://search.msn.com/results.aspx?q=81050040

  https://search.msn.com/results.aspx?q=811c000b

These numbers come from the XML schema for the Outlook SQL syntax. Sometimes using a very specific identifier in this way can deliver accurate hits.

My previous blog entry is here (Doing more with Outlook filter and SQL DASL syntax).

If you try any of these ideas, please be sure to test the results thoroughly.

-AD

Technorati profile

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
    August 11, 2005
    I was trying to find a way to filter messages based on the senders properties. I am not sure if this is possible - the filter SQL can hardly do a table join.

    I was trying to add properties onto the sender like this and it seems to pass the parser fine, but it doesnt work at all.
    "urn:schemas:httpmail:sender:company" LIKE '%QA%'
    "urn:schemas:httpmail:sender:company" LIKE '%microsoft%'


    If you have any ideas how I can make this work it woudl be appreciated. Also is there any way to link to an external database from inside the filter SQL?
  • Anonymous
    August 30, 2007
    PingBack from http://www.fishbrains.com/posts/217