Table with single select of subtables to pull the sum

Hi everyone

I’m new and did a couple of hours of learning videos, trying things and reading the forum. I can’t seem to find if this already has been asked.
The title basically sais it all. I’ll add a picture wich should help visualize how the result should work. I belive coda has more power under the hood calculation wise. Neither Notion nor Airtable seem to be suited for the job.

I forgot to add, the Table 1 has at the moment all 3 sub-tables. But depending on the job, some times only 2 sub tables whould be needed. And it’s only 3 sub-tables at the moment but in the end there will be alot more.

Not at the computer now, so can’t make a demo.

The best way (the Coda way) would be to organize those data so that it all comes from one table (i.e., combine those three). It makes a lot of sense, given that:

  1. table structure is the same
  2. they are on the same level of hierarchy — it’s basically tasks in three groups
  3. not all groups will be present at all times
  4. I assume you want to reuse the doc for multiple customers by copying it and filling anew — which may not be necessary at all, as you can track ALL customers in that single table.
  5. but most importantly, since you say it won’t just be those three groups but many more later.

And to achieve the output you want, with three sub-tables, you can simply make views of that one master table. And if you use a single doc for all clients, you can add a control to easily filter that output per client.

P.S. But, of course, it’s possible to write a simple SwitchIf formula to do what you want without restructuring the data. That way won’t be scalable though, and for every table you add you’ll have to add a row to Table 1 and edit that formula by hand.

P.P.S. And welcome to the community!

1 Like

I’d argue it’s not only the best Coda way but the general data-modelling way as well. The current design with multiple tables exhibits a design flaw called attribute splitting. You’ve taken a piece of information about your data that should be modelled by a column in a single table and instead modelled it as separate tables. As Paul says, you have to change your database design every time you add a new type.

Here’s a good article about it:

Hope it helps. :slight_smile:

I see your point, but i think it isent too much of a job for a database. I made a new example wich hopefully shows easier what i mean:

Please don’t take this the wrong way but your second example makes it even clearer that you shouldn’t be splitting recipes up into separate tables because every time you add a new recipe you’ll have a hard time incorporating it into the logic of the rest of your system. You’d have the same problem in any relational database.

Can you share the document so I can play with it and suggest an alternative solution?

I just cant figure out how i can specify ammounts for the ingridiants:

I’m thinking of something like this…

  1. A table of products that aggregates everything and shows a summary of the recipe (including amounts!)
  2. A table of recipes that breaks down each product into ingredients
  3. Your original ingredients table

That’s it. :slight_smile:

When you want to add new products you simply add a row to the Products table and however many rows you need for the ingredients in the Recipes table.

It sure looks nice :grinning: but it is missing one step. Several Lists with diffrent quantaties of products.
I further refined my main project. After suggestions to put everything into one table im at the same spot as your suggestion (does not look that fancy though):

Does this work?

/edit - forgot to multiple by Anzahl. Fixed now.

Thats great, wonderful! Im impressed. Very clever :clap:
Thanks very much.

/edit - i wish it whould be a bit more intuitve for a brain wich isent used to database thinking :stuck_out_tongue_winking_eye:

1 Like