Gathering Table Data, Grouping by Catagory and Sum Amounts

Off-Topic
1

Hi all, I am very new to Coda. I have something I am trying to achieve.

I have 2 Tables…

Table 1 is populated with Transactions
Column 1 = Name
Column 2= Transaction Amount
Column 3 = A Select List called Tax Category

Table 2 is blank

I would like Table 2 to populate by Tax Categories from Table 1.
I would like it to summarize the amounts into Tax Categories per row

For example…

Table 1
Broom, $4.00, Office Supplies
Wood, $20.00, Shop Supplies
Dustpan, $1.00, Office Supplies
Ladder, $70.00, Shop Supplies
Oil, $3.00, Car Repair

Table 2
Office Supplies, $5.00
Shop Supplies, $90.00
Car Repair, $$3.00

*** The goal here is to have an ongoing list that I will add to throughout the year. As I categorize expences in Table 1, Table 2 will populate.

I am a newbie here but If I can understand what to do in this situation I think its will open a lot of doors for me with how to approach formulas in Coda.

Thanks in advance for any help I can get.

2

Hey @Able_Thought ,
welcome to the community!

You can use a relationship column to make this work very quickly.

I made a short silent recording to give you an idea:

Here is the doc:

You will get used to the formulas and concepts very quickly. You can also do the official Coda course An introduction to Coda's building blocks - Coda 101 | Courses, I bet this will explain things like this as well at some point.

Best
Daniel

3

Hi Daniel this was so helpful and worked perfectly. It’s all starting to come together for me. Could I bother you for one more question?

Same scenario above but if there were 3 tables what would the formula look like on the “Table 2” Totals column?

For example:

Table 1-A
Broom, $4.00, Office Supplies
Wood, $20.00, Shop Supplies
Dustpan, $1.00, Office Supplies
Ladder, $70.00, Shop Supplies
Oil, $3.00, Car Repair

Table 1-B
Computer, $400.00, Office Supplies
Vacuum, $200.00, Shop Supplies

Table 2
Office Supplies, $405.00
Shop Supplies, $290.00
Car Repair, $3.00

I am having trouble figuring out how to properly write the formula to combine Table 1-A & Table 1-B into Table 2.

Thanks again this is so helpful.

4

Hey @Able_Thought
sure, no problem. I have two answers for you.

Option 1: Working with Views (Recommended)

For that scenario, I would recommend working with views, instead of two tables. A “view” uses the exact same table as a source, but can e.g. filter out rows or show different columns.

I made a quick video again to explain the system. I don’t know what the difference between Table A and B is, so I just used the letter to categorize, but it could be “renvue” and “expenses” or “Paul” and “Sheila” etc etc…

Also consider making a “master table”: Instead of having table A as the table and table B as the view, you could use the table transactions as your “database” on a different page (where you can even hide it). Then design your doc and use a view of the transactions for table A and another view of that table for table B – wherever you need it.

And the doc: https://coda.io/d/Community-Examples-DS_d64iZZh9gx8/Working-with-views_su5AR?searchClick=f835eacc-9d35-46c8-958f-5b02dcd042be_64iZZh9gx8#_ludI3

Option 2: Two tables and a formula

Here is a way to do it with two tables. This time I duplicated Transactions instead of creating a view:

I basically just created a another linked relation and added the two amounts.

Option 2 with 1 formula instead of 5 columns

If you want to go deeper into formulas, there is also an alternative way of getting the data. You can access a table without the linked relation column and use a filter to get only the rows you desire. By using “thisrow” on the categories column, the filter will get all rows where the current row is selected. Now you have the same result in one column instead of five.

Bildschirmfoto 2024-01-07 um 10.13.44
Bildschirmfoto 2024-01-07 um 10.13.441444×662 59.3 KB

Doc: https://coda.io/d/Community-Examples-DS_d64iZZh9gx8/Two-tables-One-Formula_suIPa?searchClick=f835eacc-9d35-46c8-958f-5b02dcd042be_64iZZh9gx8#_luFqY

5

This is perfect Daniel, I can’t thank you enough.

6

You are very welcome!

Views, relations and formulas are, together with actions, the key elements that make Coda such a wonderful and uncomparably useful tool.

Have fun exploring the possibilities :slight_smile: