Dropdown agreggate if id not in another source
Application Type


I want to only show items in a dropdown if those items are not present in another entity.
For example:

My Screen is a detail screen for a movie. Assuming that a movie may have multiple genres I created a third Entity for a M-M relationship between Movies and MovieGenres.

I have a dropdown to assign the Genres to the Movie in it's detail screen and when I add a genre to the movie I want that genre to not appear in the dropdown because it is already associated to the movie.

Can someone help me to achieve that? Do I need to use advanced SQL queries with a subquery or am I overcomplicating it?

Thank you!


I believe the most direct and effective approach to select genres that are not associated with a specific movie is to use a SQL query within a Data Action that will return this genre list. The SQL query would be similar to this : 

SELECT {Genres}.*

FROM {Genres}

WHERE {Genres}.[Id] NOT IN (

    SELECT {MovieGenres}.[GenderId]

    FROM {MovieGenres}

    WHERE {MovieGenres}.[MovieId] = @MovieId


Thank you Adão, it worked perfectly! I was trying that option but there was something in my clause that was not written correctly and it was just giving me an error!

Kind regards,

Pedro Luís

Great 😄, I'm glad I was able to help. keep up the great work.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.