Display field of most recent child record?

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.

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!

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:

1. 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.
2. 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.
3. 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"].
4. 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.

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.   

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]

Hi All, 

So this was a very helpful discussion thread and I have 99% of this working for me, however, I am unable to pull the most recent data point when its a "user" field.  Is there a reason I cannot "summarize" a user field?

------------------------------
Brandon Davey
------------------------------