Limit entity result of a source of an Aggregate
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

mvp_badge
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

Thanks! just right in the spot!

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

mvp_badge
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

Thanks! just right in the spot!

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