Skip to Content (Press Enter)
UserImage.jpg
Francisco007
216Views
12Comments
Solved
table records - show count and other atributes
Question
Training

Hi community,

I'm training some random exercises.

I have this aggregate to use on a table record in order to show the name, description etc


But I want to count the number or orders each customer made. (The sources of the Agg are customer and order).

The problem is: if I perform a count on customer name I get the result but I can't then use the other attributes on the table record anymore.

Any suggestions to solve this issue?

Thanks,

Francisco

0
0
20 Dec 2019
Copy Link
2018-05-30 16-28-55
Gopinath Reddy Reddigari
Solution

Gopi Nath wrote:

Hi Francisco,


Please find the screenshot of the gird where I'm seeing the data. Also attaching sample oml. Not sure if you can publish because of the IPP , but adding it as a reference. 

Hi Francisco,

Can u try count by Order Id hiding all the attributes in Order table and then doing a group by on all attributes in customer  column this way u will have access to columns that you do group by in table.  





See solution in context
DemoApp.oml
2
0
20 Dec 2019
Copy Link
2025-11-19 06-14-01
Miguel Verdasca
Champion

Hi Francisco,
If I understand what you want... Aggregates don't support the kind of requirement you have. As far as I know, there is no standard SQL that will allow you to do it either.

There may be solutions specific to your database engine, which you'll likely be able to find in an online search or in places like Stack Overflow. Be advised that your application will be bound to that specific database engine.

Alternatively you can split that query in two parts:

  • One that gets all the common part (the different orders), and use it on a TableRecords, where the second column would have on it's cell a Web Block that receives the Order as input parameter.
  • As part of the Preparation of the Web Block you would then do the second part of the query (fetch durations of calls for for the input Order) and use something like a ListRecords to display them, comma separated (you have example instructions on one of the exercises of the Developing OutSystems Web Applications online course).

This solution will be database engine agnostic, although a bit more database-intensive, as you will perform several queries to get your desired result.

Other way that you can look to your problem:

You can perform a single query, sorted by date, and post-process the result in a ForEach, building a new list of Structures with as Attributes the Order and a List of values (or another Structure, if you have more values). This is also a typical method to construct a tree-like data structure for e.g. sending to a REST service (or serving a REST call).

What you'd basically do is keeping track of the current date in the list, and if it's the same as the previous, ListAppend the data to the List Attribute of the structure. When you encounter a new Order, or when you have processed the last item, you ListAppend the top-level Structure to the list used in the Table Records.

Note that for displaying the items, you either need a Web Block (in case you want to display them as a Table Records of their own), or instead of ListAppending to the List Attribute (as I described above), append them to a Text Attribute instead.

I hope this help you, if I understood your question Francisco.

Cheers,
Nuno Verdasca

1
0
20 Dec 2019
Copy Link
UserImage.jpg
Francisco007

Nuno Miguel Verdasca wrote:

Hi Francisco,
If I understand what you want... Aggregates don't support the kind of requirement you have. As far as I know, there is no standard SQL that will allow you to do it either.

There may be solutions specific to your database engine, which you'll likely be able to find in an online search or in places like Stack Overflow. Be advised that your application will be bound to that specific database engine.

Alternatively you can split that query in two parts:

  • One that gets all the common part (the different orders), and use it on a TableRecords, where the second column would have on it's cell a Web Block that receives the Order as input parameter.
  • As part of the Preparation of the Web Block you would then do the second part of the query (fetch durations of calls for for the input Order) and use something like a ListRecords to display them, comma separated (you have example instructions on one of the exercises of the Developing OutSystems Web Applications online course).

This solution will be database engine agnostic, although a bit more database-intensive, as you will perform several queries to get your desired result.

Other way that you can look to your problem:

You can perform a single query, sorted by date, and post-process the result in a ForEach, building a new list of Structures with as Attributes the Order and a List of values (or another Structure, if you have more values). This is also a typical method to construct a tree-like data structure for e.g. sending to a REST service (or serving a REST call).

What you'd basically do is keeping track of the current date in the list, and if it's the same as the previous, ListAppend the data to the List Attribute of the structure. When you encounter a new Order, or when you have processed the last item, you ListAppend the top-level Structure to the list used in the Table Records.

Note that for displaying the items, you either need a Web Block (in case you want to display them as a Table Records of their own), or instead of ListAppending to the List Attribute (as I described above), append them to a Text Attribute instead.

