Displaying Comment Count on Card View and Ordering by Two Dates

AppSheet AppSheet Q&A
, , ,
1

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?

2

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.

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_0-1736478493798.png
Suvrutt_Gurjar_0-1736478493798.png1006×436 25 KB

3 Likes
3

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.

2 Likes
4

You are welcome. Excellent.

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.

2 Likes
5

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])
  1. 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]))
2 Likes
6

Great going. Keep it up.

1 Like