Count Comments (Expression) for a Particular Record

[Tony_Insua]

I have an ACTIVITY table and a COMMENTS table. The COMMENTS table references a Specific ACTIVITY ID in the ACTIVITY table and I’ve set this up properly in the columns.

However, I’m looking for the expression to count the number of comments for a particular activity. Some activities may have 2 comments, others could have none but what would be the general expression? Also, I would like to display this COUNT in the ACTIVITY table, not the COMMENTS table as I would like to include a text field to say something like “This activity as 8 comments” which would be a concatenation.

Thanks!

[LeventK · Accepted solution]

You can create a Virtual column and display this both in the table and the detail view as well:

IF(
    COUNT([Related Comments]) = 0, "This activity has no comments!",
    COUNT([Related Comments]) = 1, "This activity has 1 comment",
    TRUE, "This activity have "&COUNT([Related Comments])&" comments"
)

OR

SWITCH(
    COUNT([Related Comments]),
    0, "This activity has no comments!",
    1, "This activity has 1 comment",
    "This activity have "&COUNT([Related Comments])&" comments"
)