I have recently started in a company that run a lot of business processes through Coda. They have a Project Planner that is a large and complex doc that runs the Project Management Process and Data Flow, runs the Finance Data and Process Flow and provides Business Data to get business behavioural insights, a Knowledge Base and other smaller docs.
I am relatively familiar and savvy with working out formulas in Excel and have managed to build out and fix some new and simple features however I;ve been tasked with building out a new piece of functionality called the Data Funnel Dashboard which on the surface I can do and understand how I would create it butā¦
My issue is I feel very overwhelmed coming in and taking over a doc that has 13 large databases, the majority of columns are built with formulas and there must be upwards of 35 columns per database.
So how do I begin to unravel this and to build out what is a relatively simple dashboard as the data is definitely held within the DBs, they are just vast and complex.
Any help is gratefully received - and I didnāt even get onto the specifics of what I need to create.
Hi,
I would look into Database Design Principles which teaches the appropriate way to set up databases in such a way the redundancy is eliminated or reduced and formulas/queries wonāt need to be as complex.
Creating a data model (see pics) would be a great way to visualize what you have and then map the data into smaller tables (some would say use one big table but Iām not a fan). This helps with understanding the relationship between tables (1 to 1, 1 to many, many-to-many) and if new tables (called junction tables) will need to be created.
This framework is really good, hope this helps!
This is awesome. Do you know any resources on how to optimize docs? I have some large docs that run pretty slowly.
Hi @Jess_Davies
Paradoxically this can be a drawback in working with Coda. Coda is radically different from not only spreadsheets, but also word processors and even database tools.
For this reason I would recommend that you experiment with Coda for a few months before you get down to serious building of your project. Pay special attention to tables and their relationships.
@Coda_Solutions has a good post above on relational database design. It is extremely important to get a good understanding of your data and the relationships between the objects. However, Coda is not a relational database - there are no primary keys to tables, no queries like in Access or SQL, there is no enforcement of relational integrity between tables.
Therefore do not simply jump into designing a 3rd normal form ERD. Experiment, experiment, experiment. For example, in the image they have a Product/Category relationship, which is traditional 3NF. A typical Coda approach would be to have a Column type Select list. Many Header/Item relationships can be done in a similar way, depending on the number of columns either table.
Welcome @Jess_Davies! It is indeed a hairy task figuring out how somebody elseās work. I hope these two tips will help you understand the data structure of the personās doc. And also work backwards to understand how things were derived.
TIP 1
Take a look at the āDoc mapā to get a high-level overview of the Coda doc.
This will let you know how many tables there are and also the number of views. If you are not familiar with views, it is simply a different way to display data from its main table. For example, I may create a timeline view and a calendar view from my main project database.
You can quickly jump to a particular table from the doc map as well.
TIP 2
You can also drill down into the details of each table by clicking on the tableās options.
From the pop-out panel, you get a tidier display of
- All the views of that particular table
- The columns in the table. You can deduce the column type by the icon
- Detail / form layouts (just one of the many types of layouts available where thereās a different way to present the data you want). I know it can be confusing at first!
- Where else the table may be used at the page level of the Coda doc.
@Jess_Davies Those are all great suggestions from the other Codans here. First, donāt get overwhelmed. Just remember when eating an elephant you do it one bite at a time.
Unless they want things radically different (which is highly possible) I always try to mimic what the user has and is used to. You might make something look sensational, but if they feel overwhelmed using it, you lose because they wonāt use it.
Your number of tables and columns isnāt that bad. Iāve seen and built far more complex. If you build things properly with related tables, in Coda things can work quite easily. So definitely understand your table structure and I would definitely look for anything that is āwrongā and fix it before moving on.
One thing I notice with some clients is they donāt understand when something should be in a separate table. They think one big spreadsheet. I think related tables. They also like to use select lists where they type in the selections. I usually change those to tables. Thatās quick and easy to do in Coda, thereās an option in the column settings one click and it turns that into a table for you. Then if I want to use that select list in other tables itās there and ready to use.
Coda is a bit odd for us database developers in that we are trained to ānormalizeā data which means you break it all down into the tiniest bits and then relate things back together. However, in Coda, you donāt always want to normalize all your data. So learning how to build proper related tables is great, it can at times work against you because you might then wind up just adding back in more columns to get Coda to work like Coda works.
If you can write complex Excel formulas, Coda formulas are a breeze. They are different, but not hard at all to understand. Documentation is great. The formula builder is helpful and allows you to see all the syntax of the formula and you can easily click to get to the formula help doc to get even more info.
And youāve already found one of the best free resources. This community is THE BEST online community Iāve ever seen. Everyone here is helpful and sometimes if you share a sample doc someone will be willing to hop into your doc and show you how to solve a problem.
And if you do get in over your head or just feel you need a little extra help. There are plenty of Coda Experts you can hire. Some of them can be hired hourly to help you learn. Sometimes planning an hour of cobuilding time with one of them in your own doc can get you comfortable in Coda more quickly than on your own.
Welcome to Coda!
2 Likes
Welcome to the community, @Jess_Davies!
My advice, much in line with with the other great posts, is to be patient with yourself. On the surface, youāre solely making the move from Excel to Coda. Underneath it all, however, youāre transitioning from a spreadsheet to a database, which is a new skill and as such will require some time to get the hang of it.
Plus, chances are that the Coda doc you are taking over was build by someone also undergoing a learning curve on how to best structure a dataset. Hence, the wide columns you mention are possibly not set up according to best practices, which makes deciphering and optimizing them a task in and of itself.
Fully echoing @boonshinās sentiment: Spend your time exploring the doc map, identifying the most important tables, and then exploring those tablesā columns in detail.
One āhackā that might be helpful for some edge cases: When in doubt whether a column is actually in use, highlight the column and press delete. if it is in use in a layout or formula reference, a warning will pop up (see screenshot). Treat this as your next clue in the scavenger hunt to explore that particular layout or formula, to see how it connects together.
(If no warning pops up and the column is deleted, simply press Ctrl + Z to undo).
Let us know how itās working out for you! All the best,
Nina
2 Likes
