SubtractValues ( list ; list2 )

This functions subtracts list2 from list1, where both lists are in the format returned by the ValueListItems design function.

Average rating: 4.4 (28 votes) Log in to vote

Jonathan Stark - Show more from this author
Jonathan Stark Consulting
http://jonathanstark.com/

Sample input:

list1 = "1¶2¶3¶4¶5¶6¶7"
list2 = "7¶2¶4"

Sample output:

list3 = "¶¶¶1¶3¶5¶6"

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Let ([
    list1 = list1 & Case ( Right ( list1 ; 1 ) ≠ ¶ ; ¶ );
    list2 = list2 & Case ( Right ( list2 ; 1 ) ≠ ¶ ; ¶ );
    list3 = Case( ValueCount ( list2 ) = 1 ; 
        Substitute ( ¶ & list1 ; ¶ & list2 ; ¶ )
    ;
        SubtractValues ( 
            Substitute ( ¶ & list1 ; ¶ & RightValues ( list2 ; 1 ) ; ¶ ) 
        ; 
            LeftValues ( list2 ; ValueCount ( list2 ) - 1 ) 
        ) 
    );
    result = list3
];
    result
)

This functions subtracts list2 from list1, where both lists are in the format returned by the ValueListItems design function. It is the reverse of the built-in filterValues function. SubtractValues is useful for displaying the unselected members of list in a portal.

Note: I didn't bother trimming the leading return from the result because I was using it as a multikey for portal filtering.

There are a couple of other custom functions on Brian's site that are similar, but I think it is helpful to see all different ways to skin the cat. This page is a nice resource, by the way - kudos Dunning!

Comments

		Vaughan, Sydney, Australia Jun 23, 2011
Thanks Jonathon.

		Daniel Feb 19, 2019
This function has a recursion limit of 10,000 lines per either list.

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

SubtractValues ( list ; list2 )

Comments

Support this website.

Support this website.