I hope this help you, if I understood your question Francisco.

Cheers,
Nuno Verdasca

Hi Nuno,

Thanks for your super detailed answer. Most of the solutions you suggest are a bit complex for me right now but helps a lot so I can study and learn more.

I'll create another aggregate just for the count and place it inside the customer detail page.

Many thanks,

Francisco


1
0
20 Dec 2019
Copy Link
2018-05-30 16-28-55
Gopinath Reddy Reddigari

Hi Francisco,

Can u try count by Order Id hiding all the attributes in Order table and then doing a group by on all attributes in customer  column this way u will have access to columns that you do group by in table.  



1
0
20 Dec 2019
Copy Link
UserImage.jpg
Francisco007

Gopi Nath wrote:

Hi Francisco,

Can u try count by Order Id hiding all the attributes in Order table and then doing a group by on all attributes in customer  column this way u will have access to columns that you do group by in table.  



Thanks for your answer Gopi but it didn't work as no results were shown when I published the module and tried it!


0
0
20 Dec 2019
Copy Link
2018-05-30 16-28-55
Gopinath Reddy Reddigari
Solution

Gopi Nath wrote:

Hi Francisco,


Please find the screenshot of the gird where I'm seeing the data. Also attaching sample oml. Not sure if you can publish because of the IPP , but adding it as a reference. 

Hi Francisco,

Can u try count by Order Id hiding all the attributes in Order table and then doing a group by on all attributes in customer  column this way u will have access to columns that you do group by in table.  





DemoApp.oml
2
0
20 Dec 2019
Copy Link
UserImage.jpg
Francisco007

Gopi Nath wrote:

Gopi Nath wrote:

Hi Francisco,


Please find the screenshot of the gird where I'm seeing the data. Also attaching sample oml. Not sure if you can publish because of the IPP , but adding it as a reference. 

Hi Francisco,

Can u try count by Order Id hiding all the attributes in Order table and then doing a group by on all attributes in customer  column this way u will have access to columns that you do group by in table.  





Thanks Gopi,

I can publish it but when I open it on the web I get this (slightly different from yours)

But thanks for the example. I'll do another try on my module - maybe I made some mistake!

0
0
20 Dec 2019
Copy Link
2025-11-19 06-14-01
Miguel Verdasca
Champion

You don't have to thank Francisco for anything. Also when I have doubts I like someone to teach me, and if we all do this, we all grow.

I am glad, even if it is a little complex for you as you say, that from here you have the solution to solve your problem.

Cheers and merry x-mas sesson,

Nuno Verdasca

1
0
20 Dec 2019
Copy Link
UserImage.jpg
Francisco007

I just found out that I can't do it on the customer detail page (which has a form) due to the same reason :)

Well, time to study your solution!

Happy festive season!

Francisco

1
0
20 Dec 2019
Copy Link
UserImage.jpg
Francisco007

I did it again but did not work... maybe I'm making some mistake I don´t know

0
0
20 Dec 2019
Copy Link
2018-05-30 16-28-55
Gopinath Reddy Reddigari

Francisco wrote:

I did it again but did not work... maybe I'm making some mistake I don´t know

Hi Francisco,

Is it possible to upload oml that you are working on. I can check if i can find anything


2
0
20 Dec 2019
Copy Link
UserImage.jpg
Francisco007

Gopi Nath wrote:

Francisco wrote:

I did it again but did not work... maybe I'm making some mistake I don´t know

Hi Francisco,

Is it possible to upload oml that you are working on. I can check if i can find anything


Hi Gopi,

Sorry, you were totally right!

I just figured out the error. I had a filter customerid=customer.id

After I deleted that all went smooth! Thanks for your help!

Francisco


1
0
21 Dec 2019
Copy Link
2018-05-30 16-28-55
Gopinath Reddy Reddigari

Francisco wrote:

Gopi Nath wrote:

Francisco wrote:

I did it again but did not work... maybe I'm making some mistake I don´t know

Hi Francisco,

Is it possible to upload oml that you are working on. I can check if i can find anything


Hi Gopi,

Sorry, you were totally right!

I just figured out the error. I had a filter customerid=customer.id

After I deleted that all went smooth! Thanks for your help!

Francisco



Hi Francisco,


I'm Glad it worked.


1
0
21 Dec 2019
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.