Cannot Compare List with Text (Enum List column type vs. Price column type)

AppSheet AppSheet Q&A
1

I am working on an App for a car detailing company and I’m trying to create an initial value expression to set a Price type column [AMOUNT] based on the value of an Enum List type column [DETAIL]. The desired behavior is:

  • If the value of [DETAIL] is “Exterior” set initial value of [AMOUNT] to 100.00
  • If the value of [DETAIL] is “Interior” set initial value of [AMOUNT] to 80.00
  • If the value of [DETAIL] is “Exterior , Interior” set initial value of [AMOUNT] to 180.00
  • If the value or [DETAIL] is blank, then initial value of [AMOUNT] is blank or zero

I thought I could achieve this through an IF() expression, but I am getting the message Cannot compare List with Text in ([DETAIL] = “INTERIOR”). I believe the issue is because [DETAIL] is an Enum List column type, but I can’t seem to figure out a workaround.

My current expression is:

IF(
[DETAIL] = “EXTERIOR”, “100.00”, “”,
[DETAIL] = “INTERIOR”, “80.00”, “”,
[DETAIL] = “INTERIOR , GLASS”, “180.00”, “”
)

2

You cannot put a series of IF() tests in the way you have done.

When you have a column defined as a List type, you would need to check if the literal value is “IN” the list. AND when there is more than one to check, it is best to check if the column contains the literal values individually, in case they are not in a specific order.

Lastly I would recommend using an IFS() function instead with an expression as follows (note I am assuming that you are trying to assign Decimal values:

IFS(
    IN("EXTERIOR", [Detail]), 100.00,
    AND(IN("INTERIOR", [Detail]), IN("GLASS", [Detail])), 180.00,
    IN("INTERIOR", [Detail]), 80.00,
    TRUE, 0.00
)

Note that order matters in the above. The second and third both have INTERIOR as an item to check so you would want to place the more limiting check first.

Also note that these checks only care if the literal values are included - not that they are the ONLY values in the [Detail] list. If you want to be that [Detail] list contains those values only then you would need strengthen the condition as follows:

IFS(
    AND(IN("EXTERIOR", [Detail]), COUNT([Detail]) = 1), 
        100.00,

    AND(IN("INTERIOR", [Detail]), IN("GLASS", [Detail]), COUNT([Detail]) = 2), 
        180.00,

    AND(IN("INTERIOR", [Detail]), COUNT([Detail]) = 1), 
        80.00,

    TRUE, 
        0.00
)

Some more details about IFS():

https://support.google.com/appsheet/answer/10107912?hl=en

1 Like
3

Your expression works perfectly. Thank you so much for your reply, I really appreciate how thorough you were. May I ask how “TRUE” contributes to the expression at the end?

1 Like
4

It omits the duplicates

5

TRUE is the default condition. If none of the other conditions are met, the function falls through to TRUE and assigns a default value of zero - as opposed to leaving an unassigned column blank.

If you should want the column to stay unassigned when no conditions are met, then remove the TRUE line.

2 Likes