10 Replies Latest reply on Aug 13, 2014 9:31 AM by philmodjunk

Separating list values

Title

Separating list values

Post

PROBLEM:

I have a table like this example:

List1     List2   List3

Person1                a            1         x

Person2                              2         y

Person3                b

LIst1,2,3 are fields with dropdown list of values

I have a calculation field showing lists values:

a 1 x

I want to have them separated with ",". How can I do this?

• 1. Re: Separating list values

Substitute ( List ( list1 ; list2 ; list3 ) ; ¶ ; ", " )

• 2. Re: Separating list values

I don't know how to use tjis "substitute" function...

Maybe few more details.

I have 4 list of values.

For example:

WORLD

1 Big Boss

2 Small Boss

3 Nobody

COUNTRY

1 Driver

2 Doctor

3 Writer

TOWN

1 Sportsman

2 SInger

3 Postman

HOME

1 Father

2 Mother

3 Child

I have a list of persons. Some of them have all fields filled, few persons has all fields empty.

I need to have a calculation field with all choosen values separated with ","

Mine is very simple:

If ( IsEmpty ( World ) ; "" ; Middle ( World ; 3 ; 30 ) & ", " )
&  If ( IsEmpty ( Country ) ; "" ; Middle ( Country ; 3 ; 30 ) & ", ")
&  If ( IsEmpty ( Town ) ; "" ; Middle ( Town ; 3 ; 30 ) & ", ")
&  If ( IsEmpty ( Home ) ; "" ; Middle ( Home ; 3 ; 30 ) )

I use this [Middle] function to get rid off numbers. I need numbers, because in another place of my database I use this lists of values to sort records. Without them "Nobody" is before "Small boss", etc.

My calculation field works quite good, but it adds "," if 4th field is empty. I tried to use conditional formating with "Right" function, but id doesn't work.

• 3. Re: Separating list values

That's why I used the list function. It automatically drops out list elements when a specified field or expression is empty. Where I have put a field, you can also put an expression. Try it and see how it works for you.

But I don't understand why you need to use the Middle function in your example.

• 4. Re: Separating list values

Without "Middle" function I get calculation field:

1 Big Boss; 3 Child

I don't want this numbers, only:

Big Boss, Child

• 5. Re: Separating list values

You can use RightWords ( Field, WordCount ( field ) - 1 )

to get the same result. This can allow for a bit more variation, such as a two digit number, and still work

And your expression or mine can be used inside the list function. If it returns null, that element of the list is omitted and you don't get the extra comma that you don't want.

• 6. Re: Separating list values

This

Substitute ( List ( list1 ; list2 ; list3 ) ; ¶ ; ", " )

doesn't work :(

• 7. Re: Separating list values

But this:

Substitute ( List ( Middle ( World ; 3 ; 30 ) ; Middle ( Country ; 3 ; 30 ) ; Middle ( Town ; 3 ; 30 ) ; Middle ( Home ; 3 ; 30 ) ) ; ¶ ; ", " )

Should work, provided that each Middle function returns a null value when it should.
• 8. Re: Separating list values

Now it works just great!

Thanks a lot once again!

• 9. Re: Separating list values

Is it going to work if I change dropdown menu field to multiple choice field? Now I see that in few cases I need two values from this same list...

• 10. Re: Separating list values

You'll need to modify your expressions inside the list since the Middle function will no longer work as specified. The presence of that number value to be excluded will make that a pain to do--might need a custom function.