Query Custom Form Fields

I am creating a custom form in Outlook 2002/Exchange
2003. This form has many user-defined fields to track
document names and revision numbers. The users want to
be able to query the fields to find the document names.
Currently I have created each field as a new field,
using unique field name. For example:

DocName1 DocNumber1
DocName2 DocNumber2
DocName3 DocNumber3

This approach allows me to create multiple entry fields,
but makes it difficult for a user to query a form to find
if a specific document name or number is present. Is
there a way to query ALL fields with name DocName? Or is
there a way to export the information to a database for
bettery query capabilities?

Thanks,
Joe

No, you cannot do a full text search, nor use wildcards for field names when
using the Restrict, Find or AdvancedSearch methods.

However, it is possible that you could build your search string dynamically
by looping through the UserProperties collection and looking for DocName# in
each field, and adding every one to an array. Then loop through the array
and build your search criteria with OR clauses for all those fields. Note
that only the AdvancedSearch method supports CONTAINS type searches, where
you are looking for occurences of text within text.

Another option is to use one custom field, but store the values in it using
delimiters that you can later extract using the Split function.

--
Eric Legault - B.A, MCP, MCSD, Outlook MVP
--
Try Picture Attachments Wizard for Outlook!
http://tinyurl.com/9bby8
--
Job: http://www.imaginets.com
Blog: http://blogs.officezealot.com/legault/


"Joe" wrote:

> I am creating a custom form in Outlook 2002/Exchange
> 2003. This form has many user-defined fields to track
> document names and revision numbers. The users want to
> be able to query the fields to find the document names.
> Currently I have created each field as a new field,
> using unique field name. For example:
>
> DocName1 DocNumber1
> DocName2 DocNumber2
> DocName3 DocNumber3
>
> This approach allows me to create multiple entry fields,
> but makes it difficult for a user to query a form to find
> if a specific document name or number is present. Is
> there a way to query ALL fields with name DocName? Or is
> there a way to export the information to a database for
> bettery query capabilities?
>
> Thanks,
> Joe
>

Eric,
Thanks for the response. (Un)fortunately, I'm a network guy and my
programming skills are pretty much limited to cut-and-paste plagarism. Could
you point me to a document or example on using delimiters in the field data?
I tried assigning the same field name to multiple field instances, but it
appeared to overwrite existing data any time other data was entered.

Thanks,
Joe

"Eric Legault [MVP - Outlook]" wrote:

> No, you cannot do a full text search, nor use wildcards for field names when
> using the Restrict, Find or AdvancedSearch methods.
>
> However, it is possible that you could build your search string dynamically
> by looping through the UserProperties collection and looking for DocName# in
> each field, and adding every one to an array. Then loop through the array
> and build your search criteria with OR clauses for all those fields. Note
> that only the AdvancedSearch method supports CONTAINS type searches, where
> you are looking for occurences of text within text.
>
> Another option is to use one custom field, but store the values in it using
> delimiters that you can later extract using the Split function.
>
> --
> Eric Legault - B.A, MCP, MCSD, Outlook MVP
> --
> Try Picture Attachments Wizard for Outlook!
> http://tinyurl.com/9bby8
> --
> Job: http://www.imaginets.com
> Blog: http://blogs.officezealot.com/legault/
>
>
> "Joe" wrote:
>
> > I am creating a custom form in Outlook 2002/Exchange
> > 2003. This form has many user-defined fields to track
> > document names and revision numbers. The users want to
> > be able to query the fields to find the document names.
> > Currently I have created each field as a new field,
> > using unique field name. For example:
> >
> > DocName1 DocNumber1
> > DocName2 DocNumber2
> > DocName3 DocNumber3
> >
> > This approach allows me to create multiple entry fields,
> > but makes it difficult for a user to query a form to find
> > if a specific document name or number is present. Is
> > there a way to query ALL fields with name DocName? Or is
> > there a way to export the information to a database for
> > bettery query capabilities?
> >
> > Thanks,
> > Joe
> >

