Dropdown displaying info based on info from the lookup colum from the sibling table

[Jan_Henrik]

I've three tables:
Table 1, called language

Table 2, called country-language

Table 3 called countries. 

Table 2 consists of two lookups: One to the language table, and the other to the country table. 

In my canvas app, using data from Dataverse,  I am using a dropdown controller displaying countries and another display language.  The user are first using the language dropdown. How can the country dropdown display only the countries who speak the language chosen in the language dropdown?

[Muzammil_069506]

Hi @Jan_HenrikHenrik if your country drop down has too dependent on your language drop down so you need write 

If(!IsBlank(LanguageDROPDOWN.Selected.value),show all countries,Filter(Countries,Language=LanguageDROPDOWN.Selected.value))

 Please let me know if you need more help on this  please accept this as solution

[Jan_Henrik]

Thank you, but this does not seem to solve the problem. The connection between language and country is through the table called country-language. 
When I type this formula: 

Filter(country-language,language.language=LanguageDropdown.Selected.language) I get the ID of the wanted rows, but I can not get the country name. 

Primary column in language table is language
and in country table is country

(I'm getting erromessage going from the countries table)

[Jan_Henrik]

I've come a bit further.
I've found out that this code:

LookUp(country-language, language.language = LanguageDropdown.Selected.language.language, countries)

returns the first match. But when using search or filter, it gives nothing but error. How can this code be tweaked to return all matches?

[Muzammil_069506]

Hi @Jan_Henrik  please tell more details on how tables are related?

[Jan_Henrik]

Hi, this is the setup:
Table 1 called language, with primary name column (Text-column) called language 

Table 2 called countries, with primary name column (Text-column) called country
Table 3 called country-language with primary column (Autonumber) called ID. This third table consist also of a lookup column to the language table, and another lookup column to the countries table. 

The only relation between the two tables, countries and language, is through the table country-language, as a country can speak many languages and a language can be in many countries , so to avoid the many-to-many relationship. 

[Muzammil_069506]

Filter('Country-Language', Language.Value = DropdownLanguage.Selected.Value).Country

Can you try this please let me know if it works out for you, please accept my answer as solution

[Jan_Henrik]

Thank you, but it gives me errormessage saying name is not recognized. 
When typing formula Filter('Country-Language', Language.Value = DropdownLanguage.Selected.Value) it gives ID of the wanted rows. 

[Muzammil_069506]

Hi @Jan_Henrik  is there any specific purpose why do want to three table instead can't you store in one table country name and language it will be easier it will serve as your referential table then you can simply filter as per your need i feel your data model needs to be corrected let me know otherwise

[Jan_Henrik]

I need three tables because of the many to many relationship, between the two main tables. So the third table is used to store the connection between them. 
Another example is if you swap the country and language with movie and actor tables. A movie has many actors, and an actor can play in many movies, so here you need as well a third table called movie-actor.    The question here as well is the same as in the first post.  When movie is selected in a dropdown, how can the second dropdown show only the actors that is played in that movie?
Hope this made more sense