DASL Filter function?

Status
Not open for further replies.
M

Mark B

I am trying to figure out how to use the "undocumented" Today(S) function.

Basically I have about 10 search folders that make use of most of these:

%yesterday

%today

%tomorrow

%last7days

%next7days

%lastweek

%thisweek

%nextweek

%lastmonth

%thismonth

%nextmonth

So I am also hoping that these functions also allow for passing of a (S)

seconds parameter. The reason I am hoping to do this is to pre-adjust my UTC

DateTime field "MileStone1UTC" so that it will be normalized into local

date/time.

e.g.

"http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/MileStone1UTC"

= Today(46800)

e.g.

"http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/MileStone1UTC"

= Tomorrow(46800)

However it doesn't work. Anyone know what will here?

Maybe if I knew what Today(0) equated to I could use that if it was an exact

time such as Saturday, January 30, 12:00 AM.

Then I could use:

MileStone1UTC >= Today(0+46800) AND MileStone1UTC <

Today(0+46800+(24*60*60))

where 46800 is 13 hours in my case -- that is the UTC offset from local

time.

Here's the article I was referencing:

=================================================================================

http://blogs.msdn.com/andrewdelin/archive/2005/08/08/448882.aspx

-Snip -

Outlook SQL DASL syntax - an update

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:

(

("http://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/811c000b"=0) AND(NOT("http://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/81050040" IS NULL)) AND("http://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/81050040" <= today(864000)))When applied to a task folder, this shows 'notcompleted' tasks that have aDue Date set in the next 10 days from today.Thissame technique should be useful for filtering on received date (etc) onemailfolders and others.-Snip end-=================================================================================
 
I believe that Today(n) is the only one that allows that, but with

undocumented things they aren't documented and they can't necessarily be

relied on in all versions. I think Today(n) works across the board, but I've

never tested it.

I think you're going to have to test what Today(0) is for you. My guess is

that it's not something you would be able to rely on as a hard-coded value

except for users in the exact same time zone as you. If the time zone

changes I'd expect the offset value to change. I'd also expect it to change

with daylight savings time changes. My impression is this is not the best

way to go if you intend to distribute your code.

What I'd do is instead massage the data in that user defined field so it's

correct. Convert it to local time before you store it in the data field, or

run one-time code to convert existing data and you're done and don't have to

worry about this sort of thing.

"Mark B" <none123@none.com> wrote in message

news:eTn6zyEpKHA.1544@TK2MSFTNGP02.phx.gbl...
> I am trying to figure out how to use the "undocumented" Today(S) function.
> Basically I have about 10 search folders that make use of most of these:

> %yesterday
> %today
> %tomorrow
> %last7days
> %next7days
> %lastweek
> %thisweek
> %nextweek
> %lastmonth
> %thismonth
> %nextmonth

> So I am also hoping that these functions also allow for passing of a (S)
> seconds parameter. The reason I am hoping to do this is to pre-adjust my
> UTC DateTime field "MileStone1UTC" so that it will be normalized into
> local date/time.

> e.g.

> "http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/MileStone1UTC"
> = Today(46800)

> e.g.

> "http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/MileStone1UTC"
> = Tomorrow(46800)

> However it doesn't work. Anyone know what will here?

> Maybe if I knew what Today(0) equated to I could use that if it was an
> exact time such as Saturday, January 30, 12:00 AM.

> Then I could use:

> MileStone1UTC >= Today(0+46800) AND MileStone1UTC <
> Today(0+46800+(24*60*60))

> where 46800 is 13 hours in my case -- that is the UTC offset from local
> time.

> Here's the article I was referencing:
> =================================================================================
> http://blogs.msdn.com/andrewdelin/archive/2005/08/08/448882.aspx

> -Snip -

> Outlook SQL DASL syntax - an update
> 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:

> (
> ("http://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/811c000b"=0)
> AND(NOT("http://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/81050040"
> IS NULL))
> AND("http://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/81050040"
> <= today(864000)))When applied to a task folder, this shows 'notcompleted'
> tasks that have aDue Date set in the next 10 days from today.Thissame
> technique should be useful for filtering on received date (etc)
> onemailfolders and others.-Snip
> end-=================================================================================
 
Yes I tend to agree.

The only downside to creating an additional user-defined field called

MileStone1LocalTime is if the add-on is stored on somebody'slaptop and they

tend to be a jet-setter (e.g. travelling salesperson) and frequently enter

different timezones. Then the hard-coded local time is no longer relevent.

I suppose the way around this is to store the UTC offset in settings and

check that it is the same on Addin startup. If it's not I'd probably need a

messagebox saying "time-zone adjustments need to be made -- this can take a

few minutes." and then proceed to loop through all emails in all folders,

updating the user-defined field value and saving each one. This would need

to be on the main thread since if I tried to do it in the background it

would appear to hang.
<kenslovak@mvps.org> wrote in message

news:u2AmGkNpKHA.1548@TK2MSFTNGP02.phx.gbl...
> I believe that Today(n) is the only one that allows that, but with
> undocumented things they aren't documented and they can't necessarily be
> relied on in all versions. I think Today(n) works across the board, but
> I've never tested it.

> I think you're going to have to test what Today(0) is for you. My guess is
> that it's not something you would be able to rely on as a hard-coded value
> except for users in the exact same time zone as you. If the time zone
> changes I'd expect the offset value to change. I'd also expect it to
> change with daylight savings time changes. My impression is this is not
> the best way to go if you intend to distribute your code.

> What I'd do is instead massage the data in that user defined field so it's
> correct. Convert it to local time before you store it in the data field,
> or run one-time code to convert existing data and you're done and don't
> have to worry about this sort of thing.

> >

>

> "Mark B" <none123@none.com> wrote in message
> news:eTn6zyEpKHA.1544@TK2MSFTNGP02.phx.gbl...
> >I am trying to figure out how to use the "undocumented" Today(S) function.
> >Basically I have about 10 search folders that make use of most of these:
>

>> %yesterday
> > %today
> > %tomorrow
> > %last7days
> > %next7days
> > %lastweek
> > %thisweek
> > %nextweek
> > %lastmonth
> > %thismonth
> > %nextmonth
>

>> So I am also hoping that these functions also allow for passing of a (S)
> > seconds parameter. The reason I am hoping to do this is to pre-adjust my
> > UTC DateTime field "MileStone1UTC" so that it will be normalized into
> > local date/time.
>

>> e.g.
>

>> "http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/MileStone1UTC"
> > = Today(46800)
>

>> e.g.
>

>> "http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/MileStone1UTC"
> > = Tomorrow(46800)
>

>> However it doesn't work. Anyone know what will here?
>

>> Maybe if I knew what Today(0) equated to I could use that if it was an
> > exact time such as Saturday, January 30, 12:00 AM.
>

>> Then I could use:
>

>> MileStone1UTC >= Today(0+46800) AND MileStone1UTC <
> > Today(0+46800+(24*60*60))
>

>> where 46800 is 13 hours in my case -- that is the UTC offset from local
> > time.
>

>
>
>> Here's the article I was referencing:
> > =================================================================================
> > http://blogs.msdn.com/andrewdelin/archive/2005/08/08/448882.aspx
>

>> -Snip -
>

>> Outlook SQL DASL syntax - an update
> > 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:
>

>> (
> > ("http://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/811c000b"=0)
> > AND(NOT("http://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/81050040"
> > IS NULL))
> > AND("http://schemas.microsoft.com/mapi/id/{00062003-0000-0000-C000-000000000046}/81050040"
> > <= today(864000)))When applied to a task folder, this shows
> > 'notcompleted' tasks that have aDue Date set in the next 10 days from
> > today.Thissame technique should be useful for filtering on received date
> > (etc) onemailfolders and others.-Snip
> > end-=================================================================================

>
 
I think you're overthinking this.

If a user is in Florida and their settings are correct for local time zone

the data is stored as UTC, compensated by the local time zone offset. If the

user flies to California and they want UTC times to be correct for that

local time zone they can set the Outlook time zone, add an alternate time

zone and/or change the Windows time zone. Then everything is correct for

local time again.

This is the exact same thing for every single date field in every Outlook

item. If when the date values are entered the time zones are correct the

data is correct in UTC and that's all you can ever expect. If the user

changes time zones and doesn't update their settings every single one of

their date fields will be off by some offset to local time.

"Mark B" <none123@none.com> wrote in message

news:uNDF7PRpKHA.3776@TK2MSFTNGP04.phx.gbl...
> Yes I tend to agree.

> The only downside to creating an additional user-defined field called
> MileStone1LocalTime is if the add-on is stored on somebody'slaptop and
> they tend to be a jet-setter (e.g. travelling salesperson) and frequently
> enter different timezones. Then the hard-coded local time is no longer
> relevent.

