Filter on distinct dropdown with related tables

(0) Share

Report

Posted on by jeppegroenj1

Hi everyone.

I am very new to this and I have read through a number of posts without any luck.

I have 2 tables:

Table 1: Movies

Table 2: MovieGenres

Field 1 (Primary key): ID

Field 2: Title

Field 1 (Primary key): ID
Field 2: Genre

The tables are related (many MovieGenres to one Movie) and the ID fields are the same.

I am trying to do this:

Dropdown 1: A dropdown with distinct genres
Dropdown 2: A dropdown showing me the Title of all Movies related to the genre selected in Dropdown 1

I have tried a number of things, but I always get stuck, although this sounds like an easy task.

Do you have any suggestions to how I do this?

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

WiZey 3,023 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @jeppegroenj1 ,

Could you show us what you tried so far? There are a few ways to accomplish what you're looking for and actually a lot of topics on this same issue, so I'm kind of curious what specific issue you're facing.
jeppegroenj1 13 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @WiZey,
This is my current not optimal solution:
Dropdown1: Distinct(MovieGenres, Genre)
Dropdown2: Filter(MovieGenres, Genre = Dropdown1.Selected.Result)
Dropdown3: Filter(Movies, ID = Dropdown2.Selected.MovieID)
I have 2 issues. Firstly, I preferably want to make a solution without the Dropdown2 list (I use this dropdown only to get the ID's of all MovieGenres with the Genre chosen in Dropdown1).
Secondly, the Dropdown3 only gives me one result (the selected ID in Dropdown2. But the Dropdown2 list can have multiple values and I only get 1).
Do you know what I can do further, or guide me to other posts that I haven't seen or understood?
Thank you very much for your time.
WiZey 3,023 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hello @jeppegroenj1 ,

First, I believe it is absolutely possible with only two dropdown lists. You'll have one dropdown to display and select multiple genres, and a second dropdown to display movies related to selected genres so anymore components than this would become redundant in your app.

Second, if you wish to actually select multiple genres and base your filter on those selections, then you must modify your "Filter()". You are only getting one result here because you're comparing "Movie.ID" to a single "Genre.ID". To check if your movie belongs to multiple selected genres, you should use the term "in".

Can you try the code below and see how it goes please?

Dropdown1:
Items=Distinct(MovieGenre, Genre) //If "Genre" is a "choice" type of field, use "Choices()" instead: Items=Choices(MovieGenre, Genre)

Dropdown2:
Items=(Movie, ID in Dropdown1.SelectedItems.ID)
jeppegroenj1 13 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @WiZey
Thanks for your reply,
I tried your suggestion:
Dropdown 1: Items=Distinct(MovieGenres, Genre)
Dropdown 2: Items=(Movie, ID in Dropdown1.SelectedItems.ID)
However, I get an error in the Dropdown 2 (marked red): "Name isn't valid. The identifier isn't recognized."
I think the reason is that Dropdown 1 only contains 1 column (because it is a distinct column). It only contains a "Result" column with the name of the genre. So the filter in Dropdown 2 cannot find the ID in Dropdown 1. Please see screenshot of Dropdown 1 below:

Just to give you an idea on the data and relations:
Table: Movies
ID (PK) Title
1 The Dark Knight
2 Pulp Fiction

Table: MovieGenres
ID (FK to Movies) Genre
1 Action
1 Drama
1 Crime
2 Drama

According to my logic, I need to do something like this:
Relate the distinct Dropdown 1 (only containing Genre names) to the MovieGenres table (to get the ID's)
Relate the Dropdown 2 (Movies) to the MovieGenres table related to the distinct Dropdown 1
Or maybe I am confused and missing something?
Verified answer

WiZey 3,023 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hello @jeppegroenj1 ,

My apologies, I've rushed the filter on the second dropdown.

It should be something like this :

Items=Filter(Movie, ID in Filter(MovieGenres, Title in Dropdown1.SelectedItems.Result).ID)
jeppegroenj1 13 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WiZey,
After recreating the relation between the 2 tables, your function worked!
I was failing to understand how to make a filter within a filter. Now I know.
Thank you very much for taking your time helping me out!