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

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.

Hi @Pejme

Welcome to the community !

You may want to use dereference expression.
If you have an Occupation table, and use a occupation information (such as Rate) in another table, then your expression would be:
[Occupation].[Rate]

For reference:

Always true because calculated on the fly, vs real columns calculated on edition mode only.

Also:
I did not look at the video as I avoid reading these, I prefer screenshots :slight_smile:
Here is an article that provides guidance:

2 Likes

Thanks for your feedback @Aurelien . Much appreciated!

Unfortunately, I can’t get [Occupation].[Rate] to work. I get the following error messages.

I thought a video would be easier for the reader as I am still a newbie and don’t know all the terminologies (and English is my second langue) but I’ll try to explain it with screenshots if that’s preferred :slight_smile:

I currently have two tables.

I am currently using Data Validity Valid if to get the data from table Rates

This has been working well to create a dropdown list of options based on table Rates and the data that the user chooses.

I’m not facing any problems with Country and Occupation as I want the user to actively tick one of the available options. I am also not having any issues with Seniority as the formula I am using IFS([Days employed]>60,3,[Days employed]>31,2,[Days employed]>0,1,) is working well.

However, I am having problems getting the app to automatically choose Rate. Currently, the correct rate shows as the only option, but the user needs to actively tick it. I need the app to choose it.

I have read the link you provided. I have watched the video in the article. Maybe the answer is there, but I just can’t get it to work. So any further assistance would be very appreciated!

1 Like

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 :wink:)
  • 2nd sheet ==> “Rate”

Let us know if it’s OK for now

For reference:

2 Likes