Here's the documentation on the Split function:

http://msdn.microsoft.com/library/d.../vsfctsplit.asp

If you are going to store delimited data in a field, use the Join function
if your data is in an array:

http://msdn.microsoft.com/library/d...l/vsfctjoin.asp

Otherwise, you just need to use Split to get it out. Let's walk though
this. Say you create a DocNames custom field. If you already have "Doc1" in
that field as a value, append to it using a comma delimiter:

MailItem.UserProperties("DocNames").Value =
MailItem.UserProperties("DocNames").Value & "," & "Doc 2"

So now DocNames has "Doc1, Doc2" as the value. You can now use the Find or
Restrict functions against just the DocNames field to look for occurrences of
"Doc2".

If you need to work with the data in DocNames in your code, you can use the
Split function to parse the field values into an array that you can loop
through (see the documentation).

For more resources on getting started with programming custom forms, see
this link:

http://www.outlookcode.com/d/forms.htm

--
Try Picture Attachments Wizard for Outlook!
http://tinyurl.com/9bby8
--
Eric Legault - B.A, MCP, MCSD, Outlook MVP
Job: http://www.imaginets.com
Blog: http://blogs.officezealot.com/legault/


"Joe" wrote:

> Eric,
> Thanks for the response. (Un)fortunately, I'm a network guy and my
> programming skills are pretty much limited to cut-and-paste plagarism. Could
> you point me to a document or example on using delimiters in the field data?
> I tried assigning the same field name to multiple field instances, but it
> appeared to overwrite existing data any time other data was entered.
>
> Thanks,
> Joe
>
> "Eric Legault [MVP - Outlook]" wrote:
>
> > No, you cannot do a full text search, nor use wildcards for field names when
> > using the Restrict, Find or AdvancedSearch methods.
> >
> > However, it is possible that you could build your search string dynamically
> > by looping through the UserProperties collection and looking for DocName# in
> > each field, and adding every one to an array. Then loop through the array
> > and build your search criteria with OR clauses for all those fields. Note
> > that only the AdvancedSearch method supports CONTAINS type searches, where
> > you are looking for occurences of text within text.
> >
> > Another option is to use one custom field, but store the values in it using
> > delimiters that you can later extract using the Split function.
> >
> > --
> > Eric Legault - B.A, MCP, MCSD, Outlook MVP
> > --
> > Try Picture Attachments Wizard for Outlook!
> > http://tinyurl.com/9bby8
> > --
> > Job: http://www.imaginets.com
> > Blog: http://blogs.officezealot.com/legault/
> >
> >
> > "Joe" wrote:
> >
> > > I am creating a custom form in Outlook 2002/Exchange
> > > 2003. This form has many user-defined fields to track
> > > document names and revision numbers. The users want to
> > > be able to query the fields to find the document names.
> > > Currently I have created each field as a new field,
> > > using unique field name. For example:
> > >
> > > DocName1 DocNumber1
> > > DocName2 DocNumber2
> > > DocName3 DocNumber3
> > >
> > > This approach allows me to create multiple entry fields,
> > > but makes it difficult for a user to query a form to find
> > > if a specific document name or number is present. Is
> > > there a way to query ALL fields with name DocName? Or is
> > > there a way to export the information to a database for
> > > bettery query capabilities?
> > >
> > > Thanks,
> > > Joe
> > >

Eric,
Still trying to make sense of this, and trying to make sure I make the right
decision on how to proceed. So let me rephrase my question and see if your
suggestions are still appropriate.

I have three fields on a single form. A user will enter a different
document name in each field, then send the message to a public folder. Over
time a user will want to search the contents of the public folder for the
presence of one or more document names contained in the custom fields. If I
use different field names for each field, your suggestion of looping through
the forms and building an array would seem to be a time/CPU intensive task.
If I use a common field name for all fields, how do I keep the field data
from being overwritten each time data is entered. (If I enter data in the
first instance of the field, tab down to the second instance and enter data,
the original data is replaced as soon as I tab to the third field.)

Your thoughts are greatly appreciated.
Thanks,
Joe

"Eric Legault [MVP - Outlook]" wrote:

> Here's the documentation on the Split function:
>
> http://msdn.microsoft.com/library/d.../vsfctsplit.asp
>
> If you are going to store delimited data in a field, use the Join function
> if your data is in an array:
>
> http://msdn.microsoft.com/library/d...l/vsfctjoin.asp
>
> Otherwise, you just need to use Split to get it out. Let's walk though
> this. Say you create a DocNames custom field. If you already have "Doc1" in
> that field as a value, append to it using a comma delimiter:
>
> MailItem.UserProperties("DocNames").Value =
> MailItem.UserProperties("DocNames").Value & "," & "Doc 2"
>
> So now DocNames has "Doc1, Doc2" as the value. You can now use the Find or
> Restrict functions against just the DocNames field to look for occurrences of
> "Doc2".
>
> If you need to work with the data in DocNames in your code, you can use the
> Split function to parse the field values into an array that you can loop
> through (see the documentation).
>
> For more resources on getting started with programming custom forms, see
> this link:
>
> http://www.outlookcode.com/d/forms.htm
>
> --
> Try Picture Attachments Wizard for Outlook!
> http://tinyurl.com/9bby8
> --
> Eric Legault - B.A, MCP, MCSD, Outlook MVP
> Job: http://www.imaginets.com
> Blog: http://blogs.officezealot.com/legault/
>
>
> "Joe" wrote:
>
> > Eric,
> > Thanks for the response. (Un)fortunately, I'm a network guy and my
> > programming skills are pretty much limited to cut-and-paste plagarism. Could
> > you point me to a document or example on using delimiters in the field data?
> > I tried assigning the same field name to multiple field instances, but it
> > appeared to overwrite existing data any time other data was entered.
> >
> > Thanks,
> > Joe
> >
> > "Eric Legault [MVP - Outlook]" wrote:
> >
> > > No, you cannot do a full text search, nor use wildcards for field names when
> > > using the Restrict, Find or AdvancedSearch methods.
> > >
> > > However, it is possible that you could build your search string dynamically
> > > by looping through the UserProperties collection and looking for DocName# in
> > > each field, and adding every one to an array. Then loop through the array
> > > and build your search criteria with OR clauses for all those fields. Note
> > > that only the AdvancedSearch method supports CONTAINS type searches, where
> > > you are looking for occurences of text within text.
> > >
> > > Another option is to use one custom field, but store the values in it using
> > > delimiters that you can later extract using the Split function.
> > >
> > > --
> > > Eric Legault - B.A, MCP, MCSD, Outlook MVP
> > > --
> > > Try Picture Attachments Wizard for Outlook!
> > > http://tinyurl.com/9bby8
> > > --
> > > Job: http://www.imaginets.com
> > > Blog: http://blogs.officezealot.com/legault/
> > >
> > >
> > > "Joe" wrote:
> > >
> > > > I am creating a custom form in Outlook 2002/Exchange
> > > > 2003. This form has many user-defined fields to track
> > > > document names and revision numbers. The users want to
> > > > be able to query the fields to find the document names.
> > > > Currently I have created each field as a new field,
> > > > using unique field name. For example:
> > > >
> > > > DocName1 DocNumber1
> > > > DocName2 DocNumber2
> > > > DocName3 DocNumber3
> > > >
> > > > This approach allows me to create multiple entry fields,
> > > > but makes it difficult for a user to query a form to find
> > > > if a specific document name or number is present. Is
> > > > there a way to query ALL fields with name DocName? Or is
> > > > there a way to export the information to a database for
> > > > bettery query capabilities?
> > > >
> > > > Thanks,
> > > > Joe
> > > >

