CASE WHEN equivalent in AppSheet operating on a list column

[leeca]

How to categorize a list column?

For example, I have 3 rows in my table, the column type is List and column name is [Purchase Status]:

1. {To Purchase, Purchase Complete}
2. {Purchase Complete, Purchase Complete}
3. {To Purchase, To Purchase}

To further categorize my [Purchase Status], I have tried using:

SWITCH(
  ANY(UNIQUE(LIST([Purchase Status]))) = "To Purchase", "To Purchase",
  ANY(UNIQUE(LIST([Purchase Status]))) = "Purchase Complete", "Purchase Complete",
  "Partially Purchased"
)

But got the error: 

Cannot compare List with Text in (ANY(UNIQUE(LIST([Request Item Status]))) = "To Purchase")

I am trying to achieve:

If all values in the list is "To Purchase", return status = "To Purchase",

if all values in the list is "Purchase Complete", return status = "Purchase Complete",

else return "Partially Purchased"

[graham_howe · Accepted solution]

If you want to check an item against a list, use IN() instead of '='.

https://help.appsheet.com/en/?q=In 

Update: hit send too soon

Using IN() you can first check if "Purchase Complete" is in the list, if it is not, then the result will be "To Purchase". Then check if "To Purchase" is in the list, if it is not then the result is "Purchase Complete". Else the answer is "Partially Purchased"

[graham_howe · Accepted solution]

If you want to check an item against a list, use IN() instead of '='.

https://help.appsheet.com/en/?q=In 

Update: hit send too soon

Using IN() you can first check if "Purchase Complete" is in the list, if it is not, then the result will be "To Purchase". Then check if "To Purchase" is in the list, if it is not then the result is "Purchase Complete". Else the answer is "Partially Purchased"

[Suvrutt_Gurjar · Accepted solution]

If understanding of your requirement is correct, maybe you could try something like below

IFS(

AND(COUNT([Purchase Status])=2, NOT (IN("To Purchase",[Purchase Status]))), "Purchase Complete",

AND(COUNT([Purchase Status])=2, NOT (IN( "Purchase Complete",[Purchase Status]))), "To Purchase",

AND(COUNT([Purchase Status])=2, "Partially Purchased"

)

[leeca]

Thank you for the suggestion @Suvrutt_Gurjar 

I tried to modify your suggestion using my actual table names but was still unable to achieve my intended result.

IFS(
    AND(
        COUNT(SELECT(Request[Request Item Status],[requestKey]=[_THISROW].[requestKey], TRUE)) = 1,
        IN("To Purchase",[Request Item Status])
    ), "To Purchase",

    AND(
        COUNT(SELECT(Request[Request Item Status],[requestKey]=[_THISROW].[requestKey], TRUE)) = 1,
        IN("Purchase Complete",[Request Item Status])
    ), "Purchase Complete",

    COUNT(SELECT(Request[Request Item Status], TRUE, TRUE)) = 2, "Partially Purchased"
)

I ran the below expression against my Requests table and was expecting to get "2", as the result for the below row, instead I got "1".

COUNT(SELECT(Request[Request Item Status],[requestKey]=[_THISROW].[requestKey], TRUE))

The [Request Item Status] is set up with the following expression:

SELECT(Request Item[Request Item Status], [requestKey] = [_THISROW].[requestKey], FALSE)

I only have 2 values, "To Purchase" and "Purchase Complete" in the [Request Item Status] column., however, as Request and Request Item are set up as a pair of Parent-Child tables, one Request has many Request Items.

[Suvrutt_Gurjar]

@graham_howe : As it happens many times, when I was typing in my response, I did not see you had already responded. I seem to have basically expanded your suggestion into an expression. Good to know our approach is on similar lines.

[graham_howe]

Yes I'm on mobile at the moment so writing code snippets is a bit difficult. However in this instance I might be tempted to use nested IF() functions rather than IFS(). I think you could avoid the AND() functions that way.

[Suvrutt_Gurjar]

Not sure why you have got SELECT() in the expression. Where are you using this expression? If you are using it at each row level for that row and if [Request Item Status] is enumlist column, any specific reason you are using SELECT()?

From the latest screenshot you shared, just one row of your table seems to have many repeating values of "To Purchase" or "Purchase Complete". Your first post just mentioned two total items in list  for each row.

It will be difficult to share more suggestions without knowing your table columns or how you have constructed your columns and your exact requirements.

[leeca]

Thanks for pointing out that I had used SELECT incorrectly - I am sometimes confused as to when to use SELECT vs [Column Names].

The below expression is what I was after:

IFS(
    AND(
        COUNT(UNIQUE([Request Item Status])) = 1,
        IN("Purchase Complete", [Request Item Status])
    ), "Purchase Complete",

    AND(
        COUNT(UNIQUE([Request Item Status])) = 1,
        IN("To Purchase", [Request Item Status])
    ), "To Purchase"

    COUNT(UNIQUE([Request Item Status])) = 2, "Partially Purchased"
)

 If all the values in the list are "Purchase Complete", then return "Purchase Complete"

 If all the values in the list are "To Purchase", then return "To Purchase"

If any one of the values in the list is "To Purchase", then return "Partially Purchased"

[Joseph_Seddik]

Alternative expression, doing the same thing: 

IFS(
   ISBLANK([Purchase Status] - LIST(“Purchase Complete”)), “Purchase Complete”,
   ISBLANK([Purchase Status] - LIST(“To Purchase”)), “To Purchase”,
   TRUE, “Partially Purchased”
)

List Subtraction | AppSheet Help Center 

[leeca]

Thanks, this is helpful