Create a New Column That References Previous Record in Another Table

[arhomberg]

Hello Everyone!

I am looking to find a way to create a new column in a table that looks for a record in another table with the associated id, and pulls a column from it.

Above is a picture of the current data layout. I would like to create the 'New Create Date' in my Table2 that references the previous 'systemmodstamp' in Table1 for the matching 'id', which would be "7/8/2021 19:08". You'll notice that it has to be the most recent 'systemmodstamp' for that specific record. The pattern is that the next most recent entry in the system is suppsoed to have a 'createddate' of the previous records 'systemmodstamp'.  If there is no version of the Table2 'id' in Table1, it should do nothing.

Any advice on how to approach this would be greatly appreciated.

Thanks!

[Vera_33 · Accepted solution]

Hi @arhomberg 

You can use UI to Group Table1 by id, find the max systemmodstamp, then join Table2.