cdo Filtering according to Categories

[Bert_Bert]

Hi,
i have a big trouble trying to filter only Messages having certain category.

I succeeded in filtering String, boolean or MessageClass types of fields.

For example string:
Set oFiltr = oMessages.Filter
oFiltr.Fields.Add myproptag, 8, val

and works perfect.

Also boolean works fine
oFiltr.Fields.Add name, 11, True


However if I want to filter Categories, neither of the following works:
oFiltr.Fields.Add proptagcateg, 8192, "category"
oFiltr.Fields.Add proptagcateg, 8, "category"
oFiltr.Fields.Add proptagcateg, 8192, field_categories_containing_my_one
oFiltr.Fields.Add proptagcateg, 8, field_categories_containing_my_one
the Messages.Count skips to zero instead of expected number

Do I have at least some workaround for the solution to work quickly enough
(I am afraid of Outlook object model, Restrict would work I guess) if there
happen to be more items in the given category ?


Please help me, there are only examples with strings but it is not possible
to find a single example for keyword types.


thanks a lot for any hint

 

[Ken Slovak - [MVP - Outlook]]

Keywords is what you're looking for. You'd use that with
"2903020000000000C000000000000046" as the GUID.

The "Create an Outlook Contact with CDO" example at
http://www.cdolive.com/cdo5p2.htm shows adding a category to a contact item
as an example of how to use that property.

 

[Bert_Bert]

thank you, good example
but I do not have problem adding keywords field or to update categories
The problem is, if I try to FILTER and do it the same way - it does not work.

My goal is in Outlook 2003 to
1. Filter according more criteria, mainly based on categories and also based
on user defined String fileds (which is not problem)
2. Sort the filtered collection according to user defined field (long or
date, not keywords)
3. and the to perform loop on the sorted list, or first X members only

the folder contains more than thousands items so iterating even with CDO and
comparing one-by-one is not good solution for me.

So far I have a workaround always an item gets updated with changed
categories, it also copies the same value to the "Categories2" which is
string type and then it is somehow able to filter.

But I would like to find more clear solution. This is not robust way for
some imports, non-standard manipulations with items, etc. I need either to
get CDO categories filtering wok, or at least to read somewhere that is ti
NOT POSSIBLE, or, to find some other way.

For example if I use Outlook objecty model (2003) can I also sort the
filtered collection?
Also I have some feeling it could be possible to work with items in SQL way
but I have no clue what library or what model is it.

And another question related to this: DASL filter is where in the object
model? Is it passed in Outlook object model or is it something in MAPI ?

I believe in Outlook 2007 object model it will be easier, but I do not like
and trust the last version for many reasons, mainly totally different user
interface. All people got used to the old one, so let's change everything :-(
to be able to sell it as a new version.

thanks a lot for any hint

 

[Ken Slovak - [MVP - Outlook]]

Filtering on categories has never been real reliable in my opinion. I prefer
to create a MAPI named property or to use a usually unused field such as
BillingInformation.

CDO knows nothing about DASL filters or syntax, only CDO prop tags. I don't
know why you're using CDO anyway, it's an outdated technology that hasn't
been updated in years and is barely supported by MS. I haven't even used it
in my own programs at all probably in 5 years.

What I'd recommend is using a 3rd party library, Redemption
(www.dimastr.com/redemption), which is a COM wrapper around Extended MAPI.
That will accept DASL syntax, is modern and has all the power of Extended
MAPI. For filtering a table that has thousands of items nothing beats a
filtered MAPITable, which is what Outlook uses under the hood. That's
probably an order of magnitude faster than CDO for something like that.

 

[Bert_Bert]

Ken, thanks a lot for a good tip.
I converted to the redemption and I have a good feeling about that wrapper.

However I have the same problem as before.
I have two ways how to filter with mapitable. The first one - SQL, seems to
work for me, it is somehow difficult to formulate the dasl properties, but
Categories filtering works so far.

However the second way, the Filter, Restrictioning method that is also
described at Redemption web page very clearly DOES NOT work for categories
but does very well work for Textual and other types.

I followed the hint regarding the Outlook spy to find IOut what Outlook uses
when he makes filtering according to categories. It showed me:

rt : RES_AND
res.resAnd :
cRes : 2
lpRes :
rt : RES_AND
res.resAnd :
cRes : 3
lpRes :
rt : RES_CONTENT
resContent :
ulFuzzyLevel : FL_FULLSTRING FL_IGNORECASE
ulPropTag : 0x8014 (0x8014101E)
lpProp :
ulPropTag : 0x8014 (0x8014001E)
Value : my_category_name_i_want_to_filter

rt : RES_CONTENT
resContent :
ulFuzzyLevel : FL_FULLSTRING FL_IGNORECASE
ulPropTag : 0x844F (0x844F101E)
lpProp :
ulPropTag : 0x844F (0x844F001E)
Value : another_multivalued_keyword_from_cu_defned_mv_field

