I know the question of filtered / nested select lists has been asked more than once, so please bare with me.
We are setting up an internal time tracking system for our office staff where a series of job costing codes (Workflow IDs) will be used to track time against projects. Here is an truncated list:
Workflow ID
Task Description
00.00
General Administration and Overhead
00.01
General Admin Costs
00.02
Overhead Cost (Office, Insurance, Etc.)
00.03
Misc. Project Costs
00.04
PTO
00.05
Holiday
01.00
Business Development
01.01
General Business Development
01.02
Assist with Pre-MFG Agreement Preparation
02.00
Pre-Development
02.01
General Pre-Development
02.02
Assist with Concept/Feasibility/Design Criteria
03.00
Contract Documents
03.01
General Contact Review
03.02
Contract Review
04.00
Plan Development
04.01
General Plan Development
04.02
Schematic Design Development
04.03
Design Drawing Development
04.04
Design Lock Documentation and Execution
04.05
Detailed Pricing Revisions
04.06
Construction Drawings
As you can see the bold items are groupings for the sub-items. Intuitively I would set this up as two tables with the Workflow ID being the display column for each then effectively joining them.
Workflow ID
Task Description
00.00
General Administration and Overhead
01.00
Business Development
02.00
Pre-Development
03.00
Contract Documents
04.00
Plan Development
Workflow ID
Task Description
00.01
General Admin Costs
00.02
Overhead Cost (Office, Insurance, Etc.)
00.03
Misc. Project Costs
00.04
PTO
00.05
Holiday
01.01
General Business Development
01.02
Assist with Pre-MFG Agreement Preparation
02.01
General Pre-Development
02.02
Assist with Concept/Feasibility/Design Criteria
03.01
General Contact Review
03.02
Contract Review
04.01
General Plan Development
04.02
Schematic Design Development
04.03
Design Drawing Development
04.04
Design Lock Documentation and Execution
04.05
Detailed Pricing Revisions
04.06
Construction Drawings
Herein lies my question: I always think about the display column in terms of a relational database (and this being the unique value—primary key if you will—that is being described by all of the other columns in the row). Is this flawed in terms of how Coda thinks of them?
In this particular use case it is desirable for the employees to be able to first select the main descriptor and then the secondary descriptor upon which the Workflow ID will automatically populate. This being opposite scenario of what I outlined above.
User would enter: Plan Development then Construction Drawings and 04.06 would automatically populate.
Of note, some of the sub-descriptions have been intentionally re-named to make them unique. For example, it is possible to have something like “drawing review” under multiple main descriptions, but were renamed purely to avoid lookup errors in the spreadsheet that we currently utilize. This was a workaround and isn’t necessarily desirable in the Coda version.
@Scott_Collier-Weir I would love to hear your thoughts on this, but by all means, input from any and all is welcomed!
would this work out for you? The best way to do this in coda is to use two different tables and views with grouping. When adding a new sub-workflow in the view of sub-workflows, the next ID becomes chosen automatically.
This reminds me that you have provide a response to a question that I need to have a look at still…! This looks good.
Hi Nate
Some additional comments on your specific questions:
Coda’s display column - It is not necessarily a unique value. If you want the entries in the column to be unique, you will need to enforce that your self. It is the “access point” to a row. E.g. when you lookup to a table, the value that will be shown in the dropdown is this value. Using this value, you can access, using dot notation, all off the columns in the lookup source table.
In the setup as Quentin set up above, the descriptions do not need to be unique. But it then raises the question of whether you want a strictly hierarchical structure, where a sub-workflow only links to a specific (one and one only) workflow, or whether you want a matrix relationship where the link between workflow and subworkflow can be established dynamically.
From your numbering convention it seems as if you want a fixed structure, in which case Quentin’s example will work. If you want a matrix type solution, you will need to modify logic slightly.
Thanks for reaching out @Mohamed_Elrefae ! Were you looking for some help from @Piet_Strydom ? If so we will need Edit access to your doc that you’ve linked. It looks like your doc is currently in View Only mode.
check out your Doc, I fixed the problems. The “Choose a Related Task”, or “Select Child” column in your case, doesn’t need a formula to define it’s value, as you want to be able to select an item.
However, only those child tasks with the same master as the selected one in “Select Master” should be selectable. Therefore, you needed a filter formula instead. You can find it in the “Select Child” column menu → Lookup options → Option settings → filter → formula
@M_Schneider , sorry for the delayed response – and thanks for the prompt, I needed it. This solution ended up working great for our needs as the supervisor of that department wanted the ability to add new categories/tasks and this method helped streamline that. Thanks for the valuable input!
