Sum one column based on checkbox in another column

[Guyinpv]

I have a table ‘Questions’ like this:

Item	Importance	Complete
Row1	P1	True
Row2	P2	True
Row3	P3	False

I want to have a summary outside the table that shows the sum of Importance based on the numeric values, but only for rows where Complete is True. In this case, the sum would be 3 which is P1 + P2 or 1+2.

To drop the “P” I use the function Right() and this gives me just the number.

My problem is that I’ve found three formulas that give me the correct answer, but in all three of them Coda gives me an error of “Wrong argument type” with “Sum expects parameter value to be a number, a list of numbers, a formatted number, a list of formatted numbers…”

The formula works perfect, but it tells me that error when I open the formula edit popup.

Here are three variations of the formula:

Sum(Questions.Filter([Complete]).Importance.forEach(Right(CurrentValue,1)))
Here I have Sum() as the top level function with everything inside it.

Questions.Filter([Complete]).Importance.forEach(Right(CurrentValue,1)).Sum()
Here I do the table filtering first and Sum at the end.

Questions.Filter([Complete]).forEach(Right(Importance,1)).Sum()
Here I also have Sum() at the end, but a different position of the forEach loop. It’s more concise because it doesn’t need to reference currentValue.

Conceptually I understand what has to happen. I filter the table to the rows where Complete is true. Then I need to Sum those rows on the Importance column, after converting the values to numbers.

I have three data points:
Questions.filter([Complete]) → to filter the rows.
Importance.forEach(Right(CurrentValue,1)) → to convert the values to numbers.
Sum() → to add up those numbers.

No matter how I assemble those, Coda says Sum is not getting the right data.

What gives?

[Troy_Larson]

You ALMOST had it . . . Right returns a text value that you need to convert to a number.

Sum(Questions.Filter([Complete]).Importance.ForEach(Right(CurrentValue,1).ToNumber()))

[Guyinpv]

Haha, yes indeed!
I’ve done enough programming I should have known to question data types.

Pesky error is gone.

[joost_mineur]

Hey @Troy_Larson ,

Yes, this works, but it is also asking for trouble. What if there is someday an importance level of P11? I prefer to have the P values come from a lookup table with a 2nd column called ‘value’. It makes the formula clean and allows for flexibility. If you want to give P5 an importance value of 7, you are free to do so.

The formula would look like something like this:
Questions.Filter([Complete]).Importance.value.sum()

Greetings, Joost

[Guyinpv]

Appreciate it, but that isn’t my use case. There will never be more than P5 nor a change to the value system.

What I don’t like about lookup tables is that they create a hard link across multiple page copies.
Or to put another way, what I’ve built is essentially a template page, it gets copied for each client I use it on, and the data in that page needs to be static and archived once finished, or self-contained. If I use a lookup table from somewhere else, this would be linked to all the copies I ever create, and if that table changes, it effects every past page I made, which could lead to bugs of its own.

So then another solution is to put the lookup table on the same page along with everything else. So every single page copied will have its own lookup table. To me, this is ugly, takes up space, the table contents never change anyway, and it creates a ton of extra references in Coda so whenever I want to link tables or do formulas, I have this giant list of tables to slog through.

Maybe there are some ways around this? There could be solutions like this, but I don’t know if these are possible:

1. A way to create a lookup table just in meta-data, something attached to the page but isn’t seen in the main content area, just like variables or something.

2. A table that is limited to being used only on that page so that it doesn’t show up whenever I’m editing any other page. It would be a private table only usable on the one page it exists on.

3. If the first two options can’t be done, then at least some way to put the table out of sight on the page, like hide it or something, and prevent it from appearing in table lists if possible.