Creating Summary Table/View

1

Hi all,

This is a very basic functionality I’m looking for, but I can’t seem to figure out how to make it work yet.

Let’s say I have the following table:

TABLE 1 (Data):
Type | Value

Cat | 1
Cat | 0
Dog | 2
Bird | 1
Dog | 0
Dog | 4

I want to create a summary section at the top of my table to aggregate information (pretend it’s some complicated formula that’s more than just sum or avg). In a normal spreadsheet, I normally can just create a new row at the top and create custom formulas to aggregate the information for that column below.

[REQUEST TO CODA DEVELOPERS: Please add a better way to create custom summary information–the ones at the bottom now are very limited in their basic functions rather than my custom formulas. And it would be nice if the summary information was displayed at the top instead of the bottom since that’s where I want to read that information quickly.]

Anyway, since I can’t currently do that in Coda, I decided second best option was to create a separate table to summarize the information. I want to create a table that pulls in all the information and aggregates the data using my own custom formulas. So the summary table should look something like this:

TABLE 2 (Desired Summary):
Type | Avg Value of Each (example formula = SUM(Value)/COUNT(Type))

[Cat] | .5
[Dog] | 2
[Bird] | 1

CHALLENGE: I can’t figure out how to make it so that the summary table automatically pulls in each of the Type values from the first table and only pulls in the unique instance. If I use the formula =UNIQUE(Type), it lists the list [Cat, Dog, Bird] into each new row so it becomes:

TABLE 3 (Bad):
Type | Avg Value of Each

[Cat, Dog, Bird] | .5
[Cat, Dog, Bird] | 2
[Cat, Dog, Bird] | 1

I want it to list each separate [Cat], [Dog], [Bird] as its own row like in TABLE 2.

Hope this makes sense! If anyone needs to make a sample doc, I can, but hopefully this is enough.

2

It looks like you’re running into a key between Coda tables and regular spreadsheets.

Formulas are for a full column which allows for many advantages. Summaries are still needed, and for this, Coda offers “Canvas” formulas and controls. You can reference the table and use any formula you want and you can do it from anywhere in the canvas.

Here’s a great blog article that explains this in more depth as well as the reasoning for creating the product this way…

3

Yep, I’m aware of the difference between the normal spreadsheets and Coda/a relational database, which is why I’m in love with Coda as something I’ve been waiting for forever :slight_smile:

Canvas formulas aren’t bad, but they’re very manual. Let’s say I added a new type of animal to my Table 1–Chicken. Now I have to manually create each of the canvas summary formulas again for the Chicken type.

That’s why I settled for trying to create new separate Summary table (Table 2). I’m just having a challenge pulling in data from Table 1 into new separate rows for each of the types from Table 1 since there’s no “foreach()” type of function.

Thoughts on how to pull in data from Table 1 to populate a new Table 2for each unique instance of a new type of animal? Seems like this should be a basic doable functionality. :slight_smile: I personally haven’t figured it out yet!

4

Gotcha! And sorry for the assumption.

You’re definitely correct in this not being an easy thing to do without the manual process of adding the additional item to the summary table.

If a bulleted list is alright though, a formula can be used to summarize just about anything. Here’s an example similar to what you posted…

[My Table].Fruit.Unique().FormulaMap(
  Concatenate(
    CurrentValue, " = ", [My Table].Filter(Fruit=CurrentValue).Count()
  )
)
.BulletedList()

The first line creates the list to be used and uses the Unique() formula so items don’t show up multiple times. The FormulaMap() then runs through this list and calculates individually for each item. The BulletedList() does just that, takes the array and gives it a bulleted list format.

You can test this out and you’ll see that when you add a new item to your table, it will show up here too.

This is an over-simplified example, but it can be expanded.

609×532 29.7 KB

2 Likes
5

Thanks for that! Do you know if there’s a way to put that into a table instead of just the canvas? I’d like to have multiple columns for different calculations for each of the different types of animals.

6

Okay, I’ve figured out a somewhat simple solution for those this might help:

=Unique(Type).Nth(RowId(thisRow))

This populates the value for the list Type where the order of the list matches up with the row number. HOWEVER, you still have to manually add the correct number or rows to the table. If anyone knows of how to have new rows automatically be added, that’d be great!

7

Getting that into a table is trickier and would require either manually adding values as they’re added to the main table or a button to find that and add a row, then you could have an automation that clicks the button to add the row. I think it can be done, but feels like a work-around.

8

I have a solution that might just work - Option 3 here?

3 Likes