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!
Hi @Nate_Eikelberg ,
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.
Hi @Nate_Eikelberg
Find here a quick example and some advice I personnaly use.
As you said, I created two tables
Your task beeing linked to category with lookups column type
Advice : I usually create a “Summary column”, concatenating information I want to display in a future select list
Then, in the timesheet your team will use to enter rows, first create a select list / lookup to category
Then, create a “Choose a Related Tasks” column that’s a lookup to Tasks, and set custom filter to make category matches current category
And this is what we got. Of course the “Summary Tips” could be applied to category table as well to improve display when using select list
Cheers
Edit : Sorry @M_Schneider , we posted in the same time 
Hi Quentin,
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.
Regards
Piet
please help
can you add the eqution to this doc because i have proplem
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.
thanks for your attention
please find edit link
Hi Mohamed, can you please elaborate on what it is that you are looking for?
Regards
Piet
this formula not working with me
Hi @Mohamed_Elrefae ,
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!
Hi @Piet_Strydom yes, @M_Schneider’s solution worked quite well for me. Thanks for the additional info.
