List of orders showing the most expensive item on each order
Application Type
Traditional Web
Service Studio Version
11.10.18 (Build 41211)
Platform Version
11.11.0 (Build 26942)

I have a page with a table list that lists all of the sales orders in my system. The table includes order level information like order number, customer, sales rep, etc. This page has a search feature based on the aggregate filters.

In the table, I would like to include the highest priced item on each order, however, this is a sub-query of an order items data table.

  • The aggregate won't allow me to include a sub-query
  • I can do the sub-query in sql, however, then I loose the search function based on the aggregate filters
  • The aggregate doesn't seem to allow me to use the results of the sql query as a source

Is there some technique that I can use to include the highest priced item from each order in my table?

OrderExample.oml

mvp_badge
MVP

Hi Hawsidog,


You are right.

In order to achieve this use case, you will need a subquery and to use them, you need an Advanced SQL, although a pretty simple one. Something like this:


I included the OML in attachment with the example.


Kind Regards,
João

OrderExample.oml

Thank you João. On the example I posted, I already have a sql query which obtains the necessary results. I'm really looking for a way to combine the features of the sub-query with the filtering features of the aggregate. 

Is there any way to have a sub-query and use the filtering of an aggregate?

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.