Show values based on Select formula = Formatting EnumList view

[Pejme]

I have a few questions that I would appreciate help with.

These are my tables:

QUESTION 1
In the order table, I want the user to be able to chose what type of order is going to be made, and I want the dropdown to only show the products that are associated to the chosen type(s). I can get this to work if only on type is chosen.

But I can't get it to work when there are multiple types chosen. One issue that I get is because my current settings/formulas have problems  to Cannot compare Text with List in ([_THISROW].[Type] = [Type]).

The second problem is that even if I change everything to EnumList, then I will still not get all the correct products show. The Products field will not even show.

How can I be able to choose multiple types and have the product dropdown show the associated products to those types?

QUESTION 2
Is it possible to format the dropdown list to create paragraphs/new lines so each product is shown in a new row as per the cells in Google sheet?

[Joseph_Seddik]

Question 1: Use IN().

Question 2: Use SUBSTITUTE().

[Joseph_Seddik]

First, you will be much better off having a better data modelling.

1. You should have a typesTable listing the different types, with a UNIQUEID() key column and a "typeName" column marked as Label.
2. Your Products table should have a "Type" column set as Ref pointing towards the typesTable.
3. You do NOT need a Price table. You should instead have "Price" column in your Products table. 
4. You should have an Orders table. This table would contain columns about fixed order information, like Date, Customer, Server, etc. in addition to the "orderTotalValue". This table should NOT contain columns about the Products or Types.
5. You should create an orderDetails table. This will have the following columns:
   • "Type", Ref column pointing towards the typesTable.
   • "Product" Ref to Products table with option Is a part of? set. Consequently, AppSheet will create a Reverse-Reference virtual column in the Orders table named "Related orderDetails".
   • "Price" of type Prix, with the following Dereference Expression in its Initial value:
     [Product].[Price]
   • "Quantity", a Number
   • and "subTotal" with the following App formula: [Price] * [Quantity] 
     
     
6. In the Orders table, set the App formula of "orderTotalValue" using the following List Dereference expression:
   SUM([Related orderDetails][subTotal])
   

Question 1:

Use the following expression in the Valid if field of the "Product" column in the orderDetails table:

SELECT(Products[Product], [Type] = [_ThisRow].[Type])

Question 2:

You'll already have a clickable list view of products with each product in its own line. 

 :

[Joseph_Seddik · Accepted solution]

Question 1:

Use IN().

SELECT(Products[Product], IN([Type], [_ThisRow].[Type]))

Question 2:

Use SUBSTITUTE(). 

SUBSTITUTE([EnumList or List column], " , ", "
// this is a manual, explicit, empty newline
") 

[Pejme]

@Joseph_Seddik Thanks heaps!

[Joseph_Seddik]

@Pejme Welcome jumbles 🙂

[Pejme]

@Joseph_Seddik I'll tidy it up. Promise!

[Joseph_Seddik]

😁that's not what I meant but this comment is hilarious 😄😄

[Pejme]

@Joseph_Seddik Now I'm curious. What did you mean? 😊

[Joseph_Seddik]

You said thanks heaps, so I wanted to respond saying you are so much welcome 🙂

[Pejme]

---

@Joseph_Seddik wrote:

Question 2:

Use SUBSTITUTE(). 

SUBSTITUTE([EnumList or List column], " , ", "
// this is a manual, explicit, empty newline
") 

---

@Joseph_Seddik I can get the expression to fork as a formula. But when I try to use the expression in Valid If so I can get a list, I get an error message

I tried to add the SUBSTITUTE() expression to a virtual column and then reference the virtual column in Valid If in the other column. Didn't work. 

Any suggestions?

[Steve]

This is how the expression might look in Expression Assistant:

The critical parts are the quote at the end of the SUBSTITUTE() line, and that the quote that follows it occurs as the very first character of the line it's on.

[Pejme]

@Steve Thanks for your reply. Not sure if I am misunderstanding something, but I am getting the expression to work, in the formula section. @Joseph_Seddik did a good job explaining 🙂

However, when I try to use it in the Valid IF section instead, it doesn't work.

What am I doing wrong? Or can SUBSTITUTE() not be used in Valid If? And if it can't, is there any other way to get a new line?

[Joseph_Seddik]

You are right! The column constraint, as the expression assistant is telling you, expects either a Yes/No value or a List, while SUBSTITUTE() returns a Text; hence it cannot be used. 

The trick we did with SUBSTITUTE() on the list was just for the display. For all other purposes, you should use the list as is. 

[Pejme]

@Joseph_Seddik Thanks for confirming that there isn't a way to create new lines in Valid If. Doesn't work for what I need right now, but I did learn how SUBSTITUTE() works, so still a win (for the future) 🙂 

[Joseph_Seddik]

@Pejme Hey my friend don't loose focus..

The valid if is a constraint, meaning it is used to check whether a column value is valid based on a defined rule stated in an expression, or to restrict possible values to a predefined list of values. It has nothing to do with the visual representation of the data in this column, and cannot be used for this purpose with or without SUBSTITUTE(). 

We used SUBSTITUTE() to change how the data is displayed in an app view. 

What exactly is not working for what you need? What is the problem?

[Joseph_Seddik]

Well, that's a completely new and different thing.  So far, we have been formatting how would a list of elements be displayed.

Now you want to format the element itself, conserving line breaks, etc. Such formatting would be preserved for LongText-type columns in Detail views, and can even be extended with HTML or Markdown. However, in a form dropdown, and in all views other than Detail, you'll have the column shown as one continuous line.  

Perhaps you could come up with a better way to inform your users about the categories, instead of showing them this amount of information every single time and force them to make big scrolls to reach the desired option. I don't think this a good practice for a user interface any way.

Personally, I would do differently. In the Form view, if really needed, I would replace the dropdown by buttons, and have another non-editable-here LongText column just under the button dynamically updated showing the long description of the button being selected. 

Also, as users get used to the app, they would certainly not need to be shown this long description every time, so after the buttons I would add another "Show description?" column with Show/Hide buttons, defaulted to "Hide", so users can show this long description only when they really need to, then be able to hide it and go on with the rest of the form.