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

Help Center Get Answers
1

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.

2

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

Thanks a bunch!

3

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:

3oU7karq9X5t_LTH0YTHQw.png1162×266

ogjZ5zJ1P0YbjzSRIMl0GA.png1170×240

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

Hope this helps.

1 Like
4

Thanks @JulianKirkness still relevant today and works despite the old images not appearing in posts anymore.

I had a slight twist to this use case where I needed to select the latest item before the current date, ignoring any with dates after the currentTime(). So step #1 was altered to:

{Start Date} <= currentTime() ? {Start Date} : 0

The parent Max field correctly selects the current entry at #2 and from there everything works. Saved a foray into make.com with complicated rules and updating records.