Need all combinations of 3 multi value fields

HCL Domino Domino Forum
1

Let’s say I have three fields on a form. I need to use the combination of these three fields as a key in a dblookup. BUT, each of the three fields are multi value fields. How can I get every combination? Let’s say on my document have the following fields with these values:

STATE FIELD = WI and MN

TYPE FIELD = 1 and 2

LETTER FIELD = A and B

I want to end up with all 8 combinations

WI 1 A

WI 2 A

WI 1 B

WI 2 B

MN 1 A

MN 2 A

MN 1 B

MN 2 B

Then I’m hoping I can use each of these 8 values to do a dblookup to find a name that corresponds with MN 2 A for example.

Any ideas?

2

Subject: Combos := STATE *+ TYPE *+ LETTER;

3

Subject: Need all combinations of 3 multi value fields

Start by searching the dev help for “Permutation”. You might succeed with looping with @functions. I would lean to doing this in LotusScript.

4

Subject: RE: Need all combinations of 3 multi value fields

Thanks all! Never had the need to permutate before!

Now I have my list of combinations in a field and I want to use each of those combinations in the @DBlookup.

Example: The field Combo now contains a list of “WI 1 A”; “WI 2 A”; etc. I want to do a @DBLookup with each entry in the list. Possible?

5

Subject: Just do the @DbLookup specifying the Combo list as the key. Use the parm [FailSilent].

6

Subject: RE: Just do the @DbLookup specifying the Combo list as the key. Use the parm [FailSilent].

Wow … I must be missing a little something…

If I have the key field equal “WI 1 A”, I return the correct value.

If I have the key equal “MN 2 A”, I return the correct value.

But if the key has two items in a text list “WI 1 A”; “MN 2 A”, I get ‘entry not found’ (or nothing if I add the FailSilent).

The resulting field does have the ‘allow multi values’ checked. This should work, right??

7

Subject: Are you sure it is a text list?

Your example had the wrong separator. “WI 1 A”; “MN 2 A” should be “WI 1 A” : “MN 2 A”. Can you test this with a hardcoded sample like above?i.e.

Key1 := “WI 1 A” : “MN 2 A”;

Ret1 := @DbLookup(“”;“”; “YourView”; Key1; 2);

@Prompt([OK]; “Ret1”; @Implode(Ret1; ", "))

8

Subject: Are you sure it is a text list? THANKS!

THANKS!!! That’s the nudge I needed!

The ‘little’ thing I was missing was that the Combo field didn’t have the ‘Allow Multiple Vales’ checked! It still displays with semi-colon but acts differently (i.e., correctly).

9

Subject: Need all combinations of 3 multi value fields

STATE := “WI”: “MN” ;TYPE := “1” :“2” ;

LETTER := “A” : “B” ;

combinations := state *+ type *+ letter ;

10

Subject: Need all combinations of 3 multi value fields

Look at permuted operations on lists (in the help)

Regards,

Simeon