Now, I would like to have the “Main Status” autmatically update based on what’s in the other columns. It would need to look for a non-blank value in the other columns and have logic that goes something like: “if the ‘Video Status’ is ‘recording’, ‘editing’ or ‘ready for review’, set the ‘Main Status’ to ‘In Progress’”
I tried to build a formula for this, but my formula-fu is too weak.
Looking for any help with this. Also, maybe I’m just over-complicating this? Any suggestons for how to build a system with different statuses available for different work types is appreciated!
There are several ways of doing this of course, but I recommend in the table where you define your type-specific statuses, e.g. in your Video Statuses table, do:
Video Statuses
Name
Main Status
Recording
In Progress
Editing
In Progress
…
Put your mapping directly in the table that has your type-specific statuses. Then those should be lookup columns in your task table (Not sure if you are using a select list or a lookup, lookup is needed here and better in general). Then in your task table, you could have a formula in the Main Status column like:
switchIf(isNotBlank(Video Status), Video Status.Main Status, isNotBlank(Other Type of Status), Other Type of Status.Main Status, "")
Just adding to this, overall I’m not a fan of the approach of having a tasks table with various task type columns, well it can lead to things like having multiple of the type-specific columns having status set, and then what should the main status be? e.g. if Video Status is set to something that results in Todo for the main status, but then Some Other Status is set to something that results in Done, then what should main status be?
I would probably aim for something that resolves this somehow, maybe like:
Task Types Table
Main Statuses Table
Statuses Table
Type Lookup Column to Task Types Table
Name Text Column
Main Status Lookup Column to Main Statuses Table
Tasks Table
Main Status Lookup Column with a formula Status.Main Status
Task Type Lookup Column to Task Types Table
Status Lookup Column to Statuses Table, that has a filter on the Column to only show Statuses for the currently selected type in the Type column
And then on your main task table you could select the Type of work being done, e…g Video Recording, and then set the status there.
This assumes that each task is of a different type. If you need a pipeline of sorts, you could introduce a system to move tasks from one type to another upon completion.
Baseds on your comment and one of the example docs I found in the gallery, I have created a separate reference page with tables that exist to be looked up elsewhere.
This has already improved my setup.
In my main tasks table, I’m getting the correct options now, based on the task type selected.
What I can’t get to work is formula part you mentioned:
I have a Main Status column in my master task list.
Yes actually that’s pretty much what I had in mind, although you allowed for multiple of the task-types to be mapped to a single task-specific status (which is better I think).
I would just rename things a little maybe to make it clearer, but of course this is a matter of taste to some extent:
Statuses Table → Type Specific Statuses Table
Statuses by Type → Task Types
And then in your Tasks Table each task would have a Type column that allows a single Task Type to be selected, with a Filter in the Lookup Options → Item Settings: Statuses by Type = Task Type. And then in your Main Status column you could do a formula Type Specific Status.Main Status Reflection.
