Reducing list

Hi guys. I have a table with 20+ columns (Product - 1, Product -2, Product -3…Product 20)

All these columns get populated by a dropdown. My problem is that everytime I select a product, the dropdwon list should exclude the already selected products.

For Example, if I choose Product - 3, the dropdown should include all products except Product - 1 and Product - 2.

Kindly help!

My initial impression is you better change table structure and schema, rather than setting multiple column for all the different product.
Sounds like your original table structure seems to be type of Excel table.

Thanks @tsuji_koichi for the advice. However, my use case is to create a form where the user needs to select multiple products with each of them having different quantities. Hence, I don’t think I can change the table structure / schema.

Considering the above use case, can you help me to find an alternative appproach?

I need to fully review the contents of your questionnaire and data column, then might get some nice idea. In other words, information here is bit far too less to deliver some advice.

Roughly to say i thought this, if you are not able to change the table structure (now understand you cant change)

  1. Generate full list of the product as LIST, for instance, to generate the virtual column using select expression OR simply hard code like LIST(Product - 1, Product -2, Product -3…Product 20) - Let s name this vc as [FullList]

  2. Then for each fields set the type to Enum, and add this expression into the valid if constrain.

for the first column, just [FullList] this returns entire list out of virtual column
for the second column add valid if constrain like [FullList] - [First Column] this should return the list expect for the item being selected in the first column.

for the third column, then [FullList] - [First Column] - [Second Column]

Then for the reminder of the column, keep adding, actually keep add minus the previous column which would reduce the number of the list dynamically.

Again, i m sorry not doing hands on test, but technically it should work.

1 Like

ok thanks!

Test it out and let me know if you require further assistance.

@tsuji_koichi It didn’t work. Error looks like that we can’t use arithmetic expression ‘-’ in this context

See the official documents from Appsheet.

List Subtraction

LIST- returns the items in the first list after removing the items in the second list.

  • Customers[PhoneNumber] - {"1800-444-4444", "1800-555-5555"} : returns a list of customer phone number with the second list of numbers excluded.

What is going to happen if we change the syntax to something like

[FullList] - {[First Column]}

[FullList] - {[First Column], [Second Column],…,[Last Column]}

Thanks @tsuji_koichi it has worked!

Awesome! I actually learned new things ! Thank you.