Filtering aggregates in OutSystems when the 'With or Without' option is used
Sometimes you'll have a join in your aggregate that uses the 'With or Without' option. For those familiar with SQL this is an outer join and can result in some fields in your aggregate being null when the related data doesn't exist.
Recently I was dealing with an aggregate where I had added my sources and I ended up with the default joins that OutSystems creates. One of them was a With or Without join and the condition was comparing the primary key of the first table to the foreign key of the second table. Perfect. Just what I wanted.
But I also wanted to filter the second table on a different column to compare against an input variable for my screen. I went to the filters tab and added my filter and it appeared to work, but once I started adding data into the system things stopped working. After some troubleshooting, I had discovered the problem: my filter was causing the data I was expecting to be filtered out incorrectly.
This is because the column I was filtering on sometimes existed and sometimes was null, because of the With or Without join. The solution was to remove my filter from the Filters section and add it to the join condition instead. So now my join condition was comparing the keys like before AND comparing my filter column to my input variable.
TL/DR: When filtering on a table that is on the right side of a With or Without join, use the join condition to set your filter instead of the Filters section.