How to combine lists into one?

[MultiTech]

I’ve got 3 enumlists in different tables that I need to combine together in an enum on another table for it’s options.

So table’s A, B, and C have lists of email addresses that people can choose from.
In table D - all those emails need to be available as options in an enum.

I’ve got the following formula, but it’s not parsing things as I need:

split([notification_patient_facility].[notification_emails] + HCAs[HCA_Notification_Email_Options] + Hospitals[Hospital_Notification_Email_Options], “,”) - list("")

this gives me the following:

The first element of the list ([notification_patient_facility].[notification_emails]) is split into options, but the imported table options (HCAs[HCA_Notification_Email_Options] + Hospitals[Hospital_Notification_Email_Options]) are imported as a “list-option.”

I tried not including the SPLIT(), but then it was just a list of lists. I’ve also tried different split denominators: " , " (space, comma, space) and ", " (comma, space) - but all result in the same thing with the two table imports being list-options.

I also tried wrapping the whole thing in ANOTHER split, but it resulted in the same thing again (curiously).

@Aleksi help!!! (^_^)

[MultiTech]

I’ve been looking at the data types:

the first is a legit enumlist
the second and third and lists of enumlists.

That’s why the first one parses out correctly, but the others are pulling in each row as an option.

I tried splitting the second options in part of the list combination:
[notification_patient_facility].[notification_emails] + SPLIT(HCAs[HCA_Notification_Email_Options], " , ") + SPLIT(Hospitals[Hospital_Notification_Email_Options], " , ")

But it’s resulting in the same thing.

ALSO: Just tried wrapping the whole thing again:
SPLIT([notification_patient_facility].[notification_emails] + SPLIT(HCAs[HCA_Notification_Email_Options], " , ") + SPLIT(Hospitals[Hospital_Notification_Email_Options], " , "), “,”)

but it’s always resulting in the same thing.

[Steve]

What does CONCATENATE([notification_patient_facility].[notification_emails] + HCAs[HCA_Notification_Email_Options] + Hospitals[Hospital_Notification_Email_Options]) look like?

[MultiTech]

Steve:

CONCATENATE([notification_patient_facility].[notification_emails] + HCAs[HCA_Notification_Email_Options] + Hospitals[Hospital_Notification_Email_Options])

Resulted in an error. can’t have a concatenate in the valid if (or the suggested values).

I moved the formula from the valid if to the suggested values, so others could be added, and you see the list of list much better:

[Steve]

MultiTech_Visions:

Resulted in an error. can’t have a concatenate in the valid if (or the suggested values).

How about a new VC with that value? I’m interested to see if there’s something in the merged list and lists of lists that would interfere with SPLIT().

[MultiTech]

Steve:

How about a new VC with that value?

Unfortunately this is a live app, with active users and a TON of data, so something like that is out of the question.

I was thinking it might be something about the delimiter I use in the SPLIT, that’s why I was trying different things. To date I’ve tried:

“,”
", "
" , "

But each results in the same thing, seems the system ignores the spaces and just uses the comma.

[Steve]

Wouldn’t surprise me if the list-of-lists is introducing a problem. Is that a construct you’ve used successfully in the past?

[MultiTech]

Steve:

list-of-lists is introducing a problem. Is that a construct you’ve used successfully in the past?

It’s always been a problem. I’ve never been able to successfully solve it, usually I find another way to bring about the behavior I’m looking to create.

[Steve]

MultiTech_Visions:

Unfortunately this is a live app, with active users and a TON of data, so something like that is out of the question.

You could create but not save (or delete before save) the VC just to get into Expression Assistant.

[MultiTech]

The problem here is that you can’t tell how things are split in the previous page; the rendering element that makes what we see in the testing page doesn’t split things differently - each item is separated individually.

But when you open the app with the things saved, that’s when you see the items of the lists for what they really are:

If you compare the two pictures, you can see where things are split in the testing page - but there’s no way to actually tell by that unless you can see it elsewhere.

[Steve]

A Valid_If expression of the form:

[notification_patient_facility].[notification_emails]
+ SPLIT(HCAs[HCA_Notification_Email_Options], ",")
+ SPLIT(Hospitals[Hospital_Notification_Email_Options], ",")

seems to works for me. 😕

[MultiTech]

Steve:

[notification_patient_facility].[notification_emails] + SPLIT(HCAs[HCA_Notification_Email_Options], “,”) + SPLIT(Hospitals[Hospital_Notification_Email_Options], “,”)

*There’s nothing in the “[notification_patient_facility].[notification_emails]” for that picture, just shows the result of the splits.

@Aleksi filtering duplicates yes, also filtering blanks.

[Steve]

So each item in the HCAs[HCA_Notification_Email_Options] List is not itself a List, but merely Text containing a comma-separated list of email addresses?

What is each item in [notification_patient_facility].[notification_emails]?

[MultiTech]

Steve:

What is each item in [notification_patient_facility].[notification_emails] ?

The column itself is an actual enumlist, meaning a single column from the facility table containing a list of emails.

Steve:

HCAs[HCA_Notification_Email_Options]

[HCA_Notification_Email_Options], the column in the HCAs table, is itself an enumlist (containing a list of emails).

So “HCAs[HCA_Notification_Email_Options]” is a list of those lists.

[Steve]

[notification_patient_facility].[notification_emails]
+ SPLIT(CONCATENATE(HCAs[HCA_Notification_Email_Options]), ",")
+ SPLIT(CONCATENATE(Hospitals[Hospital_Notification_Email_Options]), ",")

[MultiTech]

hmm… that might work.

and indeed it did!

you the man! @Steve

[Steve]

Hooray!

[MultiTech · Accepted solution]

So to finalize everything and provide an answer to anyone looking at this string:

If you’re trying to use a “list of lists” as options for a dropdown - wrap it in a CONCATENATE() and SPLIT() by the comma, this gives you a list of your options in the correct format.

From there you can use that list in any normal way: as part of a formula or combine your lists into one; it’s all about transforming the data into the appropriate type.

TableA[Enumlist_A] + TableB[Enumlist_B]

would need to be:
SPLIT(CONCATENATE(TableA[Enumlist_A]), “,”) + SPLIT(CONCATENATE(TableB[Enumlist_B]), “,”)

[Aleksi]

@MultiTech_Visions Do you need to filter duplicates away or is there any?

[OptimiX_XcrY]

Thank you for this thread , your great idea will save me days of work.

I am trying to create an enum selection from a text cell containing words divided by "," . 

It shows fine in app the enumlist, for selections , but after I select my options , the column is still blank when I hit Done. 

Any tips? 

This is my formula to create the enumlist

split(concatenate(SELECT(x tech options[neconformitate], [_THISROW].[serie doc] = [serie doc]));",")

This is my testing table from wich I gather the options

They show up fine :

and blank when I hit done(gata)

Thank you

[OptimiX_XcrY]

Solved by making the formula :

 in([_this];split(concatenate(SELECT(x tech options[neconformitate], [_THISROW].[serie doc] = [serie doc]));",")) . 

Don't yet understand it , but i read the troubleshoot from https://support.google.com/appsheet/answer/10107949?hl=en