Using Criteria within Lookups

Ask for Help
1

Hello Gliders,
I have a Project Management app with the following google sheets

  1. Projects - sheet has Row ID’s
  2. Tasks - each task has its own task row id. It links back to the projects sheet with the Project ID. So a project can have multiple tasks.
  3. The Tasks sheet also has a column for milestones. Milestones can be Prep, Plant or Harvest.

I want to be able to show a summary of the following dates for each project.

  • Project Start, Project End,
  • Prep Start, Prep End,
  • Plant Start, Plant end

I was able to get the project start date by setting a multiple relation between Project and Tasks in my Project sheet. I then used lookup on this multiple relation to get all dates for the project. Then using a single value column pick the first date from the Date lookup for project start date.

And here’s where I am stuck. I can’t figure out how to get the prep, plant and harvest start dates because I don’t know how to select values from the date lookup using a criteria for e.g. select the first date from the multiple relation which matches the milestone “plant”.

Maybe there is a better way to go about this. Hoping to receive some guidance. Thankyou in advance.

2

How I would do it is create 3 template columns in the Projects table.

  • ‘ProjectID’ - Project
  • ‘ProjectID’ - Prep
  • ‘ProjectID’ - Plant
  • etc.

In the Task table, create a single template that joins the Project ID and Milestone.

  • 'ProjectID - ‘Milestone’

Then create a respective single relations for each template column you created in the project table and link that to the template you created in the Task table.

If your relations are single relations, the you just need a Lookup column or a Single Value column. You don’t need both.

2 Likes