5 fields - calculate all possible combinations

[dmontano]

I have 5 fields in 1 table where I need to create all possible combinations of their values and present them in a drop down list so a User can choose 1 of the combinations.

The way I am writing the calculation seems way too hard (I am writing each combination out). I am thinking that someone might have a better way. The kicker is that only one of the fields must be populated. So, in some cases - all five fields will have a value, in other cases, maybe only one, two or three fields will be populated. So, I also have to account for extra blank spaces to be removed due to concatenating.

Here is some make-believe data:

Field 1: Apple

Field 2: Tree

Field 3: Stove

Field 4: Sink

Field 5: Lake

What I am trying to achieve in a calculation:

Apple

Apple Tree

Apple Tree Stove

Apple Tree Stove Sink

Apple Tree Stove Sink Lake

Apple Stove

Apple Stove Sink

Apple Stove Sink Lake

Apple Sink

Apple Sink Lake

Apple Lake

Lake

Tree

Tree Stove

Tree Stove Sink

Tree Stove Sink Lake

Stove

Stove Sink

Stove Sink Lake

Sink

Sink Lake

The approach I came up with for just ONE of the possible combinations in the calc is:

If ( Field 1 ≠ "" ; Field 1 ) & If ( Field 2 ≠ "" ; " " & Field 2 ) & If ( Field 3 ≠ "" ; " " & Field 3 ) & If ( Field 4 ≠ "" ; " " & Field 4 ) & If ( Field 5 ≠ "" ; " " & Field 5 )

This obviously has problems that need to be worked out, such as if there is no Field 1 or Field 2 - I just created a blank space at the beginning of Field 3. I thought a (trim) function but that has problems too. My big concern is finding a way to write something where I do not have to "think" of all combinations and write explicit calcs.

[Matthew F]

The way I am writing the calculation seems way too hard.

Since your task is algorithmic it would be much simpler to write a script.

[Vaughan]

If there are only ever 5 fields then the combinations are known and finite.

Create a hard-coded string that lists all the combinations paragraph delimited, using the numeral 1 to 5 to represent each field:

1

2

3

4

5

12

13

14

15

etc

Then use the substitute function to replace each numeral with the value from the field.

[TheTominator]

To remove blank fields gracefully, use List().

List(Field 1; Field 2; Field 3; Field 4; Field 5)

To list the combinations of the values of the list above, use the following custom function. In your case the "separator" would be a space (" "). listOfValues would be

List(Field 1; Field 2; Field 3; Field 4; Field 5).

ListCombinations(listOfValues; separator) =

Let(

[

listLength = ValueCount(listOfValues);

lastValue = GetValue(listOfValues; listLength);

lesserList = Case(

listLength > 1; ListCombinations(LeftValues(listOfValues; listLength - 1); separator);

""

)

];

Case(

listLength = 0; "";

listLength = 1; RightValues(listOfValues; 1); /* Ensures ¶ at end */

lastValue & "¶" &

lesserList & /* already terminated by ¶ */

Substitute(lesserList; "¶"; separator & lastValue & "¶")

)

)

A similar question and responses is in this earlier post.

http://www.fmforums.com/forum/showtopic.php?tid/214260/

Edited December 2, 2010 by Guest

[dansmith65]

Do you mind telling what you need this calculation for?

I'm hoping that either;

1) I'll learn an interesting application of a FileMaker database

or

2) We can help you accomplish your task a simpler way.

[dmontano]

Thanks everyone for the feedback.

Hi Tominator,

I had used List earlier but I could not get it to work with the "Let" function. I started leaning towards the "Let" function just because it was getting a little wild trying to understand what was going on when I threw in "IF" statements.

I am going to investigate what you posted when I get home tonight to see if I can make sense of it. I can not decipher well - but it looks like I could use that elsewhere for similar functionality - which is exactly what I need to do. Like I said - I will need some time to look at it. Thanks for your input!

Hi Dansmith,

Below is the calculation I came up with last night and it uses the actual fields names - I think you will be able to see what I am trying to accomplish. Calc I came up with seems to work (haven't tested thoroughly yet - and would not be surprised if I have a problem). Here it is:

**********************************************************

Let (

[

legal_given = If ( IsEmpty ( person_name_given_first_legal ); "" ; person_name_given_first_legal & " ") ;

legal_surname_1 = If ( IsEmpty ( person_name_surname_middle_legal ); "" ; person_name_surname_middle_legal & " ") ;

legal_surname_2 = If ( IsEmpty ( person_name_surname_02_legal ); "" ; person_name_surname_02_legal & " ") ;

legal_surname_3 = If ( IsEmpty ( person_name_surname_03_legal ); "" ; person_name_surname_03_legal & " ") ;

legal_family = If ( IsEmpty ( person_name_family_last_legal ); "" ; person_name_family_last_legal & " ")

]

;

Trim ( legal_given & legal_surname_1 & legal_surname_2 & legal_surname_3 & legal_family )

)

***********************************************************

I know I can have a separate table of "names", but that still does not solve the issue of how to "present" a list of "name_combinations" that I would like a User to select from to declare the selection as the "Name of Record".

I am all ears for other approaches.

Thanks