When an expression returns a list of lists, this should be obvious in Table View Data and in Expression Assistant Test. The current way of displaying lists is error prone when testing expressions. Here is an example that explains the problem :
Here are the formulas and remarks for improved display clarity :
Table : emp
Virtual Column : v_has_visited_NY
App formula : =IN(“NY”, [emp_Visited])
Remark : OK
Table : result
Virtual Column : v_all_residences_list
App formula : =SELECT(emp[emp_Residence], TRUE)
Current display : WA , KY
Remark : FOR CLARITY, SHOULD BE DISPLAYED AS : { WA , KY }
Table : result
Virtual Column : v_john_residence_list
App formula : =SELECT(emp[emp_Residence], [emp_Name] = “John”)
Current display : WA
Remark : FOR CLARITY, SHOULD BE DISPLAYED AS : { WA }
Table : result
Virtual Column : v_john_residence_element
App formula : =ANY(SELECT(emp[emp_Residence], [emp_Name] = “John”))
Current display : WA
Remark : OK
Table : result
Virtual Column : v_all_visited_list_of_lists
App formula : =SELECT(emp[emp_Visited], TRUE)
Current display : NY , CA , IH , FL , MA
Remark : FOR CLARITY, SHOULD BE DISPLAYED AS : { { NY , CA , IH } , { FL , MA } }
Table : result
Virtual Column : v_john_visited_list_of_one_list
App formula : =SELECT(emp[emp_Visited], [emp_Name] = “John”)
Current display : NY , CA , IH
Remark : FOR CLARITY, SHOULD BE DISPLAYED AS : { { NY , CA , IH } }
Table : result
Virtual Column : v_john_visited_one_list
App formula : =ANY(SELECT(emp[emp_Visited], [emp_Name] = “John”))
Current display : NY , CA , IH
Remark : FOR CLARITY, SHOULD BE DISPLAYED AS : { NY , CA , IH }
Table : result
Virtual Column : v_has_john_visited_NY_bug
App formula: =IN(“NY”, SELECT(emp[emp_Visited], [emp_Name] = “John”))
Current display : N
Remark : Display OK, but the author did not realize that IN() cannot find a text element in a list of list.
Table : result
Virtual Column : v_has_john_visited_NY_Good
App formula : =IN(“NY”, ANY(SELECT(emp[emp_Visited], [emp_Name] = “John”)))
Current display : Y
Remark : OK, the author corrected the expression.