Compare two lists only one way?

AppSheet AppSheet Q&A
, ,
1

Hi

I want to have a comparison in a “Valid if” field, where I want to filter all the rows from List A that has an equivalent in List B, but not necessarily the other way around. Let’s say

CorneliusH_0-1728766365859.png
CorneliusH_0-1728766365859.png342×177 2.06 KB

I tried a few formulas and ideas in a Virtual Column and came up with this:

COUNT([AGREGAR_MEZCLA].[LIST_1])-
COUNT(INTERSECT(
LIST_2),[AGREGAR_MEZCLA].[LIST_1]
))=0

Basically I am counting the elements of list 1, and counting the shared elements of both lists; if the result is 0, it is fine; else it should not appear in the Valid If field. However when I paste that formula in the “Valid If” field of the other column and enclose it in a “Select” to get the desired Valid list, it gives me an error:

INTERSECT does not accept a list of list.

How can I avoid that and keep the basic formula? or maybe a better solution?

2

Try something like this:

ISBLANK([List 1] - [List2])

Returns TRUE is [List 2] includes at least every item in [List 1].

3

Same result. “Cannot compare list with list of lists”.

Maybe it is because list 2 is a dereference to a different row from the same list 1. What I want to achieve is, to have a list of items that share some components in common. It is something like the ABO blood type: O can be added to A, but A is not ok for O type.

Item Components Compatible Items
Item 1 ABC Item 1, Item 2, Item 8
item 2 ABC Item 1, Item 2, Item 8
Item 3 ABCD Item, 1, Item 2, Item 3, Item 4, Item 5, Item 8, Item 9
Item 4 ABCD Item, 1, Item 2, Item 3, Item 4, Item 5, Item 8, Item 9
Item 5 ABD Item 5, Item 8, Item 9
Item 6 ABG Item 6, Item 8, Item 9
Item 7 ABDG Item 5, Item 6, Item 7, Item 8, Item 9
Item 8 AB Item 8
Item9 ABD Item 5, Item 8, Item 9

It feels like another dead end I have to leave open open for now.

4

Please provide screenshots of the column configurations of the columns involved.