Multi-select list default value by lookup from other table

[Rickard_Asen]

Hi all,

I am new to the Coda community, but I am already loving this product!
I am not very familiar with coding however, so please forgive me if this is a simple question.

I have three tables:

• Projects -
  Highest hierarchy level, can contain multiple tasks.
  Can be categorized according to work type with a drop-down list via a Lookup table from “Categories”.

• Tasks -
  Lowest hierarchy level.
  Every row can belong to one project.
  Can be categorized according to work type (“Categories”).

• Categories -
  Just a list of different work types.

I want to be able to assign Categories both to Projects and Tasks.
But if I create a Task and assign it to a Project, I would like the Task to automatically get the same Category as the Project it belongs to.
If the Task doesn’t have a Project assigned to it, I would like to manually assign a Category (from the Categories list) instead.

Does anyone have any ideas what formula I should use in the Tasks table / Category list column?
Should I use a Lookup from Table (Categories) and somehow set default value based on if the Project has a category assigned to it?

Awesome product, can’t wait to learn more about it!

Best regards,
Rickard

[Joseph_B]

I put together the following that works, but may not be as clean a solution as you would prefer (i.e. not all in one column). Hopefully someone else has a better approach.

In the AutoAssign Column, I have the following formula:

In the ManualAssign Column I have the following:

And in the CategoryList Column I had this:

Effectively, the AutoAssign column would look to see if a Project has been assigned, and if it has, it automatically grabs the corresponding Category from the Projects table. The Manual Assign Column is setup just like your Category List column in the Projects table; i.e. It is a column formatted to select only the categories from the Categories table.

Finally, the CategoryList column looks first at the AutoAssign column, and grabs the value there, or, if blank, grabs the value from the Manual Assign column. The AutoAssign will always be prioritized over the ManualAssign for this.

An issue may be that you now have three columns where you only want one. You could hide the AutoAssign Column, since you presumably will have little need to interact with it directly. The Manual Assign column though you could either hide and only view when needed, or just keep it there.

I tried a “cleaner” solution of using a select list-formatted column with the formula from AutoAssign set as the default value. This is the column titled “DefaultValueFail” above. It initially seemed to work great all existing entries, but would stay blank on any new entries, which likely makes it unusable. Not sure if that is a bug, or intended. The second issue is that if you ever select a task by accident from the select list options, it overrides the auto assignment, and cannot easily be reverted.

[Rickard_Asen]

Hi @Joseph_B,

Thank you very much for your quick feedback!
Unfortunately I got stuck when I tried to implement it.

First of all, what “Format column as” option did you use for the AutoAssign column? (e.g. Select list; Text; Lookup from table)
I tried all of them, but neither gets the effect you described.

And how did you get that “chain link” symbol to the right of the column name?

---

For clarity, here is an overview of my section as it looks today:

Image

When I format the column as “Lookup from Table”, and set the formula for Default value to your formula (" =Projects.Filter([Project name]=[Belongs to Project]).[Category list].First() "), then I just get the Task Names as selectable options in the dropdown list (but nothing is auto-assigned).

I just recently noticed that there is a difference between clicking the F icon in the top of the column, vs. using the formula options under the “Format column as” menu.
I don’t quite understand how this relationship works.

For example, if I format the column as Select List, then I am able to both add a Formula to the column, and at the same time add a formula for the “Selectable options” or “Default value” under the Select List menu:

I feel like I am missing something obvious here in how the Coda system works, but I’ve tried looking around the Help section but can’t quite get my head around it.

So I think the reason why I cannot reproduce your AutoAssign column is because I have misunderstood something related to the Format Column As or the Formula relationship.

---

As you said, I would prefer to have the “cleaner” solution with just one column, but for now I am happy to just try around different solutions to learn more about how to use Coda.

Regarding the last thing you wrote:

The second issue is that if you ever select a task by accident from the select list options, it overrides the auto assignment, and cannot easily be reverted.

I think this behavior would be preferable for me.
I would like to be able to select different Category options manually on a task-by-task basis.
So the behavior I am looking for is just to auto-assign a “recommended” category to a task that belongs to a project (if that project has a category).
But sometimes certain tasks within a project may correspond better to another category, so then I would like to manually change the category (even if it permanently overrides the recommended default value).

Again, thank you for your help!
If you have any further ideas on how to solve this issue, or know of any learning material that deals with this topic, I would really appreciate it!

Best regards,
Rickard

[Joseph_B]

For the AutoAssign Column, I just used a Text column and typed an “=” into the cell to trigger Coda to interpret what I input as a formula. To put another way, I used the default “Text” format, and typed into the first cell: =Project.Filter([Project name]=[Belongs to Project]).[Category List].First()

Not sure if you hit this issue, but sometimes when jumping between formats and formulas, Coda will not immediately trigger a formula mode, even when the first character input is an =. When that happens, I clear everything, click off of the cell, then click it and input the =.

---

I did not select the chain link symbol; Coda automatically added that. Presumably it is to signal that that column is tied to another table (the Project table in this instance).

---

Yes, I have also noticed that there are “3” formulas with Select list formatted columns.

1. If you use the Select list format, you can input a formula on the column like you could do with a Text formatted column. When I test this formula, it overrides the Select list, even though the column is showing as being “Select list” formatted. i.e. This removes the dropdown menus from the column.

2. The first formula in the Select list options menu lets you use a formula to determine what should be selectable in the dropdown menu.

3. The second formula in the Select list options menu lets you use a formula to set a default value if nothing is selected.

Basically, formula #1 overrides the Select list format and its associated formulas (#2 and #3) as best I can tell. Not sure if this intended.

---

Regarding your manual override comment:

I would like to be able to select different Category options manually on a task-by-task basis.
So the behavior I am looking for is just to auto-assign a “recommended” category to a task that belongs to a project (if that project has a category).

If you get the formulas I posted above up and running, you can swap the order of the CategoryList formula, so it reads:
=IFBlank(ManualAssign,AutoAssign)
This will make the manually selected column override the auto-selected column.

Edit: Typo and Grammar