How to combine lists into one?

expressions
(Multi Tech Visions) #1

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.” :expressionless:

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!!! (^_^)

(Multi Tech Visions) #3

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 Coile) #4

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

(Multi Tech Visions) #5

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 Coile) #6

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().

(Multi Tech Visions) #7

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 Coile) #8

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

(Multi Tech Visions) #9

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 Coile) #10

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

(Multi Tech Visions) #11

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.

(Aleksi Alkio) #12

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

(Steve Coile) #13

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. :-/

(Multi Tech Visions) #14

*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. :slight_smile:

(Steve Coile) #15

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]?

(Multi Tech Visions) #16

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

[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 Coile) #17
[notification_patient_facility].[notification_emails]
+ SPLIT(CONCATENATE(HCAs[HCA_Notification_Email_Options]), ",")
+ SPLIT(CONCATENATE(Hospitals[Hospital_Notification_Email_Options]), ",")
(Multi Tech Visions) #18

hmm… that might work. :wink:

and indeed it did!


you the man! @Steve
1 Like
(Steve Coile) #19

Hooray!

1 Like
(Multi Tech Visions) #20

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]), “,”)

2 Likes