Take a look at this modified base. I’m not overly thrilled with it, because it relies upon autonumber — but on the other hand, it works. It also allows you to drag-and-drop your tasks into the sequence you desire[, work a little mumbo-jumbo,] and have the new order discovered automatically. (Yes, as that clause within the square brackets suggests, the process isn’t 100% automated — but nearly so.)
To make this work, I have added a handful of fields:
-
{Tasks::Index} - Autonumber.
-
{Tasks::ActiveIndex} - Formula:
IF(
AND(
FIND(
'prog',
{Project Status}&''
),
NOT(
OR(
Done,
Completed
)
)
),
Index,
BLANK()
)
-
{Tasks::Next} - Rollup:
Table: [Project]
Field: {MinIndex}
Aggregation formula: IF(Index=values,1)
-
{Project::MinIndex} - Rollup:
Table: [Tasks]
Field: {ActiveIndex}
Aggregation function: MIN(values)
Essentially, {Tasks::ActiveIndex} contains the autonumber for all tasks where {Project Status} is ‘In progress’, there is no {Completed} date, and {Done} is not checked; this allows the ‘next task’ process to ignore all completed tasks.
{Project::MinIndex} finds the smallest value of {ActiveIndex} for each project.
{Tasks::Next} flags task records where {ActiveIndex} equals {Project::MinIndex} — that is, the next step for each active project.
You can now filter based on {Next} to show next tasks across all projects.
After rearranging the order of project tasks
Obviously, if you change the order of project tasks, the top-most task — your new ‘next task’ — will have a higher {ActiveIndex} than other tasks for the project. To reindex your tasks and correct the determination of ‘next tasks,’ do this:
-
In the <Tasks::Active: Project> view (presumably; in any case, in the view where you resequenced the tasks) unhide the {Index} field.
-
Right-click on {Index} and select ‘Customize field type.’
-
Change the field type from ‘Autonumber’ to ‘Single-line text.’ Save.
-
Right-click on {Index} again and change the field from ‘Single-line text’ back to ‘Autonumber.’ Save.
When {Index} is converted back to ‘Autonumber’ a new sequence is generated, one that assigns the top-most task a lower {ActiveIndex} than those of other project tasks.
Again, I’m not incredibly happy with this solution, simply because of this little dance to resequence tasks. On the other hand, it removes the entire ‘next task’ process from the realm of manual intervention — and, still somewhat amazingly to me, it actually works. 
