Usability - Productivity - Business - The web - Singapore & Twins

Lists in @Formula, especially @Member and @IsNotMember

Theo Heselmans has a nice post about how to figure out if a user has a specific role without using @IsMember or @IsNotMember. He doesn't like @IsMember and @IsNotMember. While that is a question of taste (or speed?) it is a good idea to fully understand how the various comparisions work. = or *= work differnt with lists than @is(Not)Member. One of the hallmarks of @Formula is the ability to deal with lists (multiple elements, similar to arrays) in the various formulas. In a lot of the @Functions you can use lists ("red":"blue":"green") to compute your results. If a Notes item (a field) contains multiple values they are treated as lists in formulas. The following table shows the effect of the various comparison results. I limit myself to matches, you can play with "greater" and "less" comparisons.
Let us asume we have these lists:

list1 := "red":"yellow":"green"
list2 := "yellow":"red"
list3 := "black":"blue"
list4 := "red":"white"
list5 := "yellow"

Comparison Effect Matches for @true Examples Result
= Compares every single value with the same position in the other list. If one list has less elements than the other list, then the shorter list is padded with the last value. In a list of one, that one element is compared with all others. If there is ONE match true is returned. one match, same position list1 = list2
list2 = list5
list5 = list1
*= Compares every member of the first with every member of the second list. If there is a single match true is returned. In difference to = the position doesn't play a role and there is no padding (needed) one match, any position list1 *= list2
list2*= list5
! [Expression] Turns the expression into the opposite. Very clean. Should be always in front. For readability you might want to use brackets depends on expression !list1 = list2
!(list1 = list2)
!(list2 *= list5)
!= not equal operator. Compares element by element, padds a shorter list with the last element (same as =). Returns true if one match pair is different. In other words: only returns false if both list have the same members in the same sequence. one mismatch, any position list1 != list2 @true
*!= Compared every element from both lists and returns true if it finds one difference. It only will return false if both lists only contain one value (which can be there multiple times, that wouldn't matter) if one list has 2 different values "red":"red" *!= "red":"red":"red"
list1*!= list2
@IsMember Checks that all members of the first list are available in the second list. The sequence doesn't matter. If there is one element in the first list, that is not in the second list it returns false. all, any position @isMember(list2;list1)
!@IsMember reversal of @isMember. If there is just one element in the first list that is not in the second list it returns true. one mismatch, any position !@isMember(list2;list1)
@isNotMember Checks that none of the elements in the first list is in the second list. If just one element is there, but not others it already returns false. So you can't replace !@IsMember with @IsNotMember for lists arguments. all mismatches, any position @isNotMember(list2;list1)
!@isNotMember Double negation to make your head spin <g>. Needs only a single match to return true. Equivalent to *= one match, any position @isNotMember(list2;list1)

Does your head spin now?

Posted by on 30 June 2007 | Comments (4) | categories: Show-N-Tell Thursday


  1. posted by Theo Heselmans on Tuesday 03 July 2007 AD:
    Nice overview Stephan
  2. posted by Rob Pinion on Saturday 14 July 2007 AD:
    Instead of @IsMember, I use @Contains. I think @Contains is quirky, or at least has quirky documentation, but in my opinion, it's the best way to test for user roles.

    For example, for a hide-when, this formula hides the text if the user has neither MyRole nor SomeOtherRole but shows the text if the user has either or both.


    I often use this in a style.display block:

    #someDiv {
    display: <ComputedText>;

    The formula for the computed text could be:


  3. posted by Stephan H. Wissel on Thursday 25 October 2007 AD:
    Why use @contains if *= does the trick?
    Your formula would be:
    @if(@UserRoles *= "[Role1]":"[Role2]");"inline";"none")
  4. posted by Patrick Kwinten on Monday 29 February 2016 AD:
    how can instantiate an empty list?

    e.g. var cars := null;

    if (budget > 1000;cars:"vw";"");
    if (budget> 100.000;cars:"ferrari";"")