Filtering by a list of values for a dropdown

Hi there, 

I have an entity called Vacancies (that holds all vacancies at a company), a candidate entity (all candidates who may apply for vacancies), and a CandidateVacancies (all candidate to vacancy associations). 

All I want to do is have a drop down that shows all vacancies that the candidate has not yet applied for. So, essentially, I need to filter the Vacancies by the CandidateVacancies (where the CandidateVacancies id=the id of the current candidate). 

So essentially I want to filter the vacancies list by all the vacancies that have been applied for. 

I thought I might be able to do this by: 

1) A join between Vacancies and CandidateVacacies and then a filter: Just didnt get this right. Keep ending up with duplicate vacancies and I suspect I need to group by one of the elements. 


2) Performing a 'for each' loop on all CandidateVancacies (where id=current candidate id). For each of these CandidateVacancies (positions that have been applied for) I could filter Vacancies (filtering for all vacancies that do not match the current CandidateVacancy element). Those non-matching vacancies could then be appended to a list of records. Finally, after the for each loop has completed, all duplicate vacancies could be removed. 

Problem: The Append widget only seems to work with simple lists and not with compound type records. The drop down will need to show a vacancy name so I need to be able to store the vacancy names (and not just ids) for the drop down. 

Is there an easier way of filtering a record by a list? 

Kind regards, 


Hi Cole,

It's your lucky day, there is an easier way :).

What you need to do is start with the Vacancies Entity, and left join ("With or without" in the Aggregate) the CandidateVacancies entity to it. Next, you set a Filter that CandidateVanacies.Id = NullIdentifier().

Doing this, you only select Vacancies that do not have any Candidate associated with them (which is what you want).

Thanks Killian for your quick response. 

Indeed that returns vacancies that have not been applied for. However, I think it's still not working because it's returning vacancies that have not been applied for (in general) and not for a specific candidate only. 

That is to say, in this case, if any other candidate has applied for a vacancy, the current candidate will not be able to apply for the vacancy. 



Ok, so I read too quickly, appologies. In that case, you either change the filter to selecting for the specific candidate only, and you indeed need to group since you will be left with multiple instances if > 1 candidate has applied, OR you abondon the Aggregate and use a SQL statement like this (didn't test it, might contain errors :)):

SELECT {Vacancies}.*
FROM {Vacancies}
WHERE {Vanancies}.[Id] NOT IN (
    SELECT {Vacancies}.[Id]
    FROM {Vacancies}
    INNER JOIN {CandidateVacancies} ON {CandidateVacancies}.[VanciesId] = {Vacancies}.[Id]
        AND {CandidateVacancies}.[CandidateId] = @CandidateId


Personally I'd choose the latter.

I think you could simplify Kilian's SQL slightly:

SELECT {Vacancies}.*
FROM {Vacancies}
WHERE {Vanancies}.[Id] NOT IN (
    SELECT {CandidateVacancies}.[VanciesId]
    FROM {CandidateVacancies}
    WHERE {CandidateVacancies}.[CandidateId] = @CandidateId

You're right Jorge, I'm getting rusty :).