Skip to main content
Question

How to use formula or summary field to get latest record data?

I have a table sales and a child table of products.

I am trying to find the latest related child (products) field "price". I can create a summary field on the parent to get the latest date but it will not get me the "price" field. How do I use the formula field to get the latest date record and return the "price" value of that record?

Thanks!

This topic has been closed for replies.

Do a lookup of the [latest sale date] down to the child table.

Then do a summary of the price field (say maximum), where the [date of the sale] is equal to the [Latest Sale Date].
  • GeorgeFong
  • Author
  • Registered
  • March 7, 2014
Works! Thanks.
There is also a technique for getting text values from a Most Recent Child but we will save that answer for when you post the question "I want to get the name of the Customer who bought the Product Most Recently"
Mark, i know it's been 4 years, but how do you get the name of the Customer who bought the Product Most Recently?  :)
Hi Jana,
Yep, I'm still hangin' out on the Community Forum ...

To do that you  use the reverse relationship technique.

Assuming that you want to know the name of the customer who had the sales with the highest [Record ID#], on the relationship where 1 Product has Many sales you would do a summary maximum of the [Record ID#] field and call it 

[Record ID# of the last sale]

Then do a new relationship where 1 sales has many Products, and on right side of the relationship, rather than letting Quick Base create a new field for you, use the field [Record ID# of the last sale]

Then lookup any fields from the last sale down to the Product, such as [Customer Name].
Thanks Mark!  So I must be doing something wrong. For testing purposes due to failure...no matter what Parent side i create my summary "Max Record ID#" field on, i cant get it to show up in the drop down list when i create the new Relationship looking back at that table, so to speak.   

Suggestions?
Ah, can you tell me the Key field of you child table - the real child table.  Is it the usual [Record ID#] field or another text field.  I suspect that the child table Key field is not the [Record ID#] field and hence you are "SOL".
LOL...neither table's Key is usual [Record ID#] field actually....soooooo, I guess that means i am "SOL" huh!  Any work arounds?
I suppose if you are desperate enough you could set up a Sync table for the Child table and on that table make the Key field be the Record ID# field.  Set it up to Sync update once an hour.

The do the reverse relationship using that table.  I have not totally thought it though it there is a solution in there somewhere.
Terms & ConditionsAccessibility statement