none
User defined field formula - includes or contains operator RRS feed

  • Question

  • I am trying to create a user-defined field that allows me to filter the categories of outlook Items - in this case tasks.

    So I want to check if an item is in a certain category, here's what I tried:

    IIf([Categories]="! Prio - L O W","L O W",IIf([Categories]="*! Prio - ! M E D*","M E D",IIf([Categories]="! Prio - ! ! H I G H","H I G H",IIf([Categories]="! Prio - ! ! ! M U S T","M U S T","NoPrioSet"))))

    The problem is that when there is more than one category it goes to the last false statement - i.e. if I have an item with a "To Do" category and a "! Prio - L O W" category, it will not read that it is a low priority.

    I need a statement that says contains rather than = (equals) - i.e. IIf([Categories] CONTAINS "! Prio - L O W","

    I know you can set the priority elswhere, but this would be useful in other situations to sort categories.

    Thank you in advance.

    Tuesday, March 5, 2013 12:38 PM

Answers

  • I have found something that works with the "like" operator (see below).

    However, I found that you cannot sort by this User field. Any ideas?

    Here the solution

    IIf([Categories] Like "*! Prio - L O W*","L O W",IIf([Categories] Like "*! Prio - ! M E D*","M E D",IIf([Categories] Like "*! Prio - ! ! H I G H*","H I G H",IIf([Categories] Like "*! Prio - ! ! ! M U S T*","M U S T","NoPrioSet"))))

    Wednesday, March 6, 2013 12:02 AM

All replies

  • look at http://msdn.microsoft.com/en-us/library/office/cc513841(v=office.12).aspx (especially Keywords properties subchapter)
    Tuesday, March 5, 2013 2:14 PM
  • Thank you.

    I have tried to find a solution there but I may have missed it.

    If you were referring to the "Keywords Properties in a Jet Query" section, I think I wrote exactly that but it does not return true when more that one category are present.

    IIf([Categories]="! Prio - L O W","L O W",IIf([Categories]="! Prio - ! M E D","M E D",IIf([Categories]="! Prio - ! ! H I G H","H I G H",IIf([Categories]="! Prio - ! ! ! M U S T","M U S T","NoPrioSet"))))

    Tuesday, March 5, 2013 11:45 PM
  • I have found something that works with the "like" operator (see below).

    However, I found that you cannot sort by this User field. Any ideas?

    Here the solution

    IIf([Categories] Like "*! Prio - L O W*","L O W",IIf([Categories] Like "*! Prio - ! M E D*","M E D",IIf([Categories] Like "*! Prio - ! ! H I G H*","H I G H",IIf([Categories] Like "*! Prio - ! ! ! M U S T*","M U S T","NoPrioSet"))))

    Wednesday, March 6, 2013 12:02 AM
  • no, i was reffering to 'ci_phrasematch' and 'like' keywords as described in details with example in subchapter that i mentioned earlier:

    use DASL syntax that allows different restriction types such as phrase matching (ci_phrasematch keyword), starts with matching (ci_startswith keyword), or substring matching (like keyword). The following criteria string will find all items that contain "Business" as a category or as a word in a category, such as an item with the categories "Business" and "Business Intelligence."

    string filter = "@SQL=" + "\""
        + "urn:schemas-microsoft-com:office:office#Keywords"
        + "\"" + " ci_phrasematch 'Business'";

    As for sorting, why do you need sorting by category? it really does not make any sense, especially since you are matching by partial Words.

     
    Wednesday, March 6, 2013 8:25 AM
  • Hi Damian,

    Thank you for your reply. Sorry to come back to this late but I was away for a while.

    Maybe it does not make sense but here's how I'd like to use the user-defined fields.

    I have several categories on my tasks. I have project categories (say they always star by "Proj"), I have priority categories (say they start by "Prio"), I have type categories (say they start by "Type" - To Do; Follow-up; Call).

    I thought I could use use-defined fields to isolate categories and sort them by sub-categories. So the user-defined field MyProjects would include all categories stating by Proj. (that is the idea - I could not even get close to that)

    On the Priorities should be easier because there's only 4. The formula below works when there the task has only one category, but fails when the task has another category. Hence the Therefore the * operator is not really working.

    IIf([Categories] Like "*! Prio - L O W*","L O W",IIf([Categories] Like "*! Prio - ! M E D*","M E D",IIf([Categories] Like "*! Prio - ! ! H I G H*","H I G H",IIf([Categories] Like "*! Prio - ! ! ! M U S T*","M U S T","NoPrioSet"))))

    In any case this seems to be pointless because it is not possible to sort by user-defined field - which is strange and beats the point of creating an user defined field I guess.

    Thanks again for your replies so far. Nuno

    Tuesday, March 19, 2013 10:51 AM
  • You have to provide your own sorting in your custom code. As for searching, use ci_phrasematch as described in article.
    Tuesday, March 19, 2013 11:13 AM
  • Thank you again Damian.

    We are probably talking of different things. I am not programming really.

    I was looking at a way of organizing my tasks in outlook, and was going through the "Field Chooser". There I found that it is possible to make user-defined fields with Formulas. I used the formula above to isolate 4 categories in one field. So actually now in my Tasks view I see these 4 categories in a separate column.

    As for the sorting, I expected that when I click in the user-defined field header of the column it would sort by that filed - as it sorts is you click by Due Date - but it doesn't.

    Thank you for your time.

    PS: by the way, the formula window for user-defined fields does not accept the ci_phrasematch (but it s good to know it for future reference).
    The formula I wrote above ended up working. I'm afraid yesterday I had two similarly named fields and was updating one while looking at the results in the other. Sorry about that.

    Wednesday, March 20, 2013 12:37 PM