GetUniqueValues.cf ( TextList ; ExceptionsList )

Gets unique values from a list (excluding values specified in the second list)

Average rating: 4.6 (29 votes) Log in to vote

Jonathan Jeffery
igeek
http://www.igeek.co.uk

Sample input:

GetUniqueValues.cf ( "Jean¶Paul¶Jean-Paul¶Peter¶-¶ ¶Jean¶David¶Paul" ; "-¶ " )

Sample output:

Jean
Paul
Jean-Paul
Peter
David

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

/*------------------------------------------------------------------------------------------
GetUniqueValues.cf ( TextList ; ExceptionsList ) ;

Returns a list of the unique entries in the input list, ignoring any lines matching entries on the 
exceptions list
                                                                                                                                        JEJ 2012-03-07
------------------------------------------------------------------------------------------*/


Let([

// safety feature to abort on long cycles
§MaxLoop = 10000 ;

// // // // // // // // // // // // //  // // // // // // // //
 // check that $$GUVStart  is initiated
$$GUVStart = Case( $$GUVStart < 1 ; 1 ; $$GUVStart );

 // check length of input list
§InLength = ValueCount ( TextList );

// get item from list
§Object = GetValue ( TextList ; $$GUVStart  );

// now increment $$GUVStart  by one
§GUVStart_original = $$GUVStart  ;
$$GUVStart = $$GUVStart + 1 ;

// Check if the item is unique
$$GUVOutput =
Case (
// 1. if var_Object is not on the output list, or the exceptions list, add it 
(§GUVStart_original  <= §InLength ) and ( not PatternCount ( ¶ & $$GUVOutput  & ¶ ;  ¶ & §Object & ¶) ) and ( not PatternCount ( ¶ & ExceptionsList & ¶ ; ¶ & §Object & ¶) )  and ( not IsEmpty ( §Object  ) ) ; 
          Case(
         ValueCount( $$GUVOutput ) = 0 ; §Object ;
          $$GUVOutput  & ¶ & §Object 
          );

// 2. otherwise do not modify the list 
$$GUVOutput
);


// Cycle...
result = 
Case(
// memory protect - stops trying to process too big a list
§InLength > §MaxLoop; "Error -- the list is too long for comfort!"; 
 
// otherwise, if the list is small enough
   Case (
      // 1.if we are not at the end of the list, repeat the function
      ( §GUVStart_original  < §InLength ) ; GetUniqueValues.cf ( TextList ; ExceptionsList ) ;
   
      // 2. Otherwise just return the output list
      $$GUVOutput
    ) 
);

// reset global variables before outputting result
$$GUVOutput = "";
$$GUVStart  = ""
 
];
 
result
)

Filters a list, returning only one instance of any repeated value. Items are returned in the oder of first appearance. Additionally, you can submit a list of values to be ignored—for example spaces, dashes and other characters that may have been entered into a field in lieu of a proper value. Such items will be ignored only is the *whole line* of the list matches the *whole* of one of the 'ignore-items'

Comments

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

GetUniqueValues.cf ( TextList ; ExceptionsList )

Comments

Support this website.

Support this website.