Lookup field when related records change

[Matt Malyschko]

The situation -

I have 2 tables: Tasks, Dates

Tasks has fields: id, dateNext, zz_1 (zz_1 is a global constant = 1)

Dates has fields: id, id_Task, date, complete

Date is related to Tasks via:
id_Task = id and zz_1 NOT complete

The relationship is sorted by date

So, all Dates that have not been marked as complete will show in a portal the Dates records in the correct order.

 

My question is -

I want dateNext in Tasks to be automatically updated when a record in Dates is changed. In particular, when a record in Dates is marked as 'complete' I want the nextDate in Tasks to update with the next value in the portal.. if no record exists it can keep the current value that is stored.

I tried using a Lookup field for nextDate but that didn't work, and Relookup Field Contents doesn't work because of the NOT complete?

I'd like this automated and not have to check each time if they are in sync.

 

TIA

[comment]

A stored field will never update merely because a related record was modified.

Why do you need it to be a stored field? You can display the next date quite easily, using either an unstored calculation field or a one-row portal.

 

I also didn't get this part:

1 hour ago, MattyM said:

if no record exists it can keep the current value that is stored.

Where does the "current value that is stored" come from and what does it represent?

[Matt Malyschko]

I need it to be a stored field because this field is used by a calendar and needs to be editable, and in the TASKS table.