First off, looping through an array retrieved from a Split function is not an
intensive operation at all.

The main issue with your solution is that you have a dynamic number of
custom fields for document names. Since you can't do a full text search on a
given field using the Restrict of Find methods (e.g. look for occurrence of
"xyz"; only pattern matching is supported - e.g. look for "xyz****" or
"****xyz"), you have to loop through all items in the folder and inspect the
value of each desired field using the InStr function:

Sub SearchCommonFieldsExample()
Dim objItems As Outlook.Items, objFolder As Outlook.MAPIFolder
Dim objItem As MailItem
Dim objUserProperties As Outlook.UserProperties, objUserProperty As
Outlook.UserProperty
Dim intX As Integer
Dim intTextFoundLocation As Integer
Dim strBaseFieldName As String

strBaseFieldName = "Doc"
'set objFolder = Whatever folder that contains item you need to loop
through
Set objItems = objFolder.Items
For Each objItem In objItems
For intX = 1 To 3
Set objUserProperties = objItem.UserProperties
Set objUserProperty = objUserProperties(strBaseFieldName & intX)
'Look for Doc1, Doc2, or Doc3 field
If Not objUserProperty Is Nothing Then
intTextFoundLocation = InStr(objUserProperty.Value,
"TextToSearchFor")
If intTextFoundLocation <> 0 Then
'VALUE FOUND!
End If
End If
Next
Next
End Sub

If the field names that you need to search share common text, then all I'm
saying is you can cut down on your code by looping through the variations on
that name. However, this is another approach to looping through all items in
a folder, by using the AdvancedSearch method and looping through the common
fields as before:

Sub AdvancedSearchExample()
Dim objSearch As Outlook.Search
Dim strBaseFieldName As String
Dim strDASL As String, strSearch As String
Dim intX As Integer

strBaseFieldName = "Doc"

For intX = 1 To 3
strDASL =
"http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/"
strSearch = strDASL & strBaseFieldName & intX = " LIKE '%Text To
Search For%'" 'Do a full text search using LIKE predicate
Set objSearch = Application.AdvancedSearch("Inbox", strSearch)
Next
End Sub

Note that I still have to dynamically build the search string to iterate
through the variations of the field names. If the names had no common
string, then you'd have to repeat several lines of nearly identical code,
which isn't very efficient.

There's more to AdvancedSearch than the example I'm showing though; look at
the example in the Outlook VBA help file ("C:\Program Files\Microsoft
Office\OFFICE11\1033\VBAOL11.CHM" for OL 2003, or press F1 while your cursor
is within a property or method in the VBA Editor).

If your custom fields are being overwritten, then it sounds like you have
mapped multiple controls to the same field! Ensure that each control is
mapped to a unique field name (Doc1, Doc2, etc.).

--
Eric Legault - B.A, MCP, MCSD, Outlook MVP
Try Picture Attachments Wizard for Outlook! http://tinyurl.com/9bby8
Job: http://www.imaginets.com
Blog: http://blogs.officezealot.com/legault/


"Joe" wrote:

