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!
Solved! Go to Solution.
de-reference!!! you are amazing!
putting it all together the formula that works is:
MAXROW("Child_id ", โTimestampโ, [_THISROW] = [Parent_id])
Can you elaborate on this? You want to see the single most recent child record?
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!
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]
de-reference!!! you are amazing!
putting it all together the formula that works is:
MAXROW("Child_id ", โTimestampโ, [_THISROW] = [Parent_id])
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.
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
16 |