Equation Help. I want to select the value from a child record that has the latest creation date

Hi Guys - I am new to equations.

This is my data structure

ParentRecord (id, name, latest_score[equation] )

|____ ChildRecord (id, ParentRecord.id, date, score)

In the ParentRecord I plan to have an equation field called "latest_score"

Using SQL I would set this value as

SELECT score FROM ChildRecord
WHERE ChildRecord.ParentRecord.id = ParentRecord.id
AND MAXIMUM(date)

The purpose is to always have the latest score shown in the parent record.

Can I do this with an equation field? And if so - can somebody provide an example please.

Great! I see what you are doing here. I can make this work for what i need.

Thanks a bunch!

Hi Mark

I've worked out a way to do this for you - but it's not pretty...

  1. On your child record create an equation field based on your Date field (date format type)
  2. On your parent record, create a Max Date field which takes the Max of the equation field above - the reason for using the above is that Max only seems to offer equation fields and numerics (and not normal dates)
  3. On your child record create an equation field called Max date which is simply picking up the max date from the parent.
  4. Again in the child record, create an equation field called something like Latest Score with a formula like:

    {Equation Date} == {Max Date} ? {Score} : 0

  5. On the parent record, create a Sum field which sums the Equation Date field from the child.

This seems to work - I haven't tested it that thoroughly though - here is what the two tables would contain:

Note that I connected scores to a test Staff table that I use for various things.

Hope this helps.