Formulas to reference multiple values in a separate field

HCL Domino Domino Forum
1

Is there a way for formulas, which refer to selections in a multiple value field, to work together EVEN WHEN multiple selections are made? Not sure that question makes sense even to me. Let’s try the Example route.

  • Field 1 is a Dialog list field in which multiple selections can be made. The choices are A, B, C, D, E.

  • Field 2 is also a Dialog list field in which multiple selections can be made. The selections in Field 2 are based on the selections in Field 1 (ex. If A chosen in Field 1, selections in Field 2 are SEC, Big 10, ACC. If B chosen in Field 1, selections in Field 2 are NFL, NBA, MLB).

What I want to happen is the values for all selections in Field 1 to show as selections in Field 2. (i.e. If A and B chosen in Field 1, selections in Field 2 would be SEC, Big 10, ACC, NFL, NBA, MLB).

Thanks

2

Subject: Formulas to reference multiple values in a separate field

Yep.

Here’s code where fld1 is the initial selection list and fld2 is derived from whatever is selected in fld1. Be sure to turn on the ‘refresh’ settings for each field so changes are immediate.

Both fields are dialogs.

fld2 (below) uses formula for choices.

There may be other more clever ways to do this, but this works.

Doug

fromList := “a”:“b”:“c”:“d”;

ToList := “123” : “234” : “345” : “456”;

@Replace(fld1;fromList;toList)

3

Subject: RE: Formulas to reference multiple values in a separate field

Perhaps I’m doing something wrong, confused, or didn’t explain correctly the first time (could be all of the above), but with this formula…when I select “a” and “b” in Field1, my choices in Field2 are “a” and “b”. Shouldn’t my choices in Field2 be “123” and “234”?

4

Subject: RE: Formulas to reference multiple values in a separate field

I think you probably need something like this:

fromList := “a”:“b”:“c”:“d”:“e”;

ToList := “SEC~Big Ten~ACC” : “NFL~NBA~MLB” : “3~4~5” : “4~5~6” : “7~8~9”;

@Unique(@Explode(@Implode(@Replace(fld1;fromList;toList);“~”);“~”))

The idea is that you are doing the @Replace to get a list of ~-delimited strings. You @Implode that to create a single longer ~-delimited string. Then you @Explode it back into a single list.

The @Unique just accounts for the possibility that the same entry might appear in two of ~-delimited strings. If that’s not possible, you could dispense with it.

Note: the above is untested. Looks right to me, but no warranties :wink:

-rich

5

Subject: RE: Formulas to reference multiple values in a separate field

Sorry, but I get the same result as before (i.e. if A and B selected in Field1, choices in Field2 are “A” and “B”.). Any other suggestions before I rethink how I’m trying to set this up?

6

Subject: RE: Formulas to reference multiple values in a separate field

The formula Rich suggested works. Please post the EXACT formula you are using. (By the way, if the values in your first field are uppercase and you are replacing only lowercase values, you may find that the uppercase values aren’t getting replaced. And that would be because your formula isn’t tying to replace uppercase characters.)

7

Subject: RE: Formulas to reference multiple values in a separate field

Good call…problem was uppercase v lowercase. Obviously in over my head. I was beginning to think I’d stumped even you folks…should have known better (I know…oh ye of little faith). BIG THANKS to all of you.