> Eric,
> Still trying to make sense of this, and trying to make sure I make the right
> decision on how to proceed. So let me rephrase my question and see if your
> suggestions are still appropriate.
>
> I have three fields on a single form. A user will enter a different
> document name in each field, then send the message to a public folder. Over
> time a user will want to search the contents of the public folder for the
> presence of one or more document names contained in the custom fields. If I
> use different field names for each field, your suggestion of looping through
> the forms and building an array would seem to be a time/CPU intensive task.
> If I use a common field name for all fields, how do I keep the field data
> from being overwritten each time data is entered. (If I enter data in the
> first instance of the field, tab down to the second instance and enter data,
> the original data is replaced as soon as I tab to the third field.)
>
> Your thoughts are greatly appreciated.
> Thanks,
> Joe
>
> "Eric Legault [MVP - Outlook]" wrote:
>
> > Here's the documentation on the Split function:
> >
> > http://msdn.microsoft.com/library/d.../vsfctsplit.asp
> >
> > If you are going to store delimited data in a field, use the Join function
> > if your data is in an array:
> >
> > http://msdn.microsoft.com/library/d...l/vsfctjoin.asp
> >
> > Otherwise, you just need to use Split to get it out. Let's walk though
> > this. Say you create a DocNames custom field. If you already have "Doc1" in
> > that field as a value, append to it using a comma delimiter:
> >
> > MailItem.UserProperties("DocNames").Value =
> > MailItem.UserProperties("DocNames").Value & "," & "Doc 2"
> >
> > So now DocNames has "Doc1, Doc2" as the value. You can now use the Find or
> > Restrict functions against just the DocNames field to look for occurrences of
> > "Doc2".
> >
> > If you need to work with the data in DocNames in your code, you can use the
> > Split function to parse the field values into an array that you can loop
> > through (see the documentation).
> >
> > For more resources on getting started with programming custom forms, see
> > this link:
> >
> > http://www.outlookcode.com/d/forms.htm
> >
> > --
> > Try Picture Attachments Wizard for Outlook!
> > http://tinyurl.com/9bby8
> > --
> > Eric Legault - B.A, MCP, MCSD, Outlook MVP
> > Job: http://www.imaginets.com
> > Blog: http://blogs.officezealot.com/legault/
> >
> >
> > "Joe" wrote:
> >
> > > Eric,
> > > Thanks for the response. (Un)fortunately, I'm a network guy and my
> > > programming skills are pretty much limited to cut-and-paste plagarism. Could
> > > you point me to a document or example on using delimiters in the field data?
> > > I tried assigning the same field name to multiple field instances, but it
> > > appeared to overwrite existing data any time other data was entered.
> > >
> > > Thanks,
> > > Joe
> > >
> > > "Eric Legault [MVP - Outlook]" wrote:
> > >
> > > > No, you cannot do a full text search, nor use wildcards for field names when
> > > > using the Restrict, Find or AdvancedSearch methods.
> > > >
> > > > However, it is possible that you could build your search string dynamically
> > > > by looping through the UserProperties collection and looking for DocName# in
> > > > each field, and adding every one to an array. Then loop through the array
> > > > and build your search criteria with OR clauses for all those fields. Note
> > > > that only the AdvancedSearch method supports CONTAINS type searches, where
> > > > you are looking for occurences of text within text.
> > > >
> > > > Another option is to use one custom field, but store the values in it using
> > > > delimiters that you can later extract using the Split function.
> > > >
> > > > --
> > > > Eric Legault - B.A, MCP, MCSD, Outlook MVP
> > > > --
> > > > Try Picture Attachments Wizard for Outlook!
> > > > http://tinyurl.com/9bby8
> > > > --
> > > > Job: http://www.imaginets.com
> > > > Blog: http://blogs.officezealot.com/legault/
> > > >
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > I am creating a custom form in Outlook 2002/Exchange
> > > > > 2003. This form has many user-defined fields to track
> > > > > document names and revision numbers. The users want to
> > > > > be able to query the fields to find the document names.
> > > > > Currently I have created each field as a new field,
> > > > > using unique field name. For example:
> > > > >
> > > > > DocName1 DocNumber1
> > > > > DocName2 DocNumber2
> > > > > DocName3 DocNumber3
> > > > >
> > > > > This approach allows me to create multiple entry fields,
> > > > > but makes it difficult for a user to query a form to find
> > > > > if a specific document name or number is present. Is
> > > > > there a way to query ALL fields with name DocName? Or is
> > > > > there a way to export the information to a database for
> > > > > bettery query capabilities?
> > > > >
> > > > > Thanks,
> > > > > Joe
> > > > >