List of List Problem with IN() Statement

[ScottE]

EDIT SOLUTION:  I was using Items[Contacts] as my haystack instead of [Contacts]  Grrr!!!!

I have a table "Items", whose Key is Column "ItemID", has several "EnumList" type columns populated from my "Contacts" table.  This "Items" table has a VC List ("Contacts") with a formula that combines these 3 EnumLists ([Names1]+[Names2]+[Names3]), into a single VC field, ie. 1 field with all of the ContactID's relevant to that item.

My goal is to have a VC List in my "Contact" table to SELECT all "Items" that have a Contact's "ContactID "in the Items. 

SELECT(
    Items[ItemID],
    IN(
        [_THISROW].[ContactID],
        Items[Contacts]
    ):TRUE
)

The above makes sense to me, but it does not work.

I saw another post where the "haystack" for IN() was itself an EnumList, which required the use of SPLIT(CONCATENATE([haystack])), but that's not my case, nor did it work.

Suggestions?

[SkrOYC]

---

@ScottE wrote:

The above makes sense to me, but it does not work.

Don't feel bad about it, it happens to all of us.

---

@ScottE wrote:

I saw another post where the "haystack" for IN() was itself an EnumList, which required the use of SPLIT(CONCATENATE([haystack])),

---

Yep, that's the solution.

Since [Contacts] is a list, Items[Contacts] is a List of Lists.

Try this:

FILTER(
  "Items",
  IN(
    [_THISROW].[ContactID],
    SPLIT(CONCATENATE(Items[Contacts]), " , ")
  )
)

[ScottE]

@SkrOYC Thank you for the fast reply. 

Since I am using a "Real" list and not an EnumList as my haystack, I didn't think I needed the Split/Cat code; nevertheless, I get the same empty results from the TEST FEILD page with and without the Split(Concatenate([haystack]),",") code.

It seems like a real stretch of the imagination to think this could be some closed loop of Contact References.

This one has had me stumped for some time.

[SkrOYC]

Make sure your SPLIT() is written correctly.

Try copy-n'-paste the code I made before

[ScottE]

I did copy/paste, I even tried removing the whitespace in the split delimiter  " , " >> "," because my test results of Items[Contacts] does not have any spaces.  No luck.

[SkrOYC]

Common Problems

The first argument to SPLIT() should be a textual value. If not provided a textual value, the non-textual value will be converted to Text before processing.

Of particular note, list types (List and EnumList) will be converted to Text by joining the component values with a single comma (,). For instance, the list LIST("Banana", "Apricot", "Grapes") will be processed by SPLIT() as if entered as "Banana,Apricot,Grapes".

The approach SPLIT() uses differs from how list values are converted to Text in other contexts. To get the other approach, use CONCATENATE() or TEXT() to manually convert the list to Text. For instance, CONCATENATE(LIST("Banana", "Apricot", "Grapes")) will be processed by SPLIT() as if entered as "Banana , Apricot , Grapes", with the typical space-comma-space separator.

https://help.appsheet.com/en/articles/2357340-split

[SkrOYC]

Since the docs tell us the expression should be right, make sure the info inside the [Contacts] column are exactly the same values from the [Contact ID] column

Share the results of just the CONCATENATE() part please

[TeeSee1 · Accepted solution]

For this in my little sample app

I have...

For l_items

UNIQUE(
 SELECT(
  ITEMS[id],
  IN([_THISROW].[id],[c_list])
 )
)

For l_items_text

TEXT([l_items])

 Hope this will give you some more insight..

[ScottE]

@SkrOYC  @TeeSee1   DOH!  Smacks head!

The answer is quite subtle in @TeeSee1 post.  I actually built a test app and had the same errors, but I knew it was a problem with my haystack List. It should be [Contacts]  and not Items[Contacts].

FILTER(
    "Items",
    IN(
        [_THISROW].[ContactID],
        [Contacts]
    )
)

Thank you both so very much.