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 Allied Data Service http://allieddataservice.com |
Sample input:
MergeLists (
"tom¶dick¶harry" ;
"tommy¶tom¶wally"
)
"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., 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, 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 & Associates 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, GC Consulting 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 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, Colorado Springs Feb 10, 2015 |
|
| Thanks, Andy. I updated the function. | ||
 |
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, 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. | ||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.