sorting or grouping on formula based User-defined fields

  • Thread starter Thread starter Andras Szekely
  • Start date Start date
A

Andras Szekely

Hi,

I have a formula based user defined field containing the value:
Str(Mid([Subject],2,6))

By default it's not possible to sort or group by formula UDF's.

Sue Mosher wrote quite a while ago:
"Quite right, and thanks for pointing that out. Turns out that I'd had
formula and combination fields on the brain lately and hadn't been
working with any other kind of UDF for a while. And, of course, there's
a workaround. If you need to sort or group by a calculated field, put
the formula in code behind the form rather than in the definition of the
field."

Does anyone know how can this be achieved? I'm not a coder, apart from
defining rules and auto-formatting I haven't customized Outlook too much.

Many thanks
Andras
 
The code for the Item_Write event handler would look like this:

Function Item_Write()
Item.UserProperties("YourFieldName") = Mid(Item.Subject, 2, 6)
End Function

See http://www.outlookcode.com/d/forms.htm if you need more basic info on putting code in forms.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 
I must admit I'm lame for scripting and environments where scripting
happens. Of course I can replace my real fieldname in the below example.

Could you though help me which form shall I edit, how do I save it, and
what do I set in outlook, in order that this Function could help me
filling a user-defined field which I can see in my Inbox?

many thanks
Andras
 
You want to see this information on items you receive in your Inbox? A custom form would not be an appropriate solution for that scenario. It would be better done with application-level Outlook VBA code. If you're new to Outlook VBA macros, these web pages should tell you how to add code, set security, etc. so you can get started in that environment:

http://www.winnetmag.com/Articles/Index.cfm?ArticleID=21522&pg=1
http://www.outlookcode.com/d/vb.htm

When you're ready to add code, the code below would go into the built-in ThisOutlookSession module. You would then need to either restart Outlook or run the Application_Startup procedure.

Dim WithEvents inboxItems As Outlook.Items

Private Sub Application_Startup()
Dim ns As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder

Set ns = Application.GetNamespace("MAPI")
Set fld = ns.GetDefaultFolder(olFolderInbox)
' instantiate the Items object declared WithEvents
Set inboxItems = fld.Items

Set ns = Nothing
Set fld = Nothing
End Sub

Private Sub inboxItems_ItemAdd(ByVal Item As Object)
Dim prop As Outlook.UserProperty

' add property to item
Set prop = Item.UserProperties.Add("YourFieldName", olText, True)
' set property value
prop.Value = Mid(Item.Subject, 2, 6)
' save the changes to the item
Item.Save

Set prop = Nothing
End Sub

Note that VBA code is for your personal use. If you want everyone in your organization to have this functionality, you'll want to build a COM add-in and install it on each machine.

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 
Back
Top