18
Views
9
Comments
Solved
Use a structure in an Aggregate and use it in a join

Is it possible for us to join a structure to an entity in an aggregate?


I have some entities that is Joined together and I want my structure to be joined in it as well.

Rank: #287
Solution

Hi Wilbye Descalsota

First, Your SQL seems wrong. How it's can be executed without error.

Second, You can't join structures, You have to loop the source and use the action such as listfilter to get joined data and add the result to a new column.

Kind Regards,

Hi Wilbye,

Good day!

As far as I remember, you cannot do such, but you can create instead new attribute within the aggregate.

Your option will be using a List variable using your structure and bind the result of the aggregate or use SQL Widget.

Kind regards,

Chris

My case is that there are Pre Joined entities (Was developed by other devs) and it displays details such as Employee Name and such, the purpose of the structure I made is for me to add a new column to the table and display there the count of Employees in the respective department.

Example Image Below:

So here, The details and in the "Resource In Pool" column is shown, I want to display how many of them are in the certain department.

What I did before the Aggregate is I gathered the details and store it in a structure. See image below:

Results in the image below:

I'll be using the category.id to join with the other tables in the aggregate.

Rank: #287
Solution

Hi Wilbye Descalsota

First, Your SQL seems wrong. How it's can be executed without error.

Second, You can't join structures, You have to loop the source and use the action such as listfilter to get joined data and add the result to a new column.

Kind Regards,

Hi Wilbye,

If I understood it correctly, I think you don't need a new structure, you just need to group by the appropriate attributes in the aggregate and at the right order too. Or include in the join the source entity of the Name and RRFNo..You must group it in the aggregate as the same sequence in the above select statement (category_id, category, employee name, rrfno, position, skill_set, level and business_tower). 

Then you need to add another attribute in the aggregate (for the count of category_id). You will notice then that the aggregate will return a structure like result based on your group by.

Lastly, another way is to create a function (server action) were in it will return a the count you wanted by putting inside an expression (your third column). But I think it will be heavy at the UI side.

Hope this helps,

Thank you and kind regards,

Chris

Greetings Tom Zhao,

I'm currently trying this solution and I'm a bit unsure if I understood your instructions correctly, is it supposed to be like this?

The assign:

Rank: #287

Hi Wilbye, 

Yes, The flow is right. Can you share the detail for the ListFilter node?

I suppose you have to use ListFilter.FilteredList.Current attribute in the assign node. 

Kind Regards,

Rank: #287

Hi Wilbye, 

AFAIK, It's not possible. You can't use Structure(Maybe you mean a Structure List?) in an Aggregate.

Kind Regards,

Rank: #486

Yeah, everyone is right that structure cannot be used to join with the aggregation. 

Just imagine that the data on server and data on client cannot be joined directly. But, we still have other option to implement that

  1. get the data from server first
  2. then filter in the data on client to get specified fields you want
  3. or use list filter, list any, etc then get count or sum of the list filtered

Hope this help

My issue has been resolved, Tom Zhao takes this one. Thank you guys.