Display field of most recent child record?
Parent table: Computers I want a field in this table to reflect the most recent "status" that was entered for each child record in a Statuses table. I'm sorry -- I'm sure this has been answered som...
Forum Discussion
There is a new type of summary field which makes this much easier now.
1. Make a summary Maximum of the [Record ID#] of the locations table. call it [Record ID# of most recent location]
2. Look that up from the Aircraft down to Locations.
3. Make a new summary field of Combined text type to summarize the locations, subject to the filer that [Record ID#] = [[Record ID# of most recent location]
That will be the last location now up on the Parent Aircraft record.
That field will have a bubble-like format.
If that bothers, you, you can make a new formula text field field with the formula
ToText([my combined text summary field of the most recent location])
1. Make a summary Maximum of the [Record ID#] of the locations table. call it [Record ID# of most recent location]
2. Look that up from the Aircraft down to Locations.
3. Make a new summary field of Combined text type to summarize the locations, subject to the filer that [Record ID#] = [[Record ID# of most recent location]
That will be the last location now up on the Parent Aircraft record.
That field will have a bubble-like format.
If that bothers, you, you can make a new formula text field field with the formula
ToText([my combined text summary field of the most recent location])
- This is a nice technique. Unfortunately, I found the description fairly confusing, so I'm going to restate it in a way my beginner brain understands. The following description assumes a relationship has been created between Parent and Child tables and you are on the Edit Relationship page:
- On the Parent side, create a Summary Field of type Maximum and relate it to the [Record ID#]*. Call it [Latest Child Id]. This field will capture the Record ID# of the most recent Child record related to a given Parent.
- On the Child side, create a Lookup Field that references the Parent's [Latest Child Id] (the one we just created in step 1). The name will default to [Parent - Latest Child Id]. This will be a field on each Child record referencing the Child [Record ID#] that the Parent sees as the latest one.
- Finally, on the Parent side, create a Summary Field of type Combined Text that references the field you want to display, say "Your Field", from the latest Child. Under the "Only summarize records where the following is true" section, set [Record ID#] equal to [Parent - Latest Child Id] (the lookup field created in step 2). This allows the Summary Field to filter out all but the last of the Child records. Call the new Summary Field [Latest Child "Your Field"].
- Display the Parent's new Summary Field [Latest Child "Your Field"] on reports and forms as called for in your requirements or use cases.
Note wherever I've used "Child" or "Parent" in a field name substitute the names from your tables.
* [Date Modified] might be a better way to capturing the "most recent record" depending on your use case.
