I have created a massive 106 page, 116 table Travel Log doc which details all of the planning of our family trips for the past 14 trips.
For each trip there are multiple separate pages and a unique Expense Tracker table to keep the trip expenses isolated from one another. I wanted to be able to add and modify expense tracking from trip to trip, without trying to pull every trip expense in 1 master table.
My question pertains mostly to this Expense Tracker table for each year’s trip. They are all started from the standard coda Expense Tracker template table, so they contain similar columns, like description, category, amount, etc.
I would like to create a summary table of all year’s trip expenses. Is it possible to create a new table, and then reference each year’s expense table in a row, and then write formulas to summarize the “linked” expense table, such that it pulls summary data from the referenced table.
Every attempt that I have made, I end up on with links to the tables, or references to a formula that might be a sum of the 1 specific expense table, but isn’t really functioning the way that some other coda relation functionality tables work. Am I overlooking something very simple, or am I trying to do something which isn’t even possible?
What you want is totally doable. Can you share a dummy doc?
Just make a copy of your original doc, then delete the data and anonimize it using Fake expense 1, Fake expense 2, rename tables to Trip 1, Trip 2 and so on so we can see what is your doc structure.
It makes it easier to get help you need from more colleagues.
Personally I would keep all the expenses in one table, even if a trip has 100 expenses and you have 100 tables that is 10,000 rows which is still a very manageable and fast table IMHO.
To make it even feel faster you can have a table like DB Current Expenses in which you input expenses you incurr for present trips that haven’t finished. Then using a button/automation push these rows to a helper table that stores these temporary archived expenses. In the master table you will store all past expenses/archived which you will use to calculate your stats.
In the end you have to separate where you input the expenses from where you store and calculate your stats as otherwise whenever you will input a new expense…your stats will be recalculated based on that new expense which will slow things down.
I have a 67000 rows table in this way and is still a smooth experience as input storage and calculations are separated and do not run concurrently.
I want to emphasise what Christian is saying here. One of the most common questions on the forum relates to how to combine tables which should have been one table to start off with.
It is going to make your doc much more flexible and easier to work with going forward.
I understand the benefit of using 1 table in a doc/database vs. multiple tables. I am not trying to combine data from multiple tables. I am trying to summarize data from multiple tables that happen to have similar data stored in them (though not always), but definitely have common fields (like Amount).
This answer feels generated by AI. In practice, the instructions seem logical, but for Step #2, I don’t see how you can create a Lookup/Relation column that allows me to link/enter one of the travel expense tables in a row. This is how I would also like to create this summary expense table, but it doesn’t seem to work in Coda like that.
A relation table can only relate to one other table. Which would then imply that you are going to have 14 different columns, one for each trip. And a new column for every new trip. Which is STILL NOT going to give you a total across all trips.
You would need to write a formula that will have a filter to reach table and pick the total from that table. Again, you need to modify it every time you take a new trip.
And analysis takes even more effort: annual expense? New 14 party formula. Total food vs fuel vs accommodation? New formula new formula new formula, each with their filter requirements.
Combining expense related information into a single table, with identifier for the trip, will greatly simplify that. And if your sea trips have specific columns, add them. If your camping trips have special columns, add them.
It is orders of magnitude easier and more flexible, in 99% of cases to apply filters and views, than what it is to combine from multiple tables.
hey Kevin,
If I’m understanding you correctly, this is a scenario I have run into as well. Here’s what I have done.
Instead of making a summary table, I created a grid. The key difference is that in a grid, each square can have its own formula (instead of needing to write a formula for the entire column at once). This allows you to create a “table” that includes formulas that will reference different tables across your doc.