rt : RES_CONTENT
resContent :
ulFuzzyLevel : FL_FULLSTRING FL_IGNORECASE
ulPropTag : 0x844D (0x844D001E)
lpProp :
ulPropTag : 0x844D (0x844D001E)
Value : just_textual_filter


rt : RES_OR
res.resOr :
cRes : 2
lpRes :
rt : RES_CONTENT
resContent :
ulFuzzyLevel : FL_PREFIX FL_IGNORECASE
ulPropTag : PR_MESSAGE_CLASS (0x001A001E)
lpProp :
ulPropTag : PR_MESSAGE_CLASS (0x001A001E)
Value : IPM.Task

rt : RES_CONTENT
resContent :
ulFuzzyLevel : FL_PREFIX FL_IGNORECASE
ulPropTag : PR_MESSAGE_CLASS (0x001A001E)
lpProp :
ulPropTag : PR_MESSAGE_CLASS (0x001A001E)
Value : IPM.TaskRequest


I tried to re-establish the RES_CONTENT with categories exactly as Outlook
does, I tried to mask 1000 as multivalued and also without the mask, I tried
to give him just string value of "searched_categ" and also to create string
array, but again, with no success.

Textual, boolean and message class work perfect for me.

The property tag for category I tried to determine programatically thgrough
mapiutils->getidsfromnames and it is strange I received something different
than 0x8014, i.e. something different than Outlook seems to use.
Can it be that at different machine property tag of Categories is different
than on another machine ?

I would really like to understand a bit more from HOW the Outlook client
actually does this filtering, but it seems noone ever has solved it, because
there is no clue in groups so far about Categories filtering.

I believe the DASL way will be satisfactory for me
thanks again for you hints

 

[Ken Slovak - [MVP - Outlook]]

Any property above 0x80000000 is a named property and the exact tag will
vary from machine to machine and from store provider to store provider.
Categories fits into that named property situation.

To get the correct property tag for a named property you use the
GetIDsFromNames() method. Let's say you have an RDOFolder object that is the
source for the MAPITable. That object has a GetIDsFromNames() method that
you call to get back the correct property tag like this:

string catsGUID = "{00020329-0000-0000-C000-000000000046}";
const int PT_MV_STRING8 = 0x101E;

int Tag = folder.GetIDsFromNames(catsGUID, "Keywords") | PT_MV_STRING8;

A tool like OutlookSpy will let you see the component pieces of named
properties and let you get the GUID, type (PT_MV_STRING8) and ID, which can
be a word or an int value.

MAPI restrictions do take some getting used to, but all in all they're the
most powerful and flexible way to do filtering or restricting.

 

[Bert_Bert]

yes, that was exactly how I programatically got the correct PropID for
Categories.
I just wondered it is different at two OST files looking to the same
Exchange mailbox.

I also looked at exact Restriction model and tried to re-apply it
programatically to MAPITable object but it does NOT work.

What is not documented at any place and is a big question for me is for
example - shall I use
ulPropTag = 0x????001E or 0x????101E version and
shall I use for lpProp a string "mycateg" or shall I use an array
dim a (0) as String
a(0)="my_categ"
lpProp = a
when specifying Restriction for multivalued property ??

I am asking because none of the above works for me, it is NOT possible to
use restrictions since recordcount returned is either 0, or there is some
error.

If I use just String UserProperty for example, or built in property, the
filtering with ANDed criteria works perfect for me, so it is a mystery
why/whether keyword filtering in this way actually IS possible ??

Maybe Outlook uses dasl mechanism underhood - I can see it in Advanced tab
when creating filter, then I do not understand why I saw the raw Restrictions
structure with Outloomk spy, or, why it works for Outlook and does not work
for me in Visual Basic - through Redemption.

Maybe it is possible only in C language, not in VB code....

 

[Ken Slovak - [MVP - Outlook]]

ulPropTag = 0x????001E would be for an ANSI string value (PT_STRING8).
ulPropTag = 0x????101E would be for an ANSI multivalued string value
(PT_MV_STRING8).

Categories is a PT_MV_STRING8 value so you would use 0x????101E, although
that ends up being derived by or'ing the tag you get from GetIDsFromNames
with 0x0000101E (in VB that would be &H101E).

Under the hood even if you set up a DASL type SQL query it ends up being
translated into a MAPI filer or restriction. That's all Extended MAPI
understands, after all.

lpProp would be the string you are looking for, not an array.

This has nothing to do with the language you're using. I've used MAPI
restrictions with Redemption for many years with VB 6, then with C# and
VB.NET and never had any problems with any of those languages.

You certainly can use SQL such as the categories example Dmitry has at
Redemption Objects:

set Recordset = Table.ExecSQL("SELECT Subject, EntryID from Folder " & _
"where (Categories =
'one') or (Categories = 'two') " & _
"order by
LastModificationTime desc")

That just puts the job of translating the DASL SQL into MAPI restrictions
onto Redemption.