How best to store and dataview query project teams and team members?

NovasTaylor · March 6, 2023, 2:18pm

Hello everyone,

Related post on reddit with no replies to date.

I’m looking for an efficient way to manage projects and teams. I have Person notes a Rolodex folder where each person’s project Teams and Roles are assigned using YAML front matter. With this data I want to create :

A dataview query in each Person Note listing the teams that person is a member of and their roles in each team. I have this working, see below.
A dataview query in each Project Note listing the team members for roles within that project. The Project Note for “Project A” lists the team members from “Team A” and role of each person in the project (and only that project). This is not working for me.

Things I have tried

YAML front matter for Person Notes contain Team.Name and Team.Role values:

Person 1.md

NoteType: person
Team: [{Name: Team A, Role: Scrum Master},{Name: Team B, Role: Developer} ]`
---

Person 2.md

NoteType: person
Team: [{Name: Team A, Role: Stakeholder},{Name: Team B, Role: Product Owner} ]
---

The following dataview query shows all the teams a person belongs to and their role. This query runs correctly within each Person note:

Teams

```dataview
TABLE without ID link(Team.Name) as “Team”,
Team.Role as “Role”
FROM “”
WHERE file.name = this.file.name
SORT Team ASC
```

In the Project A note I want to list the people who are a member of that project based on the Team name and role for that project. If I use the following WHERE statement, the Project will list the person’s roles in ALL teams, not just Team A, which is not what I want to show in the Team A Project note:

WHERE contains(Team.Name, “Team A”)

The following query for the Team A Project currently shows no results.

Project A.md

Project A Team Members and Roles

```dataview
TABLE WITHOUT ID file.link AS “Person”, Team.Name as Name, Team.Role as Role
FROM “”
WHERE Team.Name = “Team A”
SORT Name ASC
```

My questions

What am I doing wrong in the Project Query or YAML front matter?
Is there a better way to assign, manage, and query Team membership and roles for people and projects?

Thanks for your help!

justdoitcc · March 7, 2023, 6:41am

Look at the DQL01.
Look at the DQL10 and DQL13.

Topic

Summary

How to use the FLATTEN operator via DQL when the note structure is like case_YAML_Y_AoH? (DQL01, DQL10, DQL13)

Input

Summary

the current notes

Location: “100_Project/02_dataview/Q27_ProjectPerson/Q27_test_data”

folder: 10_Projects

filename : Q27_Project A

```md
---
NoteType: "project"
TeamName: "Team A"
ProjectName: "Project A"
---

### Q27_DQL10 (OR Q27_DQL01)


```

folder: 13_Teams

filename : Q27_Team A

````md
---
NoteType: "team"
TeamName: "Team A"
ProjectName: "Project A"
---

### Q27_DQL13


````

dictionary files

Location: “100_Project/02_dataview/Q27_ProjectPerson/Q27_test_data/20_Persons”

folder: 03

filename : Q27_Person01

```yaml
---
Date: 1957-03-01
NoteType: person
Team: [{Name: Team A, Role: Scrum Master},{Name: Team B, Role: Developer} ]
---
```

folder: 04

filename : Q27_Person02

```yaml
---
Date: 1957-04-01
NoteType: person
Team: [{Name: Team A, Role: Stakeholder},{Name: Team B, Role: Product Owner} ]
---
```

folder: 05_excluded

filename : Q27_Person03

```yaml
---
Date: 1957-05-01
NoteType: person
Team: [{Name: Team C, Role: Scrum Master},{Name: Team D, Role: Developer} ]
---
```

DQL01_transform_Team_into_OneTeam_via_FLATTEN_and_TABLE

Summary

Main DQL

Code Name	Data type	Group By	Purposes	Remark
DQL01 _transform_Team _into_OneTeam _via_FLATTEN _and_TABLE	(Person notes) `Team`: a list of JavaScript Objects `OneTeam`: a JavaScript Object	no	1.To filter by `NoteType` 2.To break up a list `Team` into each individual element `OneTeam` 3.To filter by `OneTeam.Name` 4.To sort by `OneTeam.Name` in ascending order 5.To display the result as a table	Note: Require the note structure like case_YAML_Y_AoH

Notes

Summary

Q1: What does the following DQL statement mean?

Summary_Q1

Original Example: Q1 (To be explained)

```dataview

FLATTEN Team AS OneTeam

```

A1_11:

```dataview

// To break up a list `Team` into each individual element `OneTeam`
// PS.`Team`: a list of JavaScript Objects, such as `[{Name: Team A, Role: Scrum Master},{Name: Team B, Role: Developer} ]`
// PS.`OneTeam`: a JavaScript Object, such as `{Name: Team A, Role: Scrum Master}` or `{Name: Team B, Role: Developer}`
// PS.`OneTeam.Name`: a string, such as "Team A" or "Team B"
FLATTEN Team AS OneTeam

