Displaying Comment Count on Card View and Ordering by Two Dates

[exerlanda]

Hi everyone,

I'm building an AppSheet app with two data sources:

• Notes: note_id, note_title, note_text, note_date
• Comments: comment_id, note_id, comment_text, comment_date

Notes are displayed in a card view.

I'd like to show the number of comments on each card and order the notes by both note_date and comment_date. I'm having trouble finding a solution. Is this possible?

[Suvrutt_Gurjar · Accepted solution]

---

@exerlanda wrote:

I'd like to show the number of comments on each card

---

In the Notes table you would have a column called say [Related Notes] .

Please add another virtual column in the Notes table called say [Comments_Count] with an expression something like COUNT([Related Notes]) .  [Comments_Count]  will capture the number of comments for that note. Display that column in the Notes card view.

---

@exerlanda wrote:

order the notes by both note_date and comment_date. I'm having trouble finding a solution. Is this possible?

---

---

@exerlanda wrote:

both note_date and comment_date.

---

Please add a virtual column called say [Latest_Comment_Date] in the Notes table with an expression something like 

MAX([Related Comments][Comment_Date])

where [Comment_Date] is the date column in the comments table.

Please create two card views

1. "Notes by Note date" . Use [Notes_Date] in the Notes table to sort descending that card view.

2. "Notes by Comment date" . Use [Latest_Comment_Date] in the Notes table to sort descending that card view.

[Suvrutt_Gurjar · Accepted solution]

---

@exerlanda wrote:

I'd like to show the number of comments on each card

---

In the Notes table you would have a column called say [Related Notes] .

Please add another virtual column in the Notes table called say [Comments_Count] with an expression something like COUNT([Related Notes]) .  [Comments_Count]  will capture the number of comments for that note. Display that column in the Notes card view.

---

@exerlanda wrote:

order the notes by both note_date and comment_date. I'm having trouble finding a solution. Is this possible?

---

---

@exerlanda wrote:

both note_date and comment_date.

---

Please add a virtual column called say [Latest_Comment_Date] in the Notes table with an expression something like 

MAX([Related Comments][Comment_Date])

where [Comment_Date] is the date column in the comments table.

Please create two card views

1. "Notes by Note date" . Use [Notes_Date] in the Notes table to sort descending that card view.

2. "Notes by Comment date" . Use [Latest_Comment_Date] in the Notes table to sort descending that card view.

[exerlanda · Accepted solution]

Hi Suvrutt_Gurjar,

Thank you, it works fine.

I added a virtual column in notes called "comment_last_dateTime" with the formula MAX([Related comments][comment_dateTime]), and I keep only one view but I added two "sort by". The first one by "comment_last_dateTime" and the second by "note_date". I set "comment_dateTime = NOW()" by default, then everytime a comment is written, it is listed on the top. Great thank you for the help.

And for the count, I used the same idea, I created another virtual column called "comment_count" with the formula "COUNT([Related comments][comment_id])" and customize the layout to show the column in the card.

Wonderfull. Adding usermail as "note_name", it's create a perfect simple note taking app.

Thank you again.

[Suvrutt_Gurjar]

You are welcome. Excellent.

---

@exerlanda wrote:

And for the count, I used the same idea, I created another virtual column called "comment_count" with the formula "COUNT([Related comments][comment_id])" and customize the layout to show the column in the card.

---

Just to display the count, you may not need COUNT([Related comments][comment_id])

COUNT([Related comments]) should be enough. 

In fact if you check in test pane [Related comments] and [Related comments][comment_id] will furnish exactly the same list, if [comment_id] is a the key column of the "Comments" table.

This is so because  [Related comments] is essentially system generated column of the list of key values of related records of the child table. So,  [Related comments][comment_id] and [Related comments] produce the same list.

[exerlanda]

Indeed, 

COUNT([Related comments])

 is enough. Thank you!

The original sort wasn't prioritizing the latest notes/comments correctly. I renamed the `comment_last_dateTime` column to `memo_last_comment` and updated the sort expression to:

if(max([Related comments][comment_dateTime])>[note_dateTime],
       max([Related comments][comment_dateTime]),
       [note_dateTime] )

Notes are now sorted by memo_last_comment in descending order, ensuring the latest note or comment appears first.

I also added two helpful actions:

1. Add New Comment: Navigates to the `comment_Form` view, pre-filling the `memo_id` with the current note.

LINKTOFORM("comment_Form","note_id",[_THISROW])

 2. View Comment List: Navigates to the `comment_Inline` view, filtered to display comments related to the current note.

LINKTOFILTEREDVIEW("comment_Inline",
                   ([memo_id]=[_THISROW].[Row ID]))

[Suvrutt_Gurjar]

Great going. Keep it up.