Automating Timesheet Help - Query embedded fields and sum hours across all files in current folder

Help
2

A general tip regarding queries is not to build data structure where the key varies. With this I mean doing stuff like [projectcode1:: ... ] and [projectcode2:: ... ], but rather use [project:: code1] and [project:: code2]. This makes your query a lot easier and more flexible and maintainable.

Along this with, if you’re going to vary what you sum across, like only one project or all projects, you need to use a given property to record the hours. Using both of these tips, I would change the markup for your work session to become of this format:

- (project:: code1) Completed development task [hours::3.5]

Using ( ... ) if you want to hide the key, and [ ... ] if you want the key to show by default. The latter variant also allows for more customisation using CSS, as you then can target a particular set of inline fields, (like hours if you wanted to add h after the value).

Given this kind of syntax, queries to answer the two first issues of yours:

## 1) All unique project codes
```dataview
LIST
FROM "ForumStuff/f71/f71232"
FLATTEN file.lists as item
WHERE item.project
GROUP BY item.project
```

## 2) Text and sum hours worked on each project
```dataview
TABLE rows.cleanText as Tasks, sum(rows.item.hours) as Hours
FROM "ForumStuff/f71/f71232"
FLATTEN file.lists as item
FLATTEN regexreplace(item.text,
  "[\[\(][^\[\)]*[\]\)]", "") as cleanText
WHERE item.project
GROUP BY item.project as "Project code"
```

Regarding your third part it’s not as easy to do since you’re doing a double sum. In most cases if I were to do something like that, I would reuse the partial sum query within dataviewjs and calculate the last sum there, and add it to the table “manually”.

In the thread below we do just that, and it also refers to another thread where I explored these options even further. So check that/those threads and see if you’re able to do your total hour sum based on those.

1 Like