I’m new to Obsidian and I enjoy finding out how versatile it is. My primary goal is to better organize my life with the help of Obsidian.
The following use-case will be useful in many ways for other parts of my daily life if I get it to work.
Say I have two motor vehicles and I’m concerned about their maintenance history. For the purposes of this example say I have two “suppliers”. One is a body repair shop and the other one is a tire repairing business.
What I did is to create a note for vehicle 1, a note for vehicle 2, one for the body repair shop and one for the tire repairing business.
I tried various solutions and I ended with this.
In the “suppliers” notes I enter the history of repairs done to each vehicle. I created a table in each note. Each row is prefixed with an inline key, for example [car1:: and the closing ] is at the end of the row. Therefore when I open car 1’s note I can use dataview to show the car1-related rows in the respective “supplier” note.
This is satisfactory as far as it goes. With two dataview queries, that is one per supplier, I can see a list of repairs done in each vehicle’s note. However, I wish there was a way to find out the total cost of service or repairs to each vehicle over time and show it in that vehicle’s note.
I experimented with DB Folders, but it seems to be too complicated to set it up. I should add that I try to keep as much as possible a flat directory structure in my notes and use links and tags.
Thank you for the suggestions. As a new user I would prefer a dataview query, because I would understand it better, but if a dataviewjs query would be better, then I wouldn’t mind it anyway.
I don’t know if I can add some data in a useful fashion, but I’ll try.
My table in one of the suppliers notes is:
car | Date | Work done | Cost | Remarks |
| ------------------------ | ---------------------- | ----------- | ----:|:-------------------------- |
| [car1:: [[Numb_plate1]] | [[2023-01-10 Tuesday]] | tire repair | 20 | Front tire driver's side ] |
| [car2:: [[Numb_plate2]] | [[2023-02-03 Friday]] | tire change | 300 | Two front tires ] |
| | | | | |
The related dataview in the vehicle note (a note titled Numb_plate1) is:
LIST car1 from "Notes/Tire repair shop"
In note Numb_plate1 I would also have a dataview query concerning the body repair shop note, for example:
LIST car1 from "Notes/Body repair shop"
Every time I add a new row in a supplier’s table (either the Tile repair shop or the Body repair shop note) with the appropriate inline field as in the example above, the vehicle note dataview query in Numb_plate1 note would be updated accordingly with a new line.
What I don’t have is a way to sum the cost of such repairs etc.
(Apologies if the text isn’t formatted right in this note. I struggled a bit to fix it)
That was some peculiar markup, which I’m kind of surprised actually works (somewhat), but let’s work with it. Sadly, I’m in a bit of hurry, so this is what I whipped up just now. I think this can be made nicer, and possibly a little more generalised so that you’d also be able to gather the total cost of your car ownerships, across cars and suppliers.
But we’ve got start somewhere, and here’s what I’ve got so far:
```dataview
LIST "" + length(rows) + " repairs for a cost of: " + cost
FROM "Notes/Tire repair shop"
FLATTEN car1 as detailLine
FLATTEN array(split(detailLine, "\s*\|\s*")) as details
WHERE car1
GROUP BY details[0]
FLATTEN sum(map(rows, (r) => number(r.details[3]))) as cost
```
So what’s happening here (well, a lot :-d) :
We first “split” (or FLATTEN as it called in here) the multiple lines of repair into each separate detailLine
Then we both split() the detailLine into its components (or columns), before we gather them in an array, details, for later reference. Now we’ve got each repair its own details array, holding the link, date, repair type and the cost
We use the WHERE car1 to focus on just this car, and this one of the parts I’m not satisfied with, it would be nicer to use car:: , and pick the car by it’s plate, but I’m struggling with that in the time frame I had
After doing all that work to separate the different repairs into the details, we GROUP by details[0] them again, by the plate
And now we’re ready to do the actual work, because now we’ve got a rows array holding the details array, where we then can map out the cost of each repair, and sum them together in the final line. It’s hairy, but it does the job…
All this to get this output (from my test data):
I threw in the number of repairs into it, so it wouldn’t just read: Car1: 270…
The way that I had the notes connected I was entering the same data twice. I used the query given as a solution for the total cost calculation and changed it by trial and error to this:
TABLE details[1] as Date, details[2] as "Work done", details[3] as Cost, details[4] as Remarks
FROM "Notes/Tire repair shop" or "Notes/Body repair shop"
FLATTEN car1 as detailline
FLATTEN array(split(detailline, "\s*\|\s*")) as details
I don’t know if there is a fault in it, but it gives me a list (history) of all work done in my vehicle 1 note.
When using ordinary DQL queries which these are you need to make a choice:
use group by, and get the sum, but no details on each entry, or
no grouping, and no sum, but all the v details for each entry
If you switch to dataviewjs (or a hybrid version), you can get both the details and manually add an extra line for the totals. Can’t write up an example tonight, maybe tomorrow.