```

Code DQL01_transform_Team_into_OneTeam_via_FLATTEN_and_TABLE

NOTE: Query Project A Team Members and Roles

Summary_code

title: DQL01_transform_Team_into_OneTeam_via_FLATTEN_and_TABLE => 0.Require the note structure like case_YAML_Y_AoH 1.To filter by `NoteType` 2.To break up a list `Team` into each individual element `OneTeam` 3.To filter by `OneTeam.Name` 4.To sort by `OneTeam.Name` in ascending order 5.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      file.link AS "Person",
      OneTeam.Name AS "Name",
      OneTeam.Role AS "Role"
FROM "100_Project/02_dataview/Q27_ProjectPerson/Q27_test_data/20_Persons"
WHERE NoteType = "person"


FLATTEN Team AS OneTeam


WHERE OneTeam.Name = "Team A"
SORT OneTeam.Name ASC
```

Screenshots(DQL01):

DQL10_get_Team_membership_and_roles_using_this_Project_YAML_and_TABLE

Summary

Main DQL

Code Name	Data type	Group By	Purposes	Remark
DQL10 _get_Team_membership _and_roles _using_this_Project_YAML _and_TABLE	(Project notes) `this.TeamName`: a string 1.“Team A” 2.“Team B” 3.“Team C” 4.“Team D” (Person notes) `Team`: a list of JavaScript Objects `OneTeam`: a JavaScript Object	no	1.To filter by `NoteType` 2.To break up a list `Team` into each individual element `OneTeam` 3.To filter by `OneTeam.Name` 4.To sort by `OneTeam.Name` in ascending order 5.To display the result as a table	Note: 1.Require the Person note structure like case_YAML_Y_AoH 2.Require the field `TeamName` in the current Project file

Code DQL10_get_Team_membership_and_roles_using_this_Project_YAML_and_TABLE

NOTE: Query Team membership and roles for projects

Summary_code

title: DQL10_get_Team_membership_and_roles_using_this_Project_YAML_and_TABLE => 0.Require the Person note structure like case_YAML_Y_AoH, and Require the field `TeamName` in the current Project file 1.To filter by `NoteType` 2.To break up a list `Team` into each individual element `OneTeam` 3.To filter by `OneTeam.Name` 4.To sort by `OneTeam.Name` in ascending order 5.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      file.link AS "Person",
      OneTeam.Name AS "Name",
      OneTeam.Role AS "Role"
FROM "100_Project/02_dataview/Q27_ProjectPerson/Q27_test_data/20_Persons"
WHERE NoteType = "person"


FLATTEN Team AS OneTeam


WHERE OneTeam.Name = this.TeamName
SORT OneTeam.Name ASC
```

Screenshots(DQL10):

filename : Q27_Project A

```md
---
NoteType: "project"
TeamName: "Team A"
ProjectName: "Project A"
---

### Q27_DQL10 (OR Q27_DQL01)


```

DQL13_get_Team_membership_and_roles_using_this_Team_YAML_and_TABLE

Summary

Main DQL

Code Name	Data type	Group By	Purposes	Remark
DQL13 _get_Team_membership _and_roles _using_this_Team_YAML _and_TABLE	(Team notes) `this.TeamName`: a string 1.“Team A” 2.“Team B” 3.“Team C” 4.“Team D” (Person notes) `Team`: a list of JavaScript Objects `OneTeam`: a JavaScript Object	no	1.To filter by `NoteType` 2.To break up a list `Team` into each individual element `OneTeam` 3.To filter by `OneTeam.Name` 4.To sort by `OneTeam.Name` in ascending order 5.To display the result as a table	Note: 1.Require the Person note structure like case_YAML_Y_AoH 2.Require the field `TeamName` in the current Team file

Code DQL13_get_Team_membership_and_roles_using_this_Team_YAML_and_TABLE

NOTE: Query Team membership and roles for people

Summary_code

title: DQL13_get_Team_membership_and_roles_using_this_Team_YAML_and_TABLE => 0.Require the Person note structure like case_YAML_Y_AoH, and Require the field `TeamName` in the current Team file 1.To filter by `NoteType` 2.To break up a list `Team` into each individual element `OneTeam` 3.To filter by `OneTeam.Name` 4.To sort by `OneTeam.Name` in ascending order 5.To display the result as a table
collapse: close
icon: 
color: 
```dataview
TABLE WITHOUT ID 
      link(OneTeam.Name) AS "Team",
      OneTeam.Role AS "Role",
      file.link AS "Person"
FROM "100_Project/02_dataview/Q27_ProjectPerson/Q27_test_data/20_Persons"
WHERE NoteType = "person"


FLATTEN Team AS OneTeam


WHERE OneTeam.Name = this.TeamName
SORT OneTeam ASC
```

Screenshots(DQL13):

filename : Q27_Team A

````md
---
NoteType: "team"
TeamName: "Team A"
ProjectName: "Project A"
---

### Q27_DQL13


````

Reference

Summary

Q17_Purchases: case_YAML_Y-YYY

Q17_Purchases: Solutions

system · March 14, 2023, 6:41am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.