Forum Academy Marketplace Showcase Pricing Features

Math within list items

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?

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.

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.

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

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

So I got sumproduct to work for one nutrient

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

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