Is a join between two lists possible


I am trying to do something I would imagine is quite simple. I want to create a join between 2 different record lists. 

To give context. 

There is a record list of the shift, and a shift is made up of many patrols. I want to calculate statistics about the patrol so that in a "list records" widget. 

The problem I am having is that I don't see how to create a join between two lists or one is queried from a DB (using an aggregate) and one is calculated within a loop that outputs a list of records. They have a common identifier, so they can be linked. 

Hi Johann,

You can only perform joins as the database level... if you have two record lists, you will have to iterate them, filter and sort them using OutSystems logic (For Each, If, Assign, ListFilter, ListSort, etc...).

I'd expect both the Shifts and Patrols would be represented by entities (and as such, on the database and possible to Join), what have you tried and what exactly are you calculating on the loop you mention?

Hello Johann

Jorge have already give a good advice how to solve your problem, but if you turn available the lists structure. We can give more help


Hi Jorge, 

Thanks for answering - the part I really wanted to know is if you can only do joins on DB queries. I understand the iterate scenario, however, if that has to occur while the page is loading, I worry that might cause a delay. 

Initially, I just want to calculate something simple. The percentage of a certain route that is predefined - that get done one time. The calculation is quite simple. (routes completed on time/routes completed) per patrol. Where each route instance belonging to a patrol- has a t/f in a column for on time. 

I thought this might be solvable by coun and group by. however, I land up with the condition below ... i.e. I want to count twice  but it doesnt seem to allow me to apply a conditional filter to one of them

A better way might just avoid all this query logic and place the amount done on time as a column in that patrol table which gets updated each time the API gets a new update. 

Any alternative suggestions?

Alberto Ferreira wrote:

Hello Johann

Jorge have already give a good advice how to solve your problem, but if you turn available the lists structure. We can give more help


Sure could you elaborate? 

Hi Johann,

Consider creating an extra calculated attribute, with its Formula set to  If(RouteInstance.IsOnTime, 1, 0). Then you can apply a COUNT() aggregate function to it in order to return the number of routes and also apply a SUM() aggregate function to the same extra calculated attribute to return the routes on time. Finally, you can add a new calculated attribute based on the previous two to return the percentage (SUM/COUNT).