mkConcatColumns ( valueList1, valuelist2, delimiter )

add two lists horizontally (list function with more than one field per row)

Average rating: 3.5 (2 votes) Log in to vote

Michael Kupietz - Show more from this author
Michael Kupietz FileMaker Consulting
https://michaelkupietz.com

Sample input:

mkConcatColumns(
mkConcatColumns(
list(field1),
list(field2),
" -- "),
list(field3),
" / ")

(For a table with four records and three fields:

field1, field2, field3
-- -- --
a, b, c
1, 2, 3
red, blue, green
east, west, north)

Sample output:

"a -- b / c¶
1 -- 2 / 3¶
red -- blue / green¶
east -- west / north"

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

Let (
        $mkReservedCounterMCC = $mkReservedCounterMCC + 1 ;
        If (
                $mkReservedCounterMCC≤ValueCount ( Valuelist1 ) or
                    $mkReservedCounterMCC≤ValueCount ( Valuelist2 ) ;
                GetValue ( ValueList1 ; $mkReservedCounterMCC ) & Delimiter &
                    GetValue ( ValueList2 ; $mkReservedCounterMCC ) &
                    Let (
                        Next = mkConcatColumns ( ValueList1 ; ValueList2 ; Delimiter ) ;
                        If ( Next≠ "" ; "¶" & Next ; "" )
                         )
                ;
                Let ( $mkReservedCounterMCC = 0 ; "" )
                )
        )

/* Note: uses private variable $mkReservedCounterMCC, don't use this variable name in your solution. */

This will take two ¶-delimited lists and concatenate with a user-defined delimiter so the output is:

list1Value1[user-specifieddelimiter]list2Value1¶
list1Value2[user-specifieddelimiter]list2Value2¶
list1Value3[user-specifieddelimiter]list2Value3¶
...
etc.

You can nest this function to add further columns.

Functionally, this means you can use it like the list() function, but include multiple fields from each record on each row of the result.

For instance,

mkConcatColumns(
mkConcatColumns(
list(field1),
list(field2),
" -- "),
list(field3),
" / ")

will give the result

"[Record 1 field 1] -- [record 1 field 2] / [record 1 field 3]¶
[Record 2 field 1] -- [record 2 field 2] / [record 2 field 3]¶
[Record 3 field 1] -- [record 3 field 2] / [record 3 field 3]¶
...

and so forth.

Comments

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

mkConcatColumns ( valueList1, valuelist2, delimiter )

Comments

Support this website.

Support this website.