Two Tier Lookup Value

AppSheet AppSheet Q&A
1

Hi There, I have a list of products with categories and prices.

Ex: Product A, Have Type 1 ($5) and Type 2 ($7)

I am trying to make the invoice and have been stuck on the lookup price value, what I wanna do is so easy in excel because I can make a helper column like this one https://trumpexcel.com/vlookup-with-multiple-criteria/. Is there a way to do it in Appsheet?

In my Invoice form, the user needs to choose the product, and the type then the price will automatically fill in with the corresponding price.

2

You may wish to take a look at the dependent dropdowns concept in AppSheet

Help article

https://help.appsheet.com/en/articles/961554-dependent-dropdown

Sample app

https://www.appsheet.com/templates/How-to-create-a-dropdown-menu-with-values-that-depend-on-earlier-choices?appGuidString=f9584a6f-935d-48e6-85ec-050b85b93aea

2 Likes
3

Hi Romanos,

Let’s say you have the following tables:

  1. Products table, which contains information about the product but not it’s price

  2. Types table, here you list the types available for each product along with the corresponding price.

  3. Invoices table, where the users are filling the invoice form. This table should have the following columns:

    • “productID” column, Ref to “Products” table: where the users will have a list of products to select from.

    • “typeID” column, Ref to “Types” table: here the users should see a list of types related only to the selected product. For this:

      • Suggested Values, and Valid IF fields would have this expression:
        SELECT( Types[typeID], [productID] = [_THISROW].[productID] )

      • Additionally, you can even display this formula field only when the product is selected, using this expression as a condition in the “Show?” field: ISNOTBLANK([productID])

    • “Price”, that is shown to the user when the Type is selected:

      • App Formula: [typeID].[Price]
      • Show condition: ISNOTBLANK([typeID])
2 Likes
4

Thank you for your answer, I have not tried it yet. But it makes a lot of sense.

2 Likes
5

Hola @Joseph_Seddik disculpa y como haria si quiero encontrar un resultado con dos criterios de busqueda?

hice una pregunta aqui, https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Search-result-with-two-criteria/m-p/427082#M169302

quiza podria hecharme una mano

muchas gracias.