linking 3 tables

[andyh]

I've got 3 tables - 1) Athlete, 2) Athlete Races and 3) Athlete Weekly Training Score. 

1 is linked to 2 and 3, but 2 and 3 aren't linked. 

3 is updated every week, with a new entry (score) per athlete each week.

For any relevant entries in 2) (i.e. where the race date matches the date of their training score), I want to add the training score (from 3) into their race record (2)

Is this possible with lookups? Or would it need to be an automation? I've tried both but can't quite get to the answer.

Thanks in advance!

[TheTimeSavingCo · Accepted solution]

I reckon you'd need an automation for this.  I'd probably have an automation that:
1. Triggers when the "Date" field in "Athlete Races" is updated
2. Has a "Find Record" action that will look in "Athlete Weekly Training Score" to see if it can find any records for this athlete for the same date
3. If the number of records found is > 0, put the found value of the training score into a field I've created for this purpose in "Athlete Races"

Which should do what I think you're trying to do I think

[andyh]

awesome, I hadnt thought of it like that but that makes total sense and works a treat. thanks!