Referenced list

[TalkNet_TechnoS]

I have a comma separated text, product of an Enum list.
which corresponds to Key Id’s of another table.
I want to convert it to a referenced List, in order to obtain the data corresponding to each Key Id, promptly the Price column.

It occurred to me to make a Virtual Column [SPLITCOL] with the following expression:
SPLIT ([CSV], “,”)
Then in another column put
[SPLITCOL]. [PRICE]

But it doesn’t allow me to assign a list as a REF type.
How should I do to get other data from the table from a CSV list?

[WillowMobileSys]

One way I know to do this is to wrap your SPLIT() function with an INDEX() function and feed that into a LOOKUP(). It would look like this.

LOOKUP(
       INDEX(
             SPLIT([CSV], ","), 
             [Chosen Index]
       ), 
    "Other Table", 
    "Key ID Column", 
    "Price"
)

I tried this out in a little tester app so I know this does work.

This of course assumes you know which Indexed item from your CSV list you which to get the Price for. But you would need to know that for any method you use.

[TalkNet_TechnoS]

Actually, what I need is to add the price of all the items selected in the Enum List, so I thought about getting the price list of each item to wrap it in a SUM () expression.
I tried to do it with SELECT () and IN (), to select from the Options table that feeds the EnumList, those that were selected, and after obtaining the price list of the selected options, add them. But I couldn’t find the right way.

[WillowMobileSys]

Ok, yes this is a bit of different problem with a different solution. You definitely need to use SUM(), SELECT() and IN() plus SPLIT(). When you SPLIT() the column keep in mind that you have a list of your ID values.

So I believe you you can create an expression as follows:

SUM(
    SELECT(Other Table[Price], 
           IN([Key ID Column], SPLIT([CSV], ","))
    )
)

I haven’t used SPLIT() in this way and I haven’t tested this but I see no reason it shouldn’t work. Give it a try and let me know!!

[TalkNet_TechnoS]

Excellent! Thank you so much

[Steve]

TalkNet_TechnoStore:

I have a comma separated text, product of an Enum list.

An EnumList is already a list. You don’t need to use SPLIT() to use an EnumList value with IN().

The following are equivalent (assuming Haystack is of type EnumList), but the one using SPLIT() is less efficient:

IN([Needle], SPLIT(",", [Haystack]))

IN([Needle], [Haystack])