Getting the Last Record Belonging to a User

[Bigun]

I have a database with a table (Books) with three values (Trans_ID, Prev_ID, and User_ID).  The User_ID is an auto-entered value based on the current user's ID.  The Trans_ID is an auto-entered serial value.  The problem is with the Prev_ID field.  I'm trying to get the last Trans_ID entered by that particular user and I keep having issues doing so.  So far my best effort was to create another instance of the table in the "Relationships" tab of the Books table (Books Prev Calc), make them relatable by matching User_ID to User_ID, then make Prev_ID a calculation:

Evaluate this calculation from the contect of Books Prev Calc

GetNthRecord ( Books Prev Calc::Trans_ID ; Get (RecordNumber) -1 )

So far the current behavior returns the last entry in Books regardless of who the user is.  Example set of current behavior:

 

Current Output

User_ID |Trans_ID|Prev_ID|
User1   |1       |?      |
User1   |2       |1      |
User1   |3       |2      |
User2   |4       |3      |
User2   |5       |4      |
User1   |6       |5      |

An example set of data of my desired end result:

 

Desired Output

User_ID |Trans_ID|Prev_ID|
User1   |1       |?      |
User1   |2       |1      |
User1   |3       |2      |
User2   |4       |?      |
User2   |5       |4      |
User1   |6       |3      |

The question marks represent the user's first entry, therefore there wouldn't be a previous entry.  But that is the desired outcome I am looking for.

 

*edit*

 

Just realized this probably belongs in the "Calculation Engine" section.

[eos]

Add trans_ID > trans_ID to your self-join and sort by Trans_ID, descending; the first related record* via that relationship (evaluated from your original table's context!) will have the previous Trans_ID for the respective User_ID.

 

*EDIT: “First” meaning you can simply reference the related field name in a calculation; also, you don't need a calculation field to display the value on a layout (if that layout is based on the other TO of the self-join, that is); simply place (the related version of) the field on the layout.

[Bigun]

Add trans_ID > trans_ID to your self-join and sort by Trans_ID, descending; the first related record* via that relationship (evaluated from your original table's context!) will have the previous Trans_ID for the respective User_ID.

 

*EDIT: “First” meaning you can simply reference the related field name in a calculation; also, you don't need a calculation field to display the value on a layout (if that layout is based on the other TO of the self-join, that is); simply place (the related version of) the field on the layout.

 

Worked exactly as I needed it to.  Thanks!