Dataview on deep relationships

[SanDiegoBoy]

What I’m trying to do

• I follow a daily notes first philosophy and use logs to track my progress
• Whenever I work on a project I enter a task of status=“u” (or up). The task text includes a link to the appropriate project [[Project ABC]]
• I am trying to build a Project MOC to show me how many logs in general I have spent trying to work
• For example, Project ABC had 3 logs in which I did some work. On 24-11-01 one log and on 24-11-02 two logs. Total 3. I only care about the total

Things I have tried

I have made a lot of progress and through reviewing posts here and much trial an error been able to put something that gets me very close but can’t figure out the next step.
I can list every task text of the proper status that links to the current project. BTW this took a while understanding data types, lists, objects, functions, etc.
But can’t figure out how to make the count work with length. Looks like something gets messed up during the nested FLATTEN. I have tried length at multiple levels. The length shows incorrect and as multiple entries

TABLE WITHOUT ID
file.link as "Project/Program/Subproject",
dateformat(date(created-date,"yyyy-MM-dd hh:mm"),"yyyy-MM-dd") as creation,
inltaskx.text as tasktext
FROM !"System"
FLATTEN file.inlinks as inl
FLATTEN inl.file.tasks as inltaskx
FLATTEN inltaskx.outlinks as inltaskxo
WHERE (econtains(tags, "type/project") OR econtains(tags, "type/sub-project") )
AND (icontains(state, "active") OR icontains(state, "new")) AND (contains(inltaskxo, file.link)) AND (inltaskx.status="u")
SORT filename DESC
SORT up DESC

Appreciate your patience. I started with obsidian recently and I am not a programmer so proud of getting this far. Obsidian is amazing by the way

[holroy]

Which length? Your query don’t seem to have any length?

What’s the output of your query? And is that correct in listing all the entries you want to count?

[SanDiegoBoy]

• This is the output (I am just displaying one particular project).
• The column tasktext looks good to me and seem to display all the proper ocurrences of tasks on my journal that point to this particular project.
• It is 18 tasks I can see that link to that project.
  
  

Ideally I can do something smart and can collapse all those entries like this
Project/Program creation taskcount
Setup Obsidian Project 2024-10-12 18

The problem is there is no way in which I can use length to accomplish that. It always turns into multiple lines and an incorrect count. I tried a few combinations

PS. @holroy I have scanning these forums and your name stands out for sure due to all your contributions. Thanks

[SanDiegoBoy]

Ok, I was ble to get closer by looking at another one of the posts from @holroy here

Now, I am able to see the sum but lost the creation colum

TABLE WITHOUT ID
file.link as "Project/Program/Subproject",
dateformat(date(created-date,"yyyy-MM-dd hh:mm"),"yyyy-MM-dd") as creation,
length(rows.inltaskx.text) as count
FROM !"System"
FLATTEN file.inlinks as inl
FLATTEN inl.file.tasks as inltaskx
FLATTEN inltaskx.outlinks as inltaskxo
WHERE (econtains(tags, "type/project") OR econtains(tags, "type/sub-project") )
AND (icontains(state, "active") OR icontains(state, "new")) 
AND (contains(inltaskxo, file.link)) AND (inltaskx.status="u")
GROUP by file
SORT filename DESC
SORT up DESC

The result is
Project/ Program/Sub-Project creation count
Setup Obsidian Project - 23
…continues with other projects…

Which is correct but unfortunately I eliminated the creation value which showed before
I tried adding other grouping elements but got an error
Not sure how to combine both
PS I have several other columns like “creation” that are file properties. I just removed from the original post to simplify the request.

[SanDiegoBoy]

Ok, I was able to resolve my question . In case others are interested on something similar.
Lets not do that again just kidding
Very good posts on this forum helped me get on the right mindset on go on the right direction.
PS. I am not sure if I needed flat level 5, maybe lower level would have worked

TABLE WITHOUT ID
file.link as "Project/Program/Subproject",
dateformat(date(created-date,"yyyy-MM-dd hh:mm"),"yyyy-MM-dd") as creation,
length(filter(flat(map(file.inlinks, (t) =>t.file.tasks),5), (u) => u.status="u" and contains(u.outlinks,file.link)))  as logcnt,
FROM !"System"
WHERE (econtains(tags, "type/project") OR econtains(tags, "type/sub-project") )
AND (icontains(state, "active") OR icontains(state, "new"))
SORT filename DESC
SORT up DESC