How to check if another table has a record that meet a certain criteria?
I have 2 tables, one with events and one with tasks.
Task ID, Event ID, Date
1, c, 24/03/2016
2, a, 06/06/2016
3, e, 01/12/2016
4, b, 01/01/2015
5, f, 02/08/2016
6, a, 02/02/2017
Event ID, Date
What I would like is: while I am loading the Events table, can I have a calculated field where it checks if the Tasks table contains an item that has the same Event ID and a date older than the data in the event table?
So basically, when the events table is loading its first row, it should ask itself "Is there a task with an [Event ID] = 'a' with a Date < 01/01/2017 ?" and should return "Yes".
When the Events table loads its 4th row (event d) it should say "No" as there is no task with an event ID of 'd'