Brian Dunning's FileMaker Custom Functions

MergeLists ( _array1 ; _array2 )

Merge two value lists at twice the speed of similar functions. Requires RemoveEmptyValues custom function.

  Average rating: 4.2 (32 votes) Log in to vote

Michael Rhodes   Michael Rhodes
Allied Data Service
http://allieddataservice.com

Share on Facebook Share on Twitter

  Sample input:
MergeLists (
"tom¶dick¶harry" ;
"tommy¶tom¶wally"
)
  Sample output:
"tom¶dick¶harry¶tommy¶wally"

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

Good for lists with fewer than 1,000 values in common. Does not remove duplicate values within each list, but does remove duplicate values between both lists. Does not necessarily leave list1 first. If list 2 is shorter, it will be first, because the function picks the smaller list to de-dup which saves processing time.

 

Comments

Rich K.   Rich K., Phila
Aug 28, 2012
This function has been throwing a "There are too many parameters in this function." error. It does not seem throw 1202 error back to the script. It has been quietly failing for ? months ? years ?. I have seemed to localize that as soon as my arrays gets above ~1500 items. Ouchie!
 
Michael   Michael, California
Jan 1, 2013
Rich,

I updated the function. It now returns "?" if there are too many values to process. Second, it converts only the common values to a Substitute statement and uses whichever list is shorter, both changes to further minimize processing time.
 
Kevin Frank   Kevin Frank, Arcata, California
May 3, 2013
Hi Michael,

Thanks so much... this solves a difficult challenge for me.

One minor error: You have a couple "Null" references in this CF, which I'm guessing refer to another CF... I replaced them with "" and all appears to be well.

Regards,
Kevin
 
Gary   Gary, Bonn, Germany
Aug 18, 2014
Hi, thanks for this CF.

I presume, that, if both lists are sorted that the resulting list is also sorted. True?

For example:
_array1 = "2011¶2014"
_array2 = "2011¶2012¶2013¶2014"
produces "2011¶2012¶2013¶2014" as output. Fact or coincidence?

Thanks,

Gary
 
Andy Smith   Andy Smith
Feb 9, 2015
Thanks for the function. I found that the function can fail if there are no common values shared between the lists.

In the second Case function, the first test should be:

IsEmpty ( _commonValues ) ; _array1 & ¶ & _array2

Regards,
Andy
 
Michael R   Michael R, Colorado Springs
Feb 10, 2015
Thanks, Andy. I updated the function.
 
Richard DeShong   Richard DeShong, Logic Tools
Mar 17, 2015
Thanks Michael, saved me some time.

Possible bug: If _List1 is less than _List2, then _List = _List1 & Substitute( _List2 ). But then the result = Middle( _List; 2; <etc> ), which would chop of the first char. If _List2 is longer, then the first char is pilcro.

Also, to make is slightly faster when one list is <empty>, put the IsEmpty() tests outside of the Let().
 
Michael R   Michael R, Colorado Springs
Mar 17, 2015
Hi Richard, thanks for your input. The reason I personally prefer the IsEmpty command nested within the Let statement, is so I can weed out null lists. The performance hit shouldn't be observable.
 

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.