Dependent dropdown list from multiple conditions where a condition is a list

[Ekkapong14]

This is what I want to achieve.

Table 1 "Product Type" (office supplies, furniture, garden utilities, etc...)

Table 2 "Product Origin" (list of countries' names e.g. China, India, USA)

Table 3 "Product" (Product Name, Product Origin, Product Type) (e.g. "pen, china, office supplies", "bed, India, furniture)

Table 4 "Warehouse" (Warehouse ID, Warehouse name, Product Type, Product Origin) where product type and product origin are enumlist which ref to the tables above

Table 5 "Warehouse stock" (Warehouse ID, Product, Number of products)

Warehouse has a pre-specified conditions that its stocks have to match (Product Type and Product Origin).

Here is a question:

Say in Table 4 I created a warehouse that has a product type of "office supplies and furniture" and an origin of "China and India", in table 5 where I add the product to the stock of this particular warehouse How do I make the selectable products to be of  "office supplies OR furniture" AND "China OR India".

I hope my question makes sense. Thanks

[Lynn]

Hi @Ekkapong14 

Have you checked out these articles? 

https://support.google.com/appsheet/search?hl=en&q=dependent%20dropdowns&from_promoted_search=true

[Ekkapong14]

I have looked at the article. I have been able to successfully created dependent drop down list based on a single pre-selected condition. My question is how to create a dependent drop down list based a multiple conditions (“Either A OR B OR C” AND “either 1 OR 2 OR 3”)

[Steve]

The dependent dropdown feature only works for simple dependencies, and will not work in your case where there are several conditions. In your case, you'll have to construct the Valid If expressions yourself.

Try this:

FILTER(
  "Product",
  AND(
    IN([Product Origin], [_THISROW].[Warehouse ID].[Product Origin]),
    IN([Product Type], [_THISROW].[Warehouse ID].[Product Type])
  )
)