Slice Doesn't Work with List of Values by LOOKUP

[Wajeeh]

Hi Everyone,

I am new to the AppSheet and I am experiencing the issue while using the Slice to filter the data.

I am using the IN expression to validate the result and LOOKUP to fetch the list of value from the specific cell in the Slice formula but the slicing formula is not accepting all values returned by the LOOKUP, formula only accepts the first value ignore the rest.

While test this with the Formula testing Function, the results are fine, the formula works as per plan but on run time system only accept the first value of the selected cell, I have tried a lot of formula combinations along with these base formula (LIST, SELECT, CONCATENATE and what I got in my mind) but results are the same.

If I use the CONTAIN, then the system works in case the field has fixed value which is not updating but if I use this field as a quick edit field (set to Enum List) the system returns the same issue.

Kindly guide

[Aleksi]

May I ask what kind of IN() formula have you tried to use?

[Wajeeh]

Like

IN([Column to validate] , LOOKUP(“Lookup value” , “Table”, “Lookup Column” , “Result Column”)

I also have used LIST(LOOKUP Formula) also CONCATENTE(LOOKUP Formula) and Select too.

[Steve]

LOOKUP() likes to return a single value, not a list, so a LOOKUP() that returns the value of a List or EnumList column will be a Text value that must be SPLIT():

IN(
  [Column to validate],
  SPLIT(
    LOOKUP(
      "Lookup value",
      "Table",
      "Lookup Column",
      "Result Column"
    ),
    " , "
  )
)

Myself, I prefer this pattern instead:

ISNOTBLANK(
  FILTER(
    "Table",
    AND(
      ("Lookup value" = [Lookup Column]),
      IN([_THISROW].[Column to validate], [Result Column])
    )
  )
)

[Wajeeh]

Thanks Steve,
Soon I’ll test these.