For my use case, I have an entity "Program" and an entity "ProgramForStudent". The entity "Program for student" has a foreign key for "Program". I have a Screen that currently lists all the Programs. But, I want only the programs that are NOT already selected by the student to be displayed (so none of the Programs in ProgramForStudent).
My first idea was to add a filter - I wanted to do something like !GetProgramsForStudent.List.Contains(Program.Id). But after researching the list functions I couldn't find one that I could use instead of Contains.
I also thought about joining the entities, but I can't think of a way to use the join in a way that only the Programs WITHOUT ProgramForStudent will be filtered.
Is there a way to do this that I'm missing.
Sorry if the solution is right under my nose. I tried researching this but I couldn't think of a way to implement it, but maybe I'm missing something that's obvious. Thank you in advance!
Hi Jana,
Outsystems provides multiple ways here i am mentioning my way to grab your result with aggregrate.
As per our requirement, we have two entities and we have to find out the lists of programs who are not selected by any students.
1. Add 'With or Without' Join.
2. Add a filter which you are using in a List_Filter action. It will give you the same result & reduce the work.
Please use this link as a reference.
https://success.outsystems.com/Documentation/11/Reference/OutSystems_Language/Data/Handling_Data/Queries/Supported_Join_Types
Hope this may help you.
Thanks
You can do this with multiple ways I will mention two of them:
- you can create aggregate and sources will be Program and ProgramForStudent with relation "with or without" then filter returned list using "ListFilter" action with condition "ProgramForStudent .Id = NullIdentifier()" so filtered list will contains only programs not taken by students
- Second option you can use advanced SQL using SQL or Oracle syntax based on your database engine
The answer from @Sameer Dhulia below should be the best practice way, due to:
- the performance of doing the filtering directly in the database, as is not the case of iterating and filtering with the FilterList- the simplicity of using an Aggregate vs Advanced SQL
If you have any questions I will be happy to answer
Where do you place the client action shown in the first picture? Is it "onInitialize" for my screen?
Don't use "OnInitialize" to fetch screen data but you can use either "Fetch Data from Database" or "Fetch Data From Other Sources"
- For using "Fetch Data from Database" you will need to make its fetch on start and add on after fetch action to filter data returned from database to get only programs not taken by any student
- If you will use "Fetch Data from Other Sources" make sure its fetch property "At Start". "Fetch Data from Other Sources" should have at least one output which will be list of programs that you can use in
Please don't do list filters for something that can and should be done on the database. That filter condition can be applied directly on the Aggregate, which will give you way more performance.