Parent / Child expressions with timestamp - MAXROW , Select, or Lookup?

[Ms_Margo]

Hi AppSheeters!

I have a parent and child table question and can’t sort out if i should use a lookup, select, maxrow or some combo… Either way - I can’t get any of it to work

Long story short, I want my parent table to list the latest and greatest info from the child based on the timestamp of the child table.

Parent Table:

Parent_id	Key
Parent_lastest_status	Return the most recent child_status based on the Child_timestamp
Parent_lastest_manager	Return the most recent Child_manager based on the Child_timestamp
Parent_lastest_checkin	Return the most recent Child_checkin based on the Child_timestamp
Parent_lastest_checkout	Return the most recent Child_checkout based on the Child_timestamp

Child Table:

Child_id	Key
Parent_id	Reference
Child_timestamp	NOW()
Child_status	ENUM: Store 1, Store 2, Store 3
Child_manager	ENUM: Mgr A, Mgr B, Mgr C
Child_checkin	Date
Child_checkout	Date

Any help would be greatly appreciated

Thanks!

[Marc_Dillon]

Ms_Margo:

Long story short, I want my parent table to list the latest and greatest info from the child based on the timestamp of the child table.

Can you elaborate on this? You want to see the single most recent child record?

[Ms_Margo]

Correct!

I feel like this is similar to a clock in/out. In the end, we need to see one table that list the last (the latest timestamp) site, manager, and checkin and checkout from the child table.

Thanks!

[WillowMobileSys]

Ms_Margo:

Correct!

First, this is kind of an odd usage of Parent/Child but I do understand why you are doing it.

The question in my mind is how do you update the Parent Table?

Because the Child table has new rows that are likely added fairly frequently, I think what I would do is create the Parent Table with ONLY the Parent ID column. Add your other Parent Table columns as Virtual Columns and include one more for Latest _Child - which is set by a MAXROW() expression.

Then you can de-reference [Latest_Child] to set the other columns and all of these columns will automatically update anytime a new Child Row is added and updated on a users device.

Setting the Parent Table columns with App Formula expressions:

Parent_lastest_status   => [Latest_Child].[Child_status] 
Parent_lastest_manager	=> [Latest_Child].[Child_manager] 
Parent_lastest_checkin	=> [Latest_Child].[Child_checkin] 
Parent_lastest_checkout	=> [Latest_Child].[Child_checkout]

[Ms_Margo · Accepted solution]

de-reference!!! you are amazing!

putting it all together the formula that works is:
MAXROW("Child_id ", “Timestamp”, [_THISROW] = [Parent_id])

[Marc_Dillon]

INDEX( ORDERBY( [Related Children] , [Timestamp] , TRUE ) , 1 )

Should be better performing than MAXROW(), since it doesn’t have to look at the entire child Table.