Hello, I have a question of how I would be able to keep track of dates between a discussion topic and comments so that the most recent date associated with the discussion would be tracked and then applied to a virtual column to keep track of this result. The comments and discussion topic are in 2 separate tables and I’m trying to keep track of the latest entry so that way I can filer the discussion topics out based on a few days old, 1 week old, and a month old.
For example, Discussion table has a column Date Posted, another column called Discussion Topic and a column ID. Date Posted automatically creates the date using TODAY(), Discussion Topic is the place where the message is stored, and then ID sets the UNIQUEID() automatically so every topic can be distinguished from one another. Plus, a virtual column commentLink has a REF_ROWS(“Comments”,“Comments ID”) to link the Comments table to the Discussion table.
Then there is another table Comments, which has its own ID column that tracks the comments themselves with UNIQUEID(), Discussion Link, which grabs the unique ID of each row in the Discussion table and stores it in the Comments table. Plus, a Date column is used, which produces the date of comment creation automatically using TODAY(), and a column Comments Topic, which is where the comments are stored and linked to each discussion. So how would I grab the date of the last created comment entry for a discussion and then store its value so I could use that value for another expression.
I wanted to filter the last date per Discussion Topic/Comments Topic pairs where the last date entry under each ID is kept track of so that it can be filtered into New(0-5 days old), 1 week(6-10 days old), and 1 month+(over 30 days old). Sorry I know this is long and drawn out, but I didn’t know how else to explain this properly and would appreciate the help of how to do this expression. I have tried LOOKUP, calling to the table and column in the Discussion table, I think that I would also have to use MAX in there as well to grab the most recent date. Thanks for the help
If you wish to get it in the discussions table in a VC, the expression will be something like
TOP(SORT([Related Comments][Comments Date], TRUE), 1)
[Related Comments] is the reverse reference column in the discussions table.
If you wish to have it in a real column, you could try with reference actions. Asample app for reference actions is given below.
You may wish to elaborate this requirement. It is not exactly clear what you mean by pairs. Meaning it is known pair will be discussion/comments but are you needed pair of dates to be sorted by the time period? Could you give examples?
Ok I’m starting to see it of what I might be able to do, still not best with the expressions though. The pairs that I was referring to was just keeping track of the latest date from a discussion and those comments relative to the discussion so that only the latest entry date would be used in another expression. That way you could tell if the discussion was still lively or non-active in a sense. I was just trying to grab the most recent date from the discussion and those comments of that discussion so that I could keep track of how active the discussion was.
Your exact requirement is still not clear. However with the suggestions in the earlier post to get the latest comments date for a discussion, you could form the desired combination. Please do write back if you need any specific more help from the community. All the best for your app building.