Display field of most recent child record?

  • 0
  • 1
  • Question
  • Updated 3 weeks ago
  • Answered

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 somewhere, but I've tried about five different searches to see where the answer for this question would be.

Thank you!

Photo of Amber

Amber

  • 590 Points 500 badge 2x thumb

Posted 5 years ago

  • 0
  • 1
Make a summary field of the maximum of the Record ID# of the child status table records called [Maximum Record ID# of status records]

Make a new relationship where 1 Status is related to Many parents - ie a reverse relationship and the reference field at the right side will be that field [Maximum Record ID# of status records]

Then just do a lookup of the Status from the Child to the Parent. but QuickBase will see it as a lookup from a parent to a child.
Photo of Marlene

Marlene

  • 112 Points 100 badge 2x thumb
Hi!

My apologies for the very naive question but how/what is the syntax to get the maximum record ID#?
 I created a field and typed ' Max([CI-PN ID#]) ' under the formula field but the syntax seems to be incorrect.
 How do I get a max of a record?
And more generally how can I get the max of a table or a column of a table?

Thanks for your help
:)
You need to have a relationship first. If you need to have the Maximum of a whole table just to get it for some reason,, the you will need ot set up sort of a dummy table and enter one record into it.  It will be [Record ID#] of 1.

Then on the details table where you want to know the Max, you will make a formula field called perhaps [Link to max record table (=1)] and make it a formula numeric field with a formula of 1.

Then make a relationship where that 1 Max record table has many (in fact all) details records based on that link field used on the right hand side of the relationship.

Then make a summary field on that relationship for the max record ID. There is a button to make a summary field on the left side of the relationship.

Then do a lookup of that max field down to the child table and every child will know what the max record ID# is.
Photo of Marlene

Marlene

  • 112 Points 100 badge 2x thumb
Thanks!
I realize it is much different from what I would have done on excel/matlab..A whole new philosophy to embrace ;-)
I have done my homework and managed to retrieve the latest status which was my initial intend. Thanks a lot for the explanation!
Yes, I know that it seems strange to have to build a whole table, but that is what we do.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
Don't despair Marlene I am a long time Matlab user and we are going to bring more functional paradigms to QuickBase and use JavaScript for everything starting with replacing the formula language with script.
Photo of Marlene

Marlene

  • 112 Points 100 badge 2x thumb
Hi Danimal! I am actually going right now through your pasties #314 (which brings a functionnality that I expected to be built-in but is apparently not). I may have to come back to you about that too.
Anyway thanks to both of you for sharing your expertise. On-line support told me for both questions that is was just impossible to do in quickbase. I am glad to see that there are ways around..
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
Online support uses some coded language. When they say something is "impossible" they mean that you should use script.
(Edited)
Photo of Anthony Guillen

Anthony Guillen

  • 320 Points 250 badge 2x thumb
Is there any way to pull the most recent child record using maximum date instead of maximum record ID#?  In my application, some child records were created out of sequence with more recently created entries having older dates.  I imagine that using Maximum Record ID# works because then it ties to the record number on the reverse lookup.  I can't figure out how to make it work with date.. any thoughts?
No problem

Summarize max date.
Lookup to child

Summarize max record ID subject to the filter that date = max date.
Photo of Amber

Amber

  • 590 Points 500 badge 2x thumb
Wow, so impressive! I would NOT have thought of this. Thanks!!!
Photo of Jeff

Jeff

  • 10 Points
I tried this and I'm lost. I built a new simple app just to test this, but can't seem to get the Parents to display the highest numbered related status. Is there any more detail for this available?
Contact me via the information in my profile and I will have a quick look at your test app.
Photo of Jeff

Jeff

  • 10 Points
Thanks for the quick response. I found another response you wrote to a similar question, and that had enough for me to get it working.
Photo of Warren Willits

Warren Willits

  • 90 Points 75 badge 2x thumb
I know that this is a very old post but I have a similar but more complex issue. I have a fact table that captures aircraft base locations on a monthly basis (the new monthly data is appended to the table). The table captures many aircraft and their locations every month, but I am trying to pull out only the latest location for each aircraft, which means that I'm only looking for the last input for each aircraft.
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])


(Edited)
Photo of Warren Willits

Warren Willits

  • 90 Points 75 badge 2x thumb
OK, thanks I will give it a try.
Photo of Warren Willits

Warren Willits

  • 90 Points 75 badge 2x thumb
OK, so I have run into another issue. When I try to create a Summary Field on my Parent Table (Aircraft table) from my Child Table (Location table), I am getting an error that states "you cannot create a summary field using a relationship where the reference field is a lookup field". The issue is that the foreign key in my Child Table (Location table) is set up as a look-up from the Parent Table (Aircraft table) so that the user doesn't have to remember the detailed Aircraft codes when inputting a Location. Is there any way around this or do I have to create a new input that requires the user to manually type in the Aircraft code every time they input the Location data?   
Warren, can you describe the relationship between Aircraft and Locations?  What is the Key field of Aircraft?  Is it a simple relationships where 1 Aircraft has many Locations?

But if you have a legitimate reason for the setup you have, then the solution will be to have a mirror field which is maintained which has a duplicate of the related Aircraft field, so that it is not a lookup field.

Then typically I set up a save table to table copy to copy the locations table into itself merging on record ID#, and updating that mirror field.

Then I have an Automation fire to run that saved import whenever locations are added or edited and the lookup field does not match the scalar mirror field.   
Photo of Warren Willits

Warren Willits

  • 90 Points 75 badge 2x thumb
Yes, it is a single aircraft to many locations relationship. We have a dimension table with all the aircraft information and a fact table with all the various airport locations that the aircraft is based each month. The Aircraft table is basically a static list while the locations table is being updated every month.

I did try creating a new text formula field where I basically copied the Aircraft code from the Aircraft lookup input, but QuickBase still gave me an error stating that it was a Lookup field.
Can you tell me the Key field of the Aircraft table and the where you get the data for the field likely called [Related Aircraft]
Photo of Ryan Stanford

Ryan Stanford

  • 1,408 Points 1k badge 2x thumb
A possible work around, while not entirely clean... create a formula field which will have [Foreign Key] as the contents... use this new formula field to make the summary field. Same values, but technically not from the parent table to begin with.