Solved: How to get the app to chose a value from a table b... - Page 2

Pejme · 01-13-2022 10:29 PM

I am trying to create a small payroll app. I want my app to calculate my employees’ hourly rate based on their country, occupation and seniority, but I can for the love of me not get it to work properly.

I have recorded a quick 5 min video for further details.

I am struggling to get my app to automatically choose the correct hourly rate value from a table based on the information that the app user provides. Currently, the app shows the correct value as the only option to choose for the app user after they have provided the relevant information (Country, Employment start date and Occupation), but I would like the app to choose is automatically.

I assume I need to have a formula (as I do for the seniority column), but I don’t know what formula to use. I have tries LOOKUP([Occupation], “Rates”, “Occupation”, “Rate”), but it doesn’t work.

Bonus question: What is the benefit of virtual columns? I know they are to be used when you want to use formulas that doesn’t need to be stored, but what is the benefit of using virtual columns instead of normal columns? Are they faster because the calculation doesn’t get stored as an example?

I’m sure the solution is easy and obvious for more experienced developers, but I just can get my head around it so any guidance would be greatly appreciated. I have done my best to find the solution myself by reading articles and watch Youtube video before asking for help, but I just don’t get it.

Thanks in advance.

Aurelien

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:

View solution in original post

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