Thanks Shreyash,

I've done that also, but the problem is that if i add a new comment then, the new record is created in the  Invoice entity 

So you want to update that existing record ?

It is not a new record, at least I don't see it in the image, is it possible to show the invoice entity values?(when I say new record I mean a new record on the database), you can see that the values on the image are from different entities, what is happening is that you are joining three entities where values are different, and that's why you are getting more than one record.

For the same invoice, there is more than one comment, to display the comments a new line on the table will be added.

What you can do is create a block, pass the invoice id and on the block you display the comments, put the comment block on the comment column and remove the comments entity source from the GetInvoices. This way, you will have just one line for each invoice, possibly.

Yes,

I've a Entity name Invoice in which there are many attributes such as:

1.Invoice Number

2.Customer Name

Etc.

I've another Entity name UserComments in which the following attributes are present:

1.Date Of Comment

2.Comments 

3.Expected Date

4.Invoice Number(which is auto Generated if i click on a particular Invoice number of Invoice Entity.

Now, i can create many comments related to particular Invoice Number, But in the Invoice Entity Screen I only want to display the latest comment made on that invoice number.

So, you do the same as I said in my last comment about creating a block and removing the comments source entity from the getInvoices.

But on the block, you sort by DESC order with a maximum of 1 record. And that will be the value displayed on the screen, the last comment.

Let us know if it works.

Kind Regards,

Márcio

Thank You Marcio,

 I'll definitely try this and revert back to you.

Thank You Marcio,

 But the problem arrives is that the comment is showing in all invoice entries.

 My expected Result is 

If in Invoice entity there are two invoice number 1 & 2 in both the invoice number i make different comments such as 

Invoice number 1 - comment is 1

Invoice number 2 - comment is 2

Now in Invoice Screen i want to display respective comments of both Invoice Numbers.

And if i made a new comment in Invoice number 2, then it should update in screen with the latest comment on the screen.

Hope you understand what i'm trying to say.

 Thank you.

Please refer the attachment below.

The block will get the invoice id and you filter the aggregate(in the block to show the last comment) by the invoice id passed to the block using an input. Right now I am not seeing any filter. If you filter you will get (the comments for the invoice id you are passing by input parameter) the last comment of each invoice.

EDIT:

Anyways, @Stefan Weber's answer is also a solution.

Thank you very much Marcio,

just a little help that in filter aggregate what should i write I'm unable to understand,

if you just help me out will be great for me.

I just wrote an Algorithm, if incorrect please correct me

1. Create a block

2. take a list from widget 

3. fetch Data from database in Block, take the GetInvoice Aggregate and join with UserComments entity.

4. Add a Filter(idk what to add)

5. Replace the block in place of Comments column in Invoice Screen.

1. Create a Block

2. Create an input parameter in the block to get the invoice id

3. Fetch Data from the database in Block, with the UserComments entity as Source

4. Add a Filter In the aggregate in the filters tab - UserComments.InvoiceId=InvoiceIdInput (this is the invoiceId from the input in the block)

5. Sort by DESC the date you created the comment and in the end put the aggregate with max records 1

6. Put an expression in the block where the value is the result from the filtered aggregate.

7. Put the Block in the Comments column in the Invoice Screen's table and pass the InvoiceId using the aggregate you are using to show data on the table. It's going to be GetInvoices.Invoice.current.Id 

Thank You Márcio for helping me in this Problem.

Thank you.

Hi Dev,

"not getting result" is not good enough. You're a software developer. You've been told how to solve your problem. These are the steps you need to take:

1. Understand what Márcio wrote, not blindly copy it. He explained it in a way that you should be able to understand;
2. Debug the code if it doesn't work as expected. Look at the input of the web block, see what's the result of the query, try to understand why it doesn't work.

We can help you with how to do something using OutSystems. We can't help you with how to be a good software developer.

Or, to avoid aliases and custom structures, something like:

LEFT JOIN {Comment} ON {Comment}.[Id] = (
   SELECT TOP 1 {Comment}.[Id]
   FROM {Comment}
   WHERE {Comment}.[InvoiceId] = {Invoice}.[Id]
   ORDER BY {Comment}.[CreatedOn] DESC
)

An alternative would be, like Márcio wrote above, to create a Block that has the InvoiceId as input, and retrieves and displays the top comment.