Dependent Dropdown from different tables

[sheebee22]

Hi all, I know this has been asked before but having looked at the other answers (like this one) I can't actually find an accepted solution that works for my situation. It's (hopefully!) not too complex for you experts out there!

Basically, when somebody fills in a form about a visit to a school, they first select the school, and I then want the "teacher" box to populate with only the teachers from the selected school. However, the normal dependent dropdown doesn't work because the data for teacher is from a different table to "school". 

When I emailed Appsheet support, they suggested the following formula:

ORDERBY(SELECT(Teachers[key],[_THISROW].[School]=[School]),[Teacher],TRUE)

However, when I put this into the VALIDIF box, and also when I try putting it into the suggested values box, it doesn't give me any options for teachers at all after I've selected a school! See below:

So it seems like either I'm putting the formula in the wrong place (should it go somewhere that's not VALIDIF or Suggested values....?) OR the formula is slightly wrong and needs to be changed in order to work.

If somebody is able to help me out with this I would be so grateful! Thanks! 

[AleksiAlkio]

With the details you gave, the formula is correct in theory, if you are using a Ref column type with your "Teacher" column. Though you need to use your own key column name in your formula. Now it's using [Key]. When using the Ref column, it shows the value in the dropdown from the label column. Please check that you have values in that Label column.

[sheebee22]

Thank you. Yes, I have ticked "label" for the teacher name in the teachers table. But it still just gives me blanks when I select a school and then try to select a teacher.....

[AleksiAlkio]

Is the column type Ref? What is your key column's name "School" table? 

[sheebee22]

In the "School" table, the key column's name is "key". However, the teachers names data needs to come (I think) from the "Teachers" table, and the key column there is also called "key". 

[AleksiAlkio · Accepted solution]

The reason was the wrong formula with the School column. When using a formula with the Ref field, the Valid If needs to read the key column. Otherwise it shows a broken reference and it doesn't save the key value. Then the Valid If with the Teacher column doesn't find anything either.