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
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
Ok, I was ble to get closer by looking at another one of the posts from @holroyhere
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.
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