Lookup from table: automatic or manual update

Hi,
I have a small GTD database with 3 tables:

  • Life Areas
  • Projects (with lookup from table Life Areas)
  • Actions (with lookup from table Projects)

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])

Any help is appreciated.

Best, Koen

Dear Koen,

Welcome to the community :handshake:

Please check the doc, if this solves your question?

Thanks Jean_Pierre! So kind of you to help!

The Area field for Projects 1, 2, 3 in the Actions table is empty in your example. I tried changing the formula in “Area” to:

if(IsBlank(thisRow.Project),[Life Areas].Name,)
thisRow.Project.LifeArea)

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.

Any idea how to do that?

@Paul_Danyliuk are you able to give a helping hand? It’s something I want to learn too :handshake:

@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.

See:

1 Like

Dear @Paul_Danyliuk,

Thank you very much for your kind support :clap:.
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.

1 Like

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 :slight_smile:

I updated the formulas btw; should be more performant and more type-safe now.

2 Likes

Thank you @Paul_Danyliuk and @Jean_Pierre_Traets !!
I really appreciate your help!