Bases - Filtering on multiple criteria with semi-structured linked notes

What I’m trying to do

As a BIG project, I am trying to create a generic template for project management inside Obsidian. Based essentially on the PMI content, I design it with some personnal choices nonetheless. That’s for the global context.

More specifically, I am trying to manage :

  • Individuals (as Notes), with multiple properties (mostly personal data)
  • Project Team Assignments (as Notes), with few properties : TeamMemberAssigned (as text but having a link to an Individual), Assignment (as text, but could evolve in the future to a list, and maybe with links to structured Notes describing the actuel Role assigned), AssignmentStart and AssignmentEnd (as date & time)

Past that (which is fine), what I want to get is the following :

  • A base with multiple views, allowing me to see : All assignments (past and present), Only current Assignments, Only past Assignments, etc.

Things that work

The base is already created and works fine for the basics.

As the “All Team Members”, I have a table with one line per Individual and a formula-based property to get “Past & Current Assignments Count”, with the following formula :
file.backlinks.filter(value.asFile().folder == "10 Project Management Team/10.01 Project Management Team Assignments").length

Note that I could remove the “.length” at the end to have the proper Notes title which contain the Assignment (by convention)

Things I have tried

What I cannot achieve is to develop a View for only Current Assignments.
My first impulse was to add another criteria to the filter in the formula, and to keep only files whose AssignementEnd is not before today.
But it seems that I don’t know how to reach (in the meaning of writing) the property AssignmentEnd in the file filtered through the file.backlinks (so not targetting an Inidividual, but an Assignment)

If anyone could be kind enough to explain :

  • IF it is possible
  • and if yes, HOW is it possible
    … it would be awesome :slight_smile:

Thanks !

To access the end dates in your people view (the view that contains the assignment count that you showed), display this formula in a column to get started:

file.backlinks.filter(value.asFile().folder == "10 Project Management Team/10.01 Project Management Team Assignments").map(value.asFile().properties["AssignmentEnd"])

If you need more info to build your filter, or if I’ve misinterpreted the view or base whence you want to access the end dates, then can you explain and paste an example of the relevant frontmatter from a person note and an assignment note as well as your current base code?

Hey dawni !

Thank you for your reply.
It gives me clues on how to tap in the properties of a linked Note. I did not know how to articulate the “.map” thing. So, kudos to you !

However, I obiously missed to tell you about one thing : there are multiple assignments (past, current or both) on one single individual :confused:
In my example (all data shown in my screenshots are from my own test set), there is one Project Manager for a period of time, but then, this person has to stop to take another role (Artistic Director), and therefore, the other person who was previously Business Analys becomes Project Manager. You understand here that for just these two Individuals, they will have both two Assignments (one past, one current).

Trying to fix the previous view

By applying your method, I could produce the following result :

It seems to work, but in a weird manner, as it has to deal with collections. Most people have different roles on the third column (first row, “Business Analyst” and “Project Manager”, second row, “Artistic Director” and “Project Manager”, etc.). When present, multiple “AssignmentEnd” properties could be shown on the second column (as the fourth row)

Unless there is a smooth way to deal with these collections, I start to think that I do not go the best way with this for now.

Introducing a new view

Before even reading your reply, I went in a different direction.
I stuck on using the Assignment Notes as the basis for filtering, waiting for the grouping option upcoming soon, to gather the different Assignments for the same Individual.

The turning point was for me to discover about the advanced filter in order to use the “today()” variable ^^

Here is the result (with the filters used) :

It became even very simple to get the view for only Past Assignments that way :

I am already quite satisfied with the two grids above, and I am sure it will be even better with some grouping (on Individuals) soon.

Remaining issue

I intended to make another post and maybe I will, for another issue I still have.

The views shown above are useful when trying to have a global understanding at once. But I would like to present people involved in the project with their own perspective, centered on them, obviously.

As I would like to deal with Risks, Issues, Changes, Activities, Communications, etc., an Individual should get only elements connected to him/her, at once, inside his/her Individual Note.

Note that I could build specific Views in the different Bases I use, but it would have to be built manually for each individual.

But I’d rather do as I used to “back in the days”, with the Dataview plugin.
Here would be an example of an Individual Note content :

---
Type: Team Member
FirstName: Asterix
LastName: 
Gender: Male
Address: Third hut on the right
ZipCode: 
Town: The Village
Region: Brittany
Country: Gaul
Timezone: UTC+1
Email: 
Mobile Phone: 
LandlinePhone:
---
# Assignments
## Current Assignments
dataview
TABLE Assignment, AssignmentStart AS "Start", AssignmentEnd AS "End"
FROM "10 Project Management Team/10.01 Project Management Team Assignments"
WHERE contains(file.outlinks, this.file.link)
 AND !AssignmentEnd.value < today
 ANS AssigmentStart.value < today
SORT AssignmentEnd ASC

## All Assignments (Past and Current)
dataview
TABLE Assignment, AssignmentStart AS "Start", AssignmentEnd AS "End"
FROM "10 Project Management Team/10.01 Project Management Team Assignments"
WHERE contains(file.outlinks, this.file.link)
SORT End ASC

# Risks
## Risks owned
dataview
TABLE RiskNature AS "Nature", ProbableCause AS "Probable Cause", RiskOutcome AS "Outcome"
FROM "06 Risks"
WHERE contains(file.outlinks, this.file.link)
AND Type = "Risk"
SORT End ASC

## Risk assessments made
dataview
TABLE Risk AS "Risk Assessed", AssessmentDate AS "Assessment made", RiskProbability as "Probability", RiskImpact AS "Impact"
FROM "06 Risks"
WHERE contains(RiskAssessor, this.file.link)
AND Type = "Risk Assessment"
SORT AssessmentDate ASC

# Issues raised
## Discovered by this person
dataview
TABLE IssueDef AS "Definition", IssueDomain as "Domain", IssueStatus AS "Status", IssuePriority AS "Priority", OpeningDate AS "Open on", Responsible
FROM "05 Issues"
WHERE contains(DiscoveredBy, this.file.link)
AND Type = "Issue"
SORT End ASC

## This person is in charge of
dataview
TABLE IssueDef AS "Definition", IssueDomain as "Domain", IssueStatus AS "Status", IssuePriority AS "Priority", OpeningDate AS "Open on", ClosingDate AS "Close on", DiscoveredBy AS "Discovered BY"
FROM "05 Issues"
WHERE contains(Responsible, this.file.link)
AND Type = "Issue"
SORT End ASC

Everyting is working fine, except for the first Dataview query :frowning:
Even after reading previous topics on this forum regarding dates comparison, I still do not understand how to to it.

Here is the result for the Individual Note body :

If I cannot find a way, maybe I will do it with Bases and Views, but I find it very elegant with Dataview, thanks to the “this.file.link” that makes it generic, so I can include the code block in the Template for an Individual, and upon its creation, this new Individual already have everything set up correctly.

Anyway, thanks for the help, and if anybody could help my with the dates comparison, would be great :slight_smile: