How to get the app to chose a value from a table based on variables provided by the app user

AppSheet AppSheet Q&A
,
4

Got it.
Thank you for screenshots and explanations, it is clearer now.
You are using type Ref incorreclty, I suggest you have a look there:

For your “Sheet1” Table:
You will need to use a SELECT() expression.
As the SELECT() expression will give you a type List result, you will want to wrap it into an ANY() in order to get the type Number you are expecting.
Basically, you woud want to say:
“I want to get the list of items from column X, from table Y, that matches this condition. Then I want to pick a random item from this list.” (because from waht I see of your screenshot, you’ll get a single-item list).

In your case, that will be

ANY(
  SELECT(Rates[Rate],
    AND(
      [_THISROW].Country]=[Country],
      [_THISROW].[Seniority]=[Seniority],
      [_THISROW].[Occupation]=[Occupation]
    )
  )
)

Please note:
Pay attention to set explicit names to your sheets, as well as to your table names. In a perfect world, they would match so you won’t get confused.
I suggest:

  • 1st sheet ==> “People” (I saw “Untitled SpreadSheet” on the screenshots )
  • 2nd sheet ==> “Rate”

Let us know if it’s OK for now

For reference:

4 Likes