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.