Master-Detail: display detail aggregations in a table with the master record

Master-Detail: display detail aggregations in a table with the master record

  

I want to display the sum of 'Detail' columns with the 'Master' record in a table. Using an OutSystems aggregate does not seem to let me do this. I tried joining the tables and creating a new column for the aggregation of the detail column but errors abound. What is the right way to accomplish this?

Let me use the following to illustrate.

Master entity has a Title column.

Detail entity has columns for Title and Amount.

I want to display the following data in a table that will list one or more Master records:

{Master}.[Title]     {Detail}.[SumAmount]

Thanks,

Hi Bill,

If I understood correctly, you can easily accomplish that using an advanced query with something like


Select  {Master}.[Title], Sum({Detail}.[Amount])

From  {Master} inner join  {Detail} on {Master}.[Id] = {Detail}.[MasterId]

Group by {Master}.[Title]


Regards,

André

Thank you, Andre.

The query you show is what I attempted with two differences. I built it in an aggregate instead of using SQL. I also did not group the query.

So, two follow-up questions.

Should I be able to build this in an aggregate or is this a circumstance where SQL is necessary?

Is the Group By clause an essential missing link to allow use of both existing columns and aggregation in the same results / display?

Thanks.


I have answered my own question through experimentation. I added the Group By in the aggregate and was able to resolve most errors. I see the aggregate and all the fields I need with one exception. And, I did not bring this up earlier because I thought it irrelevant. I tried to keep it simple.

I have a column of checkboxes in this table. So, I need the Boolean Rich Widget. It is in my Sources list but does not appear in the results.

How do I get a Boolean to appear in my grouped results?


Thanks.

Hi Bill, 

The boolean value is from Master table? And the problem is that you can't use that variable in the tablerecords to associate to the checkbox? 

In the aggregate are you grouping by that value as well?

Regards, 

The Boolean value is from the column of checkboxes. It is paired with a List_BulkSelect to allow selection of multiple rows for deletion. See the illustration (at runtime)


I presented "Master Detail" for simplicity. The real query is illustrated below. Cost is the master entity and MonthlyCost is the detail. Note the Boolean listed in the Sources list. This Boolean will not appear in the entity dropdown if I try to join with it. Don't think I need to include it in a join. That is just additional info.

With other TableRecords I add the checkbox column, the Boolean is added to Sources, and no join is needed. The Boolean.Value appears under the TableRecords in the expression editor for reference as needed. But, when I changed the aggregate to include Group By, Boolean no longer is available.

This illustration shows checkbox value assignment without a GroupBy:

This illustration shows checkbox value assignment with a GroupBy:

Boolean is not available.

My question is how can I include the Boolean so I can assign a value to the checkbox?

Thanks.

Hi Bill, 

Sorry for the late reply, don't know if you still need this.

You are right, the boolean structure doesn't appear in the results of the aggregate when you set a group by. Adding a new field to the result set of the type boolean won't work either because it will give you an error when trying to add it to the group by. I would definitely consider to go back to the SQL query. There you can add a boolean structure to the output and set the query something like 


Select  {Master}.[Title], 0, Sum({Detail}.[Amount])

From  {Master} inner join  {Detail} on {Master}.[Id] = {Detail}.[MasterId]

Group by {Master}.[Title]


Otherwise I guess you will have to do some workaround specifically for you results. Like creating a boolean flag in the Cost entity that you can use in the Group By (note that I don't think this is a correct approach) . 

Regards,

Solution

Thanks, Andre, for the reply. OutSystems Support was able to help. Here is the answer for all who are following this thread.

They confirmed that the usual way to include a Boolean does not work when a GroupBy clause is included in the aggregate. They confirmed that adding a Boolean attribute to any of the sources will also not work. What does work is to add a new Boolean attribute to the list of grouped attributes.

In this illustration you can see the dropdown menu with the 'Add new attribute' menu item and you can see my Boolean field as well.

Solution