When an Action is part of a Project, I can figure out via Projects to what Life Area the Action belongs.
Sometimes Actions stand alone: then they don’t belong to any Project. In those cases I do want to be able to link the Action to a Life Area manually, so I added a lookup from table Life Areas to the Actions table as well.
Now here is the question: How do I make sure that when a Project is linked to the Action, the Life Area column is automatically updated by using the Life Area belonging to the used project, and how do I make sure that when there is no Project linked to the Action the user can manually update the Life Area linked to the Action?
In pseudo code, this formula in the Life Area lookup attribute in the Actions table:
If(thisRow.Project.IsBlank(),
— what formula to use here so that manual lookup is possible?—,
thisRow.Project.[Life Area])
That allows the user to set those values manual as well. They don’t automatically update when the Project linked to the Action is changed, nor when the Area linked to a Project is changed.
@Koen73 Do I understand it correctly that you want to be able to link an Action to either a Project or a Life Area directly?
I just tried and this worked: ListCombine(Projects, [Life Areas]) — a selection list combined out of projects and life areas as a parent choice for an action.
I also added two columns to infer project (if exists) and life area out of chosen value.
Thank you very much for your kind support .
To be honest it would have never come in mind to use the “ListCombine” function, what opens the door for the rest of the formulas.
At first I wanted to recommend reorganizing data structure, so that projects and life areas were on the same hierarchy level (i.e. in the same table).
Then I thought of introducing a separate column for calculating the “real” Life Area depending on which input is filled (Project or Life Area).
But then I just decided to try combine those lists and calculate Project and Life Area out of that with formulas. And it worked. Long live Coda. And now I have another trick up my sleeve to post on my future blog
I updated the formulas btw; should be more performant and more type-safe now.