Help with contains in a formula

Posted on 2008-11-04
Last Modified: 2013-12-18
I have the following code in a calendar view:

SELECT ((Form = "Time Off Request") | (Form = "TimeOff")) & (@Contains(Add_Calender; "Yes")) & (@Contains(Type; "Associate")) & (!@Contains(Status; "Pending")) & (!@Contains(Team; "LCCC")) &  (!@Contains(Team; "Team Services")) & (!@Contains(Team; "Mail/Image")) &(!@Contains(Team; "LRU"))  & (!@Contains(Team;"Team Services/Filing")) & RequestType !="Overtime"

This does work but I am wondering if there is a better way to write the Does Not Contain portion. I have tried this:

!@Contains(Team;"LCCC"|"Team Services"|Mail/Image"|"LRU") and that does not work. It will not pull any records then at all.  Does anyone have any suggestions or am I just missing the boat on how @Contains works.

Question by:kali958
    LVL 22

    Accepted Solution

    I think you are confusing @Contains and @IsMember.

    @Contains is used to search a string for a sub-string.
    @IsMember (and @IsNotMember) are used to indicate whether a list (or multiple-value field) includes a specific value.

    Further, if the field can only accept one value, and you are matching the full value (not a sub-string), then you can simply use the 'equals' operator (=).

    For example, it appears to me that Add_Calender is a single-value field that either contains "Yes" or "No".  If this assumption is correct, then I would not use @Contains, I would just use (Add_Calender = "Yes").

    Below is an example of how I would write this selection formula based on these assumptions:

    Single-valued fields:

    Multiple-valued fields:

    (If these assumptions are not correct, then the formula below will need to be modified).
    SELECT @IsMember(Form; "Time Off Request" : "TimeOff") & 
    (Add_Calender = "Yes") & (Type = "Associate") & (Status != "Pending") & 
    @IsNotMember(Team; "LCCC" : "Team Services" : "Mail/Image" : "LRU" : "Team Services/Filing") & 
    (RequestType != "Overtime")

    Open in new window


    Author Closing Comment

    Thank you Bill. That is what I needed. I was missing the difference between the @Contains and @IsMember. I thought also that @IsNotMember and @IsMember was only to be used with UserRoles.  I appreciate the education and the prompt answer!  Thanks again

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
    Article by: Rob
    Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now