Get price of selected item based on item criteria

[Cameraville2]

I have tried for 2 days...
I'm looking for how to allow a user to fill out an offer request to sell a product, the offer request would show them the price I'm willing to pay based on a price list I have setup. 

So I have some data with products, where I assign multiple conditions to the product, and then a price. 

A user should select the product they want to sell from my list of products, then select the condition of their item from my conditions, then I want to display a price to them that matches that product/condition combination. 

I'm experienced with Appsheet, so don't worry about my current setup, I can remake the app in whatever way is correct. I just can't figure it out.  Please help!!

[Aurelien · Accepted solution]

Hi @Cameraville2 

Let's say you have:

Table "Items"

Key_Column, type Text, initial value expression UNIQUEID()

Description, type Text

Table "PriceList"

Key_Column, type Text, initial value expression UNIQUEID()

Item, type Ref, Source table "Items"

Criteria1, type Enum (for example)

Criteria2, type Enum (for example)

Price, type Price

Then, your table "Offer_LineItem"

Key_Column, type Text, initial Value expression UNIQUEID()

Item, type Ref, source table "Items"

Criteria1, type Enum

Criteria2, Type Enum

Then, you can add a Virtual Column:

Price, type Price, expression:

ANY(
  SELECT(PriceList[Price],
    AND(
      [Item]=[_THISROW].[Item],
      [Criteria1]=[_THISROW].[Criteria1],
      [Criteria2]=[_THISROW].[Criteria2]
    )
  )
)

For reference:

https://help.appsheet.com/en/articles/2347632-any

https://help.appsheet.com/en/articles/2357314-select

[Cameraville2]

I remade the app according to your specs and it works as I wish now, thank you so much for your detailed help. 

[Aurelien]

You are very welcome 🙂