Problem in Expression

[aditya_glp]

Hi, I have 2 sheets (Orders and Products)

Orders sheet contains form response of a user when he places an order.
Products sheet contains price of multiple products across 2 countries.

Now, product name is a dropdown in Orders form. I want that ‘product name’ dropdown should be populated on the basis of the country selected and should exclude all those products for whom there is no price mentioned in the Products sheet.

How do I write an expression for this?

[aditya_glp]

@Aleksi Can you please guide me on this?

[Aleksi]

SELECT(Products[ProductName],AND([Country]=[_THISROW].[Country],ISNOTBLANK([Price])))

[aditya_glp]

@Aleksi

order sheet--

products sheet –

As you can see, the prices of some products are blank in some countries. So when the user fills the form and enters country = Kenya. I want to exclude all the products for which price in Kenya is blank. Can you help me writing the appropriate expression?

I was trying this but not getting desired results :

IFS(AND([Country] = “Kenya”, ISNOTBLANK(Products[Kenya])),Products[Unit],AND([Country] = “Uganda”, ISNOTBLANK(Products[Uganda])),Products[Unit],AND([Country] = “Tanzania”, ISNOTBLANK(Products[Tanzania])),Products[Unit],AND([Country] = “Nigeria”, ISNOTBLANK(Products[Nigeria])),Products[Unit],AND([Country] = “Myanmar”, ISNOTBLANK(Products[Myanmar])),Products[Unit],AND([Country] = “India”, ISNOTBLANK(Products[India])),Products[Unit])

[aditya_glp]

@Aleksi

Also I’m writing the above expression under ‘Valid If’ in product type column

[Steve]

@Aleksi’s expression was correct but didn’t take into account information you hadn’t shared.

SELECT(
  Products[Units],
  OR(
    AND(
      “Kenya” = [_THISROW].[Country],
      ISNOTBLANK([Kenya])
    ),
    AND(
      “Uganda” = [_THISROW].[Country],
      ISNOTBLANK([Uganda])
    ),
    AND(
      “Tanzania” = [_THISROW].[Country],
      ISNOTBLANK([Tanzania])
    ),
    AND(
      “Nigeria” = [_THISROW].[Country],
      ISNOTBLANK([Nigeria])
    ),
    AND(
      “Myanmar” = [_THISROW].[Country],
      ISNOTBLANK([Myanmar])
    ),
    AND(
      “India” = [_THISROW].[Country],
      ISNOTBLANK([India])
    )
  )
)

You should probably also familiarize yourself with the Valid If property:

help.appsheet.com

Valid_If Column Constraint

How to check if a given form input is valid based on its type.

help.appsheet.com

Dropdown from Valid_If

How to create a dropdown that depends on a particular list of values.

[aditya_glp]

Steve:

SELECT(
  Products[Units],
  OR(
    AND(
      “Kenya” = [_THISROW].[Country],
      ISNOTBLANK([Kenya])
    ),
    AND(
      “Uganda” = [_THISROW].[Country],
      ISNOTBLANK([Uganda])
    ),
    AND(
      “Tanzania” = [_THISROW].[Country],
      ISNOTBLANK([Tanzania])
    ),
    AND(
      “Nigeria” = [_THISROW].[Country],
      ISNOTBLANK([Nigeria])
    ),
    AND(
      “Myanmar” = [_THISROW].[Country],
      ISNOTBLANK([Myanmar])
    ),
    AND(
      “India” = [_THISROW].[Country],
      ISNOTBLANK([India])
    )
  )
)

Thanks much @Steve

[aditya_glp]

Hey @Steve @Aleksi and dear friends, Can someone please help me to access the price of a product in a country as per above table. For example, what expression do I write if I wish to access the price of Unit ‘Pico 100’ in Nigeria (which is 3525)?

Please refer to ‘products sheet’ shared above.

[aditya_glp]

Hey @Steve @Aleksi and dear friends, Can someone please help me to access the price of a product in a country as per above table. For example, what expression do I write if I wish to access the price of Unit ‘Pico 100’ in Nigeria (which is 3525)?

Please refer to ‘products sheet’ shared above.