LinkedIn
Twitter
1 ACCEPTED SOLUTION
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:
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 
Here is an article that provides guidance:
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
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!
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:
Excellent news @Aurelien! I can confirm that your magic works. Thanks heaps. Will read through all the articles you have shared as well, and work to understand the logic behind the expression you suggested.
Also, thanks for the tip re naming the sheets and columns correctly. Will do! To be honest, the sheet that I shared is not the โrealโ sheet. Thatโs why I didnโt spend much time naming the sheet/columns. I just created this one for the purposes of my question to make it easier for the reader to follow as the โrealโ sheet has 245 columns (and counting).
Again, very, very appreciated! If you only knew how long I tried to solve this myself before asking for help