> I suppose the way around this is to store the UTC offset in settings and
> check that it is the same on Addin startup. If it's not I'd probably need
> a messagebox saying "time-zone adjustments need to be made -- this can
> take a few minutes." and then proceed to loop through all emails in all
> folders, updating the user-defined field value and saving each one. This
> would need to be on the main thread since if I tried to do it in the
> background it would appear to hang.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
M DASL Filter function? Outlook VBA and Custom Forms 3
M Today(S) function in DASL Filter Outlook VBA and Custom Forms 1
M Today(S) function in DASL Filter Outlook VBA and Custom Forms 1
P Prevent Outlook 2016 from using DASL filter Using Outlook 4
E DASL Filter doesn't work when defined programatically Outlook VBA and Custom Forms 1
B How to create the customized Outlook SQL DASL for selected days? Outlook VBA and Custom Forms 2
U Drops filters for published views in public folders.Inserts DASL Outlook VBA and Custom Forms 1
R unable to filter tasks when start date is on or before today Using Outlook 3
O Filter-Query Builder-Description - what field name to use? Using Outlook 4
W Macro to Filter Based on Latest Email Outlook VBA and Custom Forms 6
Jennifer Murphy Spam filter not working Using Outlook 13
Y Filter unread emails in a search folder vba help Outlook VBA and Custom Forms 0
M How create a Rule to filter sender's email with more that one @ sign Using Outlook 1
M Sorting by Day in Date Column Advanced Filter BCM (Business Contact Manager) 1
Z Task Filter Not Working When I add too many criteria Using Outlook 0
M Filter Conversations - Filter Out Single Emails Outlook VBA and Custom Forms 3
B Looking to filter (or just find/search) for only messages that the sender has sent more than 1 messa Using Outlook 2
B Wanting to run a script that will filter any body that has a russian link in it. Outlook VBA and Custom Forms 5
C Filter/Search emails sent to internal Exchange address only Using Outlook 2
e_a_g_l_e_p_i Is there a good third party SPAM filter that intergrates with Outlook 2010 Using Outlook 7
P How to filter primary contacts in business contact manager BCM (Business Contact Manager) 0
O Outlook Web Access - how to disable spam filter Using Outlook 6
G How to filter by the format of an email address Using Outlook 6
S Mail filter recieved before the last 2 working days Using Outlook 1
Liza Creating a rule in outlook to filter messages Using Outlook 0
F Adding textbox filter to listbox? Outlook VBA and Custom Forms 2
Diane Poremsky Filter and Save Contacts to a CSV File Using Outlook 0
Diane Poremsky Filter and Save Contacts to a CSV File Using Outlook 0
Fozzie Bear HomeTab Email filter "Greyed Out" in Outlook 2013 Exchange Server Administration 3
W Filter condition "contains"/"doesn't contain" doesn't always work Using Outlook 10
B Conditional Formatting:How to use >1 value in a filter Using Outlook 7
J Outlook VBA for Email Filter Outlook VBA and Custom Forms 2
Emerogork How do I set up an "Incoming mail filter" Using Outlook 12
M Filter today's mail Using Outlook 2
M trying to disable junk email filter. completely. Using Outlook 4
J Task Filter for Status OR One Month Using Outlook 4
R Filter Views by Category Using Outlook 2
D Junk Mail filter Using Outlook 1
S Filter by accounts but communicate to all linked contacts BCM (Business Contact Manager) 1
D What is this SQL filter that is applied to my Inbox? Using Outlook 6
J Outlook Contacts: How to filter contact phone numbers from a cti request Using Outlook 1
H Two accounts, Junk email filter only works on one account, office 2010, pop3 Using Outlook 5
D How Would I Write This Filter? Using Outlook 3
N Filter end date works differently in OL'07/OL'10 vs OL'03? Hello?! Using Outlook 12
williamlambton Filter which removes Display Names from incoming emails. Using Outlook 2
J How to filter by address fragment (e.g. "@domain.com") with advanced find? Using Outlook 2
L Need a good email filter Using Outlook 2
P BCM Review & Filter BCM (Business Contact Manager) 1
M Create search folder filter that converts UTC time to local? Outlook VBA and Custom Forms 9
H Outlook 2007 SQL syntax to filter views of tasks and todo items Outlook VBA and Custom Forms 1

Similar threads

Back
Top