Counting trip expenses

Maarrk · April 30, 2024, 10:10pm

Feels a bit silly, but on the other hand Excel or programming are overkill for the task. And this way it’s editable on mobile and kept together with other notes about the trip

The page can have one or more tables, and then I pass a query to a custom function:

```template
**Total cost: {{sum("cost", {table where page = @page.name})}}€**
transport: {{sum("cost", {table where page = @page.name and tags = 'transport'})}}€
```

|Description|Cost|
|-----------|----|
|train ticket #transport|6.25|
|supermarket|15.12|

The sum function is defined in this Space Script

silverbullet.registerFunction("sum", (key, items) => {
  return items
    .map(i => parseFloat(i[key]))
    .reduce((acc, n) => acc + (isNaN(n) ? 0 : n), 0)
    .toFixed(2);
});

zef · May 1, 2024, 6:24am

Ah nice use case for the table indexing. Bye bye spreadsheets!

paletochen · May 1, 2024, 10:49am

I love this. Thanks for sharing!
Perfect timing as I am reading this feature while being on vacation and sharing costs.

I have been using for 3 trips so far SilverBullet for cost tracking, though through a simple bullet list. No automatic calculations.

This will save me time.
It would be amazing to enhance it to provide cost-sharing, as I normally create the cost tracking during vacation because I need to share the expenses with other people and at the end of the trip we make numbers.

[UPDATE] I guess I can use tags for it for now

paletochen · May 1, 2024, 1:18pm

Well, thanks again for doing this.
I just added some more formulas and now I have what I need.

Example with splitting expenses between 2 families (1 of 3 people and the other of 2, so we should pay different amounts at the end of the trip)

molnarti · September 29, 2024, 6:25pm

the script gave me wrong sums, then after some troubleshooting i realized its not counting lines with empty colums.

as you can see in the image, only the items where link is present are summed. any idea how to fix this?

Maarrk · September 30, 2024, 7:58pm

I’m afraid that this is not my fault, but a bug in how SilverBullet handles empty cells, that seems to be previously unknown. After a bit of my troubleshooting I described it in the general case on GitHub:

github.com/silverbulletmd/silverbullet

Table rows are indexed incorrectly when some cells are empty

opened 07:48PM - 30 Sep 24 UTC

Maarrk

The columns in a given row are assigned in order of their definition, skipping e…mpty columns. Minimal example: ~~~ |Description|Foo|Bar| |-----------|---|---| |Row 1 |A1 |B1 | |Row 2 | |B2 | ```query table where page = @page.name ``` ~~~ Produces the following output (notice that second row has "B2" assigned to "foo"): |Description|Foo|Bar| |-----------|---|---| |Row 1 |A1 |B1 | |Row 2 | |B2 | |tableref|ref|tag|tags|page|pos|description|foo|bar| |--|--|--|--|--|--|--|--|--| |Indexing issue@1|Indexing issue@45|table||Indexing issue|45|Row 1|A1|B1| |Indexing issue@1|Indexing issue@67|table||Indexing issue|67|Row 2|B2||

To work around the bug with empty cells in your specific table, you can either:

put any text in each cell of the “Link” column, like N/A or TODO or just a . (dot)
reorder the columns so the “Price” column isn’t after any column that may be missing items; in your example that order would be “Item”, “Price”, “Link”