How to make a simple form to fill out multiple related tables

[john_doe]

Hello there,

brand new to coda, have read quite a lot et spent time watching tutorial.
Yet it’s surprisingly difficult to achieve something very basic.

Am a dog trainer. Made a table for dog owners, another one for dogs, then another one for evaluations. One owner can have several dogs, and one dog can go through multiples evaluations.
An evaluation is for one dog at a time, and a dog belongs to one owner at a time.

Trying to make a form where I can just create an evaluation, fill out owner properties, then dog properties then evaluation properties.
While playing wiht coda, at some point I manage to get a “+” button to add new dogs belonging to current owner but can’t reproduce it.
Currently, wether I use form view or detail view, I’m facing a long list of previous owner and dogs to choose from when I actually want to create new one and fill in all data.

Any insights would be welcome, as am afraid to have time to struggle with such simple things to rebuild my customers management system.

thank you

[Billy_Jackson]

It sounds like your “Dogs” and “Owner” columns are Lookup columns that reference different tables (which is exactly right!) What you’re looking for is to select the “Allow Quick Adding of New Options”. If you Right Click on the Lookup column header, and open up “Option Settings” you should see the toggle in there, as in the screenshot below:

This will allow you to simply start typing a name into the column. Coda will then add an option that says “+ Add [whatever you typed in]” which will create a new Dog/Owner with that name.

There are other ways to fill out more details for them, if you need, but I would start with that and see if it accomplishes what you need!

[john_doe]

Hello Billy

thank you very much for your answer.

My test doc is comprised of 3 tables : owners, dogs, evaluations.
Each would have a bunch of properties and I tried to use lookup tables in multiple ways.
What would make sense the most for me at the moment (unless am wrong) is to have a lookup column in dogs table that relates to owner name and a reference to dogs name in owners table. Then the evaluation would have a lookup column for dogs name and a reference to dogs owner.

As for what you suggested, I checked all lookup columns in my test and they all had that allow quick adding of new options check box on already.

I’m missing something because using a form view on my evaluation table, I dont see a way to add new dog and owner. All I can do is select previously dog and owner entered (it could be a long list).
On the evaluation table itself, I can see the plus button to add a row in dog but that doesn’t let me input all dog’s and owner’s properties. It just add a row with a name and nothing else.

My need is as this:

Imagine I am sitting in front of a dog owner for an initial evaluation of one dog.

As I ask questions, I type in all informations in a nicely organized unique form.
So, I dont want to scroll down several tables to get to the end of each to just add rows, then scroll horizontally to fill in little cells in a giant table. I need a simple EMPTY form to fill in all informations at once.

First, I fill in owner and family members informations (name, phone, email, address, how many adult/kids in the house… ),
Then, dog informations (name, breed, age, neutered or not, health…),
Then environnemental informations and list of issues for which I’ve been called in.

Am currently using a simple notetaking app. It’s just not very convenient to later on make associations and searches between invoices, work sessions schedule, training plans and so on. Hence the need for a tool like coda.

[Billy_Jackson]

I understand!
I think for structure, this is what I recommend:

TABLES {Columns}
Owners {Name, Phone, Email Address, # Adults, # Kids, LOOKUP: Dogs}
Dogs {Name, Breed, Age, Neutered, Health, FORMULA GENERATED LOOKUP: Owner}
Evaluations {LOOKUP: DOGS, FORMULA GENERATED LOOKUP: Owner, Issue, etc.}
Intake {All of the above^^}

You will then have two different Evaluation Forms: One for pre-existing clients (where you can just select the dog + owner from dropdowns and fill out the evaluation-specific information only), and one for new clients (where you will enter ALL the information)

On this second form, you will enter all the information for the Owners, Dogs, and the initial Evaluation. Upon submission, you can trigger an automation that parses all the information into the separate base tables.

There are other ways to accomplish what you’re looking for, but I think this one - while adding the automation step - gets you the simplest, cleanest intake form with as little fussing as possible, which it sounds like is your top priority.

Let me know if that makes sense!

[john_doe]

That makes sense indeed !

So I get that I need to make 2 pages (one for each use case) with all the inputs I need, then parse/copy those inputs in different tables which will have the almost exact same field names.
From coda support information and your proposition, I get it’s not possible to use coda form feature and I must redo it entirely using automation.

I am indeed in a hurry to build at least a working draft of this system, since am managing several customers a day and have already too much data to handle using text files, spreadsheets, emails, hand written notes, pdf there and there.

[Billy_Jackson]

@john_doe

So I get that I need to make 2 pages (one for each use case) with all the inputs I need, then parse/copy those inputs in different tables which will have the almost exact same field names.

A form would be okay for this!
You’ll just have two different ones.
One on the table “INTAKE” (for new customers)
and one on the table “Evaluations” (for old customers)

You will then create an automation, triggered when there’s a new Intake row, to copy all the relevant data to the 3 separate tables.

[john_doe]

how would I copy all data to different tables using automation ?

[Billy_Jackson]

So you would make a button column in the INTAKE row and the formula would be something like this:

AddRow([Evaluations], 
Evaluation.Owner, 
AddRow([Owners], Owners.Name, thisrow.OwnerName, Owners.PhoneNumber, thisrow.OwnerPhoneNumber... etc etc),
Evaluation.Dog, 
AddRow([Dogs], Dogs.Name, thisrow.DogName, Dogs.Breed, thisrow.DogBreed... etc etc),
Evaluation.Issue, thisRow.issue... etc etc)

Then you would set up an automation to push that button whenever a new row is added to Intake.

Does that make sense?

[john_doe]

so I understand that I can nest an AddRow function within the columnValue field.

Thank you very much Billy
I’ll try this out

[Billy_Jackson]

You can indeed! an AddRow will return the row it adds, so that you can connect a lookup column to a newly created row.

Once you’ve given it a try, feel free to share the document with me if you need any help/pointers

[William_Bell]

This has been a very helpful discussion on learning how to populate multiple tables from a form - thanks so much for your ask, and the responses!

Much success w/your application John - I’ve used Coda to prototype an application I’ve had in the back of my mind for a few years now - I LOVE Coda!