I'm having trouble with an aggregate across 3 tables. I've recreated a sample OML to describe my situation. I've recreated my table structure in the sample as per that in my actual OML, just with dummy values & a lot less of them.

Essentially I have 3 tables, and I want to always return 3 rows per query (the three rows of tbl1), however when I filter the query by True, it returns only 2 rows (as there are no record assigned to the first row of tbl1). I need this lost row to be returned in the query but with a 0.

I think it's a left/right/inner join issue, but I'm not up on them in Outsystems.

Would appreciate any insight.


Your OML, as usual, comes with empty tables. Either way, the common way to do that is to LEFT JOIN the target Entity (so "With or Without" in the Aggregate). Did you try?

I would like share with you the follow OutSystems document, that I believe that will help you: Supported Join Types

Cheers,
Nuno

Solution

Correct me if I'm wrong. You're asking to show all records from table 1 and 2, but you only want to show the values of table 3 if the value of Active is true.

To begin you'd want table 3's join to be with or without (a left join) so that if there's no row in table 3 the full list of records from table 1 will show. 

The more important thing isthat your filter should be with the join condition instead of the filter condition. 


Solution

Caulibeam wrote:

Correct me if I'm wrong. You're asking to show all records from table 1 and 2, but you only want to show the values of table 3 if the value of Active is true.

To begin you'd want table 3's join to be with or without (a left join) so that if there's no row in table 3 the full list of records from table 1 will show. 

The more important thing isthat your filter should be with the join condition instead of the filter condition. 




Thank you - left join & the filter in the join condition sorted it.

Nuno Miguel Verdasca wrote:

Your OML, as usual, comes with empty tables. Either way, the common way to do that is to LEFT JOIN the target Entity (so "With or Without" in the Aggregate). Did you try?

I'm glad my answer helped you, Steve Webb and Caulibeam =)