Math within list items

Questions
1

Is there a way to do math inside of an item in a list?

I’ve got a thing Journal.
Entries in Journal contain a reference to an entry in thing Food and a number to multiply by.
So the journal would be read “on this date I ate 500g of this Food”
The Food thing has a long list of nutrients.

At the moment what I’m trying to do is add up how many of each nutrient someone ate over a time period. So taking a list of Journals.

Journal

  • food
  • times

Food

  • name
  • nutrient 1
  • nutrient 2
  • nutrient 3

The problem is that I want to multiply a field under Food by a field under Journal and then add the result for that nutrient from each Journal.

list = search for Journal

  • journal 1
  • journal 2
  • journal 3

total nutrient 1 = (Journal-1-nutrient-1 * journal-1-times) + (journal-2-nutrient-1 * journal-2-times) + (journal-3-nutrient-1 * journal-3-times)
total nutrient 2 = (Journal-1-nutrient-2 * journal-1-times) + (journal-2-nutrient-2 * journal-2-times) + (journal-3-nutrient-2 * journal-3-times)
total nutrient 3 = (Journal-1-nutrient-3 * journal-1-times) + (journal-2-nutrient-3 * journal-2-times) + (journal-3-nutrient-3 * journal-3-times)

Can I build an expression to get each of these totals off of one search that returns the list of journals?

2

How about this approach …

List Item Expression for nutrient 1:
source data list: search Journal
Expression: field(“food”,“nutrient 1”) * field(“times”)

Then :sum the resulting list.

Repeat for the other two nutrients.

1 Like
3

There aren’t just 3 nutrients. The list of nutrients is arbitrarily long and could change in the future.

I’m investigating Calculate Formula: Sum Product. That’s listed on its own at the top level of the expression builder, it doesn’t show up when you’re working with lists.

4

So what does your data structure look like, for the Food table?

5

It shows up when you can enter a single number result, which is what it returns.

6

So I got sumproduct to work for one nutrient

image.jpg983×466 92.7 KB

Now I’m trying to build a repeating group for all of the nutrients rather than manually build an element for each one.
I’ve got the same sumproduct going on in each cell, but I can’t figure out how to reference that cell’s nutrient to filter the journal>food>nutrient list

image.jpg978×437 87.4 KB

I think the problem is with how I setup the data, actually. At least for this purpose. I don’t think there’s a way to filter to the correct amount because the data is stored flat like this
Food

  • nutrient_1 (link to entry in thing nutrient)
  • amount_1 (number)
    They aren’t actually associated with each other. I think maybe if I added another thing to create an associative array kind of function it would work. Maybe like this

Array

  • nutrient (thing nutrient)
  • amount (number)

Food

  • name
  • list of arrays