Reference to same table creating a cycle

[timsimpson]

Hi.
I’m working on a task management app with two main tables; ‘Projects’ and ‘Tasks’. Each Project has a number of ‘Tasks’. Each Task has a [Start Date] column, [Duration] in days and this generates a value for the [End Date] col.

In order to create a schedule in which these Tasks can be sequential, each Task also has a [Previous Task] col. which is a Ref type, referencing the same Tasks Table (This May Be Part Of The Issue!) and allowing for the selection of other [Tasks] from the same project . To make sure the reference list doesn’t allow for the Row to reference itself I have used the expression:
SELECT(Tasks[Task ID],AND([Project]=[_THISROW].[Project],NOT([previous task]=[_THISROW]),NOT([task ID]=[_THISROW])))
as a Valid If constraint.

I’ve managed to get each Task to pick up its [Start Date] from its [Previous Task] col. taking the end date from the [Previous Task] using expression:
“IF(ISBLANK([prodn start date]),WORKDAY([Previous task].[end date],1),[Prodn start date])”.

This all works well while I am adding new task rows but If I want to change the [prodn start date], or [duration] of a task, or add a new tacks into the sequence I need to edit each task manually. So I tried replacing the [Start Date] and [End Date] columns with virtual columns, so as to get the entries to update but I am getting an error message that the app seems to be in a cycle. I appreciate that the Ref column usually references another table but in this case it seems to work until the [Start Date] and [End Date] cols are VCs. I am assuming that either the App is unable to recognise the Valid If restraint as a way of preventing the cycle. Or what’s probably more likely is that I haven’t spotted the gaping holes in my Valid If expression.

So, I’ve concluded I’m going about it all wrong but can’t see a way out. Is there some other way to allow the selecting of other rows in a table in a similar way to that facilitated by a reference?

Or, is there another clause I need to add to the Valid If restraint in order to prevent the cycle?

Sorry to have gone on, but I wanted to lay out as much on the table as possible.

Thanks in advance.
T