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

[Aleksi]

May I ask what is the main reason that you want to have that previous task in a different task record? And I mean in generally.

[timsimpson]

So that each task record is linked to a previous task and then on to a subsequent task. I’d hoped that this would allow me to update a task’s duration or the date of the first task ([Prodn Start date]) of a project and the [start date] and End date of all the other subsequent tasks would update. These tasks would then all show on a calendar either by project, or as a full calendar, showing all tasks for all projects. By being able to select a [previous task] for each task record it would also be possible to change the order of tasks, or insert new tasks into a schedule.

[Bellave_Jayaram]

You could use data change workflows to handle the propagation of changes to related tasks’ sequencing as well as start and end times of those. However workflow rules cannot trigger other workflow rules.

I believe this problem is too complex for AppSheet as it stands now. You may want to try a simpler approach. Also, you may be able to do it via scripting.

[timsimpson]

Wow, Scripting. Not sure I’m up to learning another load of coding! I’ll have a look at workflows to see if they can help. Do you know if it’s likely to be that the Valid If is not being taken into account when Appsheet assesses whether an expression creates a cycle or is it more likely that the current Valid If doesn’t prevent the risk of a cycle?

[Bellave_Jayaram]

I am not sure but I believe it is the latter. Valid_If is just a constraint on 1 column but if the entities in the formula have other rules that have to be respected, and those other entities involve this column, then it is a cycle.

Anyway, I think @Phil or @praveen may be able to tell you more.

[timsimpson]

Thanks for your input. It has given me food for thought. I’ll see if anyone else has a bright idea.

[LeventK]

@timsimpson
You can use data change workflow to update the child records when a parent record is updated. However, provided you need other changes than just date, than you might need to construct more than one data change workflow and adjust some fairly complex conditional rules so that each one of the workflow rule will not clash with the others.

[timsimpson]

It is beginning to sound like I’ve created a nightmare for myself! It all felt so achievable at the beginning. I might try to create a test app to check the workflow.

[LeventK]

@timsimpson
It’s not a big nightmare as you think…

[timsimpson]

I’ll be the judge of that! Thanks for the suggestion. Let’s see how I get on.

[LeventK]

@timsimpson
Just drop us a line if you need a hand or help…

[Steve]

THE FOLLOWING HAS NOT BEEN TESTED!!!

You could consider calculating all the task dates based a project start date, a numeric task order, and task duration (in hours). The below is designed around this model.

Tasks must have an order number of at least 1, or no order number. A task with no order number will still be present in the project’s task list, but will have no dates attached to it. Multiple tasks may share the same order number (concurrent tasks), in which case the longest duration of those will determine the start date of the task(s) with the next highest order number. Task 1 starts on the project start date. The finish date is computed as the start date plus the task days described by the task hours. Subsequent start dates are set to the finish date of the preceding task.

Renumbering the tasks reorders them and the dates adjust automatically.

Projects (table)

Start

Type: Date

Finish (virtual)

Type: Date
App formula:

INDEX(
  SORT(
    [Related Tasks][Finish],
    TRUE
  ),
  1
)

---

Tasks (table)

Project

Type: Ref to Projects
Required?: ON

Order

Type: Number
Minimum value: 1
Initial value:

(
  MAX(
    SELECT(
      Tasks[Order],
      AND(
        ([Project] = [_THISROW].[Project]),
        ISNOTBLANK([Order])
      )
    )
  )
  + 1
)

Hours

Type: Number

Start (virtual)

Type: Date
App formula:

IFS(
  ISBLANK([Order]),
    "",
  ([Order] = 1),
    [Project].[Start],
  TRUE,
    INDEX(
      SORT(
        SELECT(
          Tasks[Finish],
          AND(
            ISNOTBLANK([Order]),
            ([Order] < [_THISROW].[Order])
          )
        ),
        TRUE
      ),
      1
    )
)

Finish (virtual)

Type: Date
App formula:

IFS(
  AND(
    ISNOTBLANK([Start]),
    ISNOTBLANK([Hours])
  ),
    WORKDAY([Start], ROUND([Hours] / 8.0))
)

[Bellave_Jayaram]

Goes to show how much depends on the way a problem is framed and how simple but elegant solutions such as this are possible just by reframing. Clever use of virtual columns and the INDEX and SORT functions makes data change workflow rules unnecessary.

Thanks @Steve!!

[timsimpson]

It’s funny, I’d kind of hoped @Steve might get involved. It’s too late here in the UK for me to fully grapple with this one but I have a feeling I’ve got my work cut out tomorrow morning. Thanks all. I’ll feed back once I’ve had chance to rebuild and test. T

[timsimpson]

@Steve - Sadly it throws up another cycle. It’s fine with one task, but the moment there’s a second it gives me the error “Error:Expression appears to be part of a cycle: /nExpression appears to be part of a cycle: SELECT(TASKS[Finish],AND(NOT(ISBLANK([Order])), ([Order] < [Task ID].[Order])))/n”.

I’ll see if I can find where it’s cycling.

[Steve]

The problem is that the app formulas for the Start and Finish columns of the Tasks table refer to each other. Investigating…

[timsimpson]

@Steve I feared they might. So it ends up running into the same issue as my version. Somehow the app needs setting up such that whilst the columns refer to one another, there are conditions which prevent any row ending up referring to itself. My head is beginning to hurt.

[1minManager]

Hi Tim

Not sure if I can help. But a few months ago we did a similar sounding App where each job had multiple sub-tasks. What the App does is after a sync it does a calculation of Now() + Task1Duration + Task2Duration + Task3Duration etc to give completion Date Time. What made it more complicated was we had to do the calculation based on only working 0900-1700 Mon-Fri.

It also kept the tasks in order, yet you could either delete tasks or add new ones into the sequence and it will recalculate

It works fine, but was a pig of a job to get right (over 2 days work!). Then to top it all off, the client now wants to do something else

Anyway… If I can help, just let me know

Simon@1minManager.com

[timsimpson]

@1minManager Thanks for the input. In order to run the calculation is the completion date a Virtual or real column? I’m assuming VC.

Did the app allow for concurrent tasks too? Such that task 2 might lead into various subsequent tasks.If not then I can see how it might be possible to calculate a sum of the durations of all tasks with a lower Order number, but that would get scrambled if some tasks were scheduled to run concurrently.

While I figure this out I’ve got a clunky workaround that creates an action button to ‘refresh’ a row if it’s start date doesn’t tally with the end date of it’s [previous task]. The thing is that it requires the user to refresh manually and can take several syncs to flag all the ‘misaligned’ tasks. But the fact that ultimately it works might be a clue to an automated solution but I can’t figure it out just yet.

[1minManager]

Hi Tim

Yes its a VC, its got to be.

You mean that some tasks could be run in parallel? If so, not it doesn’t, but it could be included. So assume you have 4 tasks - A,B,C&D. But B&C are running in parallel. So what your really after is something called the ‘Critical Path’. Which is the LONGEST time the task could be. So assuming B was longer than C the Critical Path is A->B->D. So you could mathematically model this by creating a formula that says C’s duration is zero providing B is in parallel and longer.

VC’s are a pain to work with because if you look at them in a table or inline view you get the figure form the last sync. But if you edit a row, it gives you a ‘live’ figure.

Simon@1minManager.com