Skip to Content (Press Enter)
2021-04-01 07-44-07
João Coelho
127Views
3Comments
Solved
Limit entity result of a source of an Aggregate
Question
Traditional Web
Application Type
Traditional Web

Hi,

I have an employee entity that is connect to another entity "tasks" (an employe can have several tasks),  I need to have an agreggate that feeds a List Records, but I just want to get the last task. I could group by employeeid but then I can't display the other data into the List Records (such as employee name). 

This must be simple but I'm not getting it so, how should I do this?

Thanks in advance

0
0
16 Mar 2021
Copy Link
2018-10-29 08-31-03
João Marques
 
MVP
Solution

Hi João,


I don't think you can do this with an aggregate. You can however achieve this scenario easily with an Advanced SQL:


SELECT  {Customer}.*, {Purchase}.*
FROM    {Customer} INNER JOIN
        (
            SELECT  {Purchase}.[customer_id],
                    MAX({Purchase}.[date]) [MaxDate]
            FROM    {Purchase}
            GROUP BY {Purchase}.[customer_id]
        ) MaxDates ON c.id = MaxDates.customer_id INNER JOIN
        {Purchase} ON   MaxDates.customer_id = {Purchase}.[customer_id]
                    AND MaxDates.MaxDate = {Purchase}.[date]


This is an example of such a query.


Kind Regards,
João

See solution in context
0
0
16 Mar 2021
Copy Link
2021-04-01 07-44-07
João Coelho

Thanks! just right in the spot!

0
0
16 Mar 2021
Copy Link
2020-09-21 11-35-56
Sachin Waghmare

Hello,

You can sort the tasks entity with entity identifier in Descending order in aggregate and limit it to display only 1 record on the screen as shown below.

Thanks & Kind Regards,

Sachin

0
0
16 Mar 2021
Copy Link
2018-10-29 08-31-03
João Marques
 
MVP
Solution

Hi João,


I don't think you can do this with an aggregate. You can however achieve this scenario easily with an Advanced SQL:


SELECT  {Customer}.*, {Purchase}.*
FROM    {Customer} INNER JOIN
        (
            SELECT  {Purchase}.[customer_id],
                    MAX({Purchase}.[date]) [MaxDate]
            FROM    {Purchase}
            GROUP BY {Purchase}.[customer_id]
        ) MaxDates ON c.id = MaxDates.customer_id INNER JOIN
        {Purchase} ON   MaxDates.customer_id = {Purchase}.[customer_id]
                    AND MaxDates.MaxDate = {Purchase}.[date]


This is an example of such a query.


Kind Regards,
João

0
0
16 Mar 2021
Copy Link
2021-04-01 07-44-07
João Coelho

Thanks! just right in the spot!

0
0
16 Mar 2021
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.