Relating to the post https://www.outsystems.com/forums/discussion/40028/show-the-list-inside-the-nested-according/

I have this same situation, but only one level of detail. It is Projects >> Milestones

I have defined my structure as outlined in the previous post. 


The last column in the ProjectStructure is column whose datatype is ProjectMilestoneStruct List. Here is my local variable definition:

I am using OutSystems tables for my tables. The question I have is how to get the last column populated with a list.

I have tried aggregates, but filling the MilestoneList is unclear - it doesn't seem to map to the columns.

I have tried advanced queries, but again I can't seem to get the subquery to come back as a structure. 

I know this is a lot of information, but if someone could help point me in the right direction to get this structure returned, I'd appreciate it.

Stephanie

Hi Stephen,


One have some quick question, 

1) you are able to get all data in aggregate or advance sql query successfully

2) When you assign value from aggregate to your list variable then you get value on parent attribute and milestonelist is empty?

Solution

Hello Stephanie,

You just can't get information, from database, in this shape. No matter if you are using Aggregates or SQL queries. That is not how SQL (and an aggregate generates SQL) works.

So, the EASIEST way to do what you want, is to have a simple aggregate that fetches projects, passing this to your list in the screen.

If you are using the Accordion-List-Accordion Item pattern, in the Title of the Accordion Item you can put some information about the project. Than, in the Content, you can put more information about the project (more details), and you can add a WEB BLOCK that has as Input Parameter the ProjectID, and inside it it fetches the milestones for this project and show in a list.

This has a performance penalty, as you will have n+1 database accesses, where n will be the number of lines in your list.

You can reduce this impact making as the web block be constructed only when the accordion item is clicked (this will require some JavaScript / tricks).

Cheers.

Solution

Rajat Agrawal wrote:

Hi Stephen,


One have some quick question, 

1) you are able to get all data in aggregate or advance sql query successfully

2) When you assign value from aggregate to your list variable then you get value on parent attribute and milestonelist is empty?

No, I actually was just starting the SQL statements, so it was not yet working. I was just following the suggestions in the link I mentioned about the Accordion.

I understand what needs to be done with filling this data, will be working on it. Thank you for your reply.


Stephanie


Eduardo Jauch wrote:

Hello Stephanie,

You just can't get information, from database, in this shape. No matter if you are using Aggregates or SQL queries. That is not how SQL (and an aggregate generates SQL) works.

So, the EASIEST way to do what you want, is to have a simple aggregate that fetches projects, passing this to your list in the screen.

If you are using the Accordion-List-Accordion Item pattern, in the Title of the Accordion Item you can put some information about the project. Than, in the Content, you can put more information about the project (more details), and you can add a WEB BLOCK that has as Input Parameter the ProjectID, and inside it it fetches the milestones for this project and show in a list.

This has a performance penalty, as you will have n+1 database accesses, where n will be the number of lines in your list.

You can reduce this impact making as the web block be constructed only when the accordion item is clicked (this will require some JavaScript / tricks).

Cheers.


Thank you. I will post the solution when I get to it.

Stephanie