I'm trying to do away with repetitive data on spreadsheets and I'm not quiet getting there with Coda. Am I using it wrong?

Hi, so I deal with a lot of intertwined and connected data. If you’re someone with coding experience, you’ll see that what I’m trying to do is basically define objects on a spreadsheet, but instead of having the object properties as all being identical slots in an array, actually having them be arrays inside the original array (the spreadsheet row in that case) as needed.
Think of it as a 3D (2.5 dimensions btw, not 3D) data on sheet.

Example (this is a real life example of my daily work):
I’m doing automation equipment installation and programming work for multiple buildings. I have to keep track of many things, all related together.
I’d like to open a spreadsheet and see the list of buildings I’m making automation for.
Each building will have multiple floors. So rows are building names, columns are the building’s address, number of floors, and… the list of the floors. As of today, the only two ways to go about this on a spreadsheet are: A) you make multiple columns and name them Floor 1, Floor 2, …Floor 100, then enter the floor ID for each of those floors. B) Create a separate tab with the key field as Floor ID, list all floor IDs of all buildings, then make a column for Building Name, where you assign the floorIDs to the proper buildings. smh at both methods I mean, there has got to be a better way to enter such simple data.
A better visual way. Since let buildingFiftyFourSouthSide = { streetAddress: "540 East Main St.", numberOfFloors: 6, floors: {"Basement", "Mechanical Equip.", "Pharmacy Floor", "Roof Top"}} is still a valid way to go about it if you really wanted.

I would love to be able to see the floors all listed there as items within one cell. You click the cell and it opens all the floors as a list, and opens that building now that you’re zooming it.

There would also be another column that has the equipment found on that floor.

Certain equipment is shared among floors, say a tall air duct that actually is present on two floors at once. We can just show the tag or item for that equipment mentioned twice, each instance on the row of the floor it’s at. (since this would be a visualization nightmare if I expected the piece of equipment’s tag to show it’s used on both floors).

Clicking this piece of equipment that is found on multiple floors I’d like to see it in a view where that piece of equipment is listed as the key field (row) and a column is there with the floors it’s found on with two items for the two separate floors within the same cell showing.

I don’t know if Coda can do this, and I’d love it if it does. I was going to opt for some JS on a GSheet, and show/ hide tabs automatically as a user interacts with the 2D spreadsheet but this is just a dirty solution.

So, would I be able to do this on Coda? Did anyone did anything similar to this in the community?

p.s I know that what I’m describing has been established with a bunch of flat spreadsheets thrown in a database and tied together to show to the user in the form of reports, the user enters data through forms, and searches data through queries. I understand this very well, but this is why I’m on Coda and not a light database tool. I don’t want to bother with forms, or queries. If I have the data structured properly, I believe I wouldn’t need a database that requires yet clunky time-consuming designs and updates to forms and reports.

Hey @Aiden_Ibrahim, welcome to the community!

All of this is possible if you design your data correctly. Actually that’s what Coda is perfect for.

Like with any database, you normalize your data first. From my superficial understanding of your case, you’ll have these tables:

  • Buildings — with all properties of a building
  • Floors — with all properties of a floor AND a lookup column (editable) linking this floor entry to a Building (unlike SQL, you don’t link by foreign keys, but via selecting one or multiple foreign rows)
  • Equipment — with all properties of a piece of equipment and a multi-select lookup column (editable) to select one or more Floors where this equipment is found.

When you have your structure, you can link your data backwards, i.e. denormalize by live formulas. Unlike SQL you don’t do that at query time, but at design time. E.g., in Floors table you can create a lookup column with formula that will pull in all Equipment that’s found on this floor:


where thisRow refers to a Floor for which this formula is evaluated (once per row in Floors), and CurrentValue refers to an Equipment row that’s being currently evaluated in a Filter conditional (once per Equipment piece)

Then you can build different sorts of UIs, e.g. a drill-down interface like this but with the Building->Floor->Equipment / Building -> Equipment -> Floor structure. You can do a lot with buttons.

All the time as a paid consultant / doc builder for hire :slight_smile: