Name & Parameters: [color:red][big] ListMatrix ( ListOne ; ListTwo ; separator ; counter1 ; counter2 ; result ) [/big]

Description: While the FileMaker function "Combinations" predits the amount of combinations from two lists, ListMatrix actually outputs all unique combinations. You can also add a ListOne offset, specify a separator for the values from ListOne and ListTwo, and add a header to the result.

Sample Input:

ListMatrix( "a¶b¶a", "1¶2¶3¶4¶2"; " and "; ""; ""; "Combinations of Alpha and Nums:¶" )

Results:

Combinations of Alpha and Nums:

a and 1

a and 2

a and 3

a and 4

b and 1

b and 2

b and 3

b and 4

Recursive: yes

Formula:

Let( [

numListOne = ValueCount( ListOne );

numListTwo = ValueCount( ListTwo );

counter2 = Case( counter2 < 1; 1; counter2 >= numListTwo; 1; counter2 + 1);

counter1 = Case( counter1 < 1; 1; counter2 = 1; counter1 + 1; counter1 );

result = If( counter1 + counter2 > 2; result & “¶” ) & If( PatternCount( result; ( GetValue( ListOne; counter1 ) & separator & GetValue( ListTwo; counter2 ) ) ) = 0; ( GetValue( ListOne; counter1 ) & separator & GetValue( ListTwo; counter2 ) ) )

];

Case( counter1 >= numListOne and counter2 >= numListTwo; result; ListCombiner( ListOne; ListTwo; separator; counter1; counter2; result ))

) 

Required Functions:

Author(s): gephry

Date: 02/06/07

Credits:

Disclaimer:

FM Forums does not endorse or warrantee these files are fit for any particular purpose. Do not post or distribute files without written approval from the copyright owner. All files are deemed public domain unless otherwise indictated. Please backup every file that you intend to modify.

ListMatrix.zip

Edited February 9, 200718 yr by Guest
Attachment didn't upload

Might as well ask this in the meantime:

I tried to program it so that the only parameters you send to it were ListOne, ListTwo, and separator. But, when defining the custom function it said counter1, counter2, and result were invalid parameters. So, I ended up specifying them as parameters, of which I'd prefer not to.

How can I define this function with those last three variables being internal parameters only (so they do not need to be specified when the function is called)?

Whoops, I lied. I must have modified that script so you couldn't add a header to the result as I show in the example. This version of the script however will let you add a header (no ¶ necessary after the header):

Let( [

numListOne = ValueCount( ListOne );

numListTwo = ValueCount( ListTwo );

counter2 = Case( counter2 < 1; 1; counter2 >= numListTwo; 1; counter2 + 1);

counter1 = Case( counter1 < 1; 1; counter2 = 1; counter1 + 1; counter1 );

result = result & "¶" & If( PatternCount( result; ( GetValue( ListOne; counter1 ) & separator & GetValue( ListTwo; counter2 ) ) ) = 0; ( GetValue( ListOne; counter1 ) & separator & GetValue( ListTwo; counter2 ) ) )

];

Case( counter1 >= numListOne and counter2 >= numListTwo; result; ListMatrix( ListOne; ListTwo; separator; counter1; counter2; result ))

)

Here's an version that lets you use an evaluate statement on the header:

// ListMatrixEval( ListOne; ListTwo; separator; counter1; counter2; result )

Let( [

numListOne = ValueCount( ListOne );

numListTwo = ValueCount( ListTwo );

result = Case(counter2 = "eval"; Evaluate(result); result);

counter2 = Case( counter2 < 1; 1; counter2 >= numListTwo; 1; counter2 + 1);

counter1 = Case( counter1 < 1; 1; counter2 = 1; counter1 + 1; counter1 );

result = result & "¶" &

If(

PatternCount( result; ( GetValue( ListOne; counter1 ) & separator & GetValue( ListTwo; counter2 ) ) ) = 0;

GetValue( ListOne; counter1 ) & separator & GetValue( ListTwo; counter2 )

)

];

Case( counter1 >= numListOne and counter2 >= numListTwo; result;

ListMatrixEval( ListOne; ListTwo; separator; counter1; counter2; result )

))