Re: One to many relationships and forms

Stephen_Lawrenc · ‎Nov 03, 2018

I have 2 tables a sales order table and a sales order item table, to allow a sales team member to raise an order with multiple products on one order.

I would like the team member to do this via a form.

How do you auto-generate a new record in the sales order table from a form in the sales order item table?

Stephen_Kelley · ‎Dec 03, 2018

I need the same thing; did you ever get an answer? Based on my understanding, you would have to first set up an order header record, write down the order number, then go to an “Items” table and enter in each item and order number to tie them together. This seems very cumbersome to me, and is unlike anything I have seen in any widely distributed DB application.

Jeremy_Oglesby · ‎Dec 03, 2018

Not at all – after you create your “Order” record, you can populate it with “Items” by creating “Items” records directly in the field that links to the “Items” table. You never need to leave the “Orders” table, and you are creating your “Items” directly within a single “Order”, so they are already associated with that “Order”.

Stephen_Kelley · ‎Dec 03, 2018

Thanks for the VERY quick response! Actually, I was trying to find something vaguely similar to what I am trying to do. Since I have you, maybe I could be a bit more specific?

I am trying to put together a financial inventory for my financial planning clients to fill out prior to our meetings. It would need a plan base that could then connect to income, expenses, assets, etc. Each of those would have various data points as well. For example, income could be Social Security, a pension, salary, rental properties, annuities, etc. I would also need an expenses table that would show either total expenses or broken down into categories, whichever the client preferred. I’d also like to link people. For example, I might have a multigenerational client. I would like to do the above, then if necessary, bring one contact record under another as a beneficiary, heir, trustee, etc.
Can these types of relationships be drawn from a data entry form? Would also need to be able to print out a report, showing the client data at the top and then lists of each of the various child records, rolled up, subtotaled and totaled.

Jeremy_Oglesby · ‎Dec 03, 2018

Just skimming over that, I don’t see any obvious reasons why you couldn’t do these things. I’m not in a position at the moment to be able to walk through all these relationships in detail, so I’ll refer you to the Airtable documentation for now.

You’re going to want have a solid understanding of the types of relationships you can create, and what effect they have within the database:

A beginner's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...

You’ll want to know how to draw data from one table into another, linked table, using Lookups and Rollups:

Guide to formula, lookup, count, and rollup fields

Formula, lookup, count, and rollup fields (columns) are powerful field types that allow you to compute a value based on values in other cells. These fields are configured in the field configuration...

You’ll probably need to incorporate the concept of a “join table” to facilitate more complex data relationships:

A beginner's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...

The classic example here is that of using the “Line Item” as a join structure in-between a Product record and an Order record. You don’t add a Product record directly to an Order record x number of times. Rather, you add a Product record to a Line Item record, express a quantity within the Line Item record, and then add the Line Item record to an Order record. Product and Order are related, but only through a Line Item, which represents the “joining” of a Product with an Order.

You’ll want to use the Page Designer Block to generate your printable reports. This block allows you to amalgamate any data from a record and/or its linked records into a formatting of your design.

Page designer block

The page designer block is part of Airtable Blocks, a Pro plan feature. Blocks let you extend the functionality of your bases: you can use blocks to bring new information into Airtable, visualize a...

Lastly, I’d encourage you to experiment. I have a base called “Sandbox” where I just try things out that come to mind to see how Airtable’s mechanics work, and to plan out how I can structure my data and pull it into the records I need it to be in. You’ll be much more adept at using and improving your own database if you really dig in so that you grasp the underlying stuff. :slightly_smiling_face:

Oh, and one last little piece of advice, which applies to databases generally, not just Airtable – try to think of a record (row in Airtable) as a representation of a person, place, or thing. And think of a Table (sheet or tab in Airtable) as a collection of persons, places, or things. Always name your tables as a plural expression of that collection – a “People” table will contain records each of which represents a “Person”. An “Exemptions” table will contain records each of which represents an “Exemption”. A “Financial Records” table will contain records each of which represents a “Financial Record”. As much as possible, keep like things together in one Table – DON’T split up “People” into two different Tables, one for Primary’s, and one for Dependents. RATHER, link them to a Financial record in one of two Linked Record fields – either the Primary Person field or the Dependent Person field. Hopefully that makes sense.