I’m trying to create a form that will allow club members to order multiple products. Each product has different color and size options, and I can’t figure out how to build the table to then create the form with all the different options. Any suggestions?
Zollie, the issue I have is each product (t-shirts), does not offer the same colors. Some style of shirts (the product) come in 15 colors, while others may only come in 4 or 5 colors. Any suggestions for how to handle this?
The structure of my Invoices and Line Items Example Base might help get you started! In your case, I would add at least one more table, called [Base Items]. Each item on the [Items] table would be linked to a [Base Item].
For example, say a record on the [Base Item] table is called “T-Shirt”.
The [Items] table could then have records named, “T-Shirt | Black | SM”, “T-Shirt | Black | MD”, etc.
Up to you with how granular you want to get… you could add another table in between [Base Items] and [Items] to categorize further by either size or color.
Thanks for your example! The structure of your Invoices and Line Items makes sense, but I’m not quite sure how to make it work from a form view with a junction table as you describe. What I’d want is something like the following. Three tables: [Products], [Invoices], [Line Items]. The [Line Items] table is basically a junction table between Products and Invoices–it contains a link to a Product, a Quantity field, a Lookup field for Product - Price, and Subtotal (a formula field multiplying the Price lookup by the Quantity). An Invoice contains multiple line items and a rollup field summing up the Subtotals. (I created an example base to illustrate)
The relationships all seem to work fine, but there are a few quirks I can’t figure out regarding data entry:
I want to have a form that someone can fill in for an Invoice by creating the Line Items, but there doesn’t seem to be a way to do it. They can only select a Line Item that already exists, and they can’t modify it from the Invoice form view
Even though I did not check “Allow linking to multiple records” on the Invoice link field of the Line Item table, it still lets me select that line item from other invoices, which then makes it many-to-many instead of the one-to-many I was expecting.
Is there a way to set up a form that allows people to create new line items, not to select existing ones?
Thanks very much for your help! I have experience with databases and another low-code platform, but I’m new to Airtable.
Unfortunately, this is next to impossible using Airtable’s native forms. My workaround is to use JotForm to collect form submissions, and Zapier to import them into Airtable as separate records. This definitely requires some advanced knowledge, but it is doable.
Another workaround would be to use a native Airtable form view (from the [Line Items] table), and make users submit a new response for each item (you’d probably then have to manually link each response to an invoice).
Aha, yes, this is another annoying thing when it comes to Junction tables. They are often necessary but super hard to explain and train people to understand.
Note that the “Allow linking to multiple records” setting is more or less a surface-level feature. All it does is disable the button to add a new record in that field… there are still many ways to link that record to multiple records.
One problem I often run into with Junction tables, as you’ve discovered, is how to teach users not to select items that are already assigned to a different record: in this example, (a different invoice). Here’s my workaround for this problem :slightly_smiling_face:
Create a view of your [Line Items] table that is filtered to show NO records. Name it something like “ADD NEW RECORD”.
On your [Invoices] table, set the linked record field {Line Items} to point to the “ADD NEW RECORD” view.
That’s it! The next time you go to add a line item to an invoice, you won’t see anything in the list to choose from, making it difficult to link something in error.
Thank you very much for your prompt reply. I am a bit disappointed that there’s not some secret setting that will magically do what I want :winking_face: but I’m very grateful for the time you saved me searching for it! I’m probably going to end up needing to build my own entry form and use the APIs, but can maybe make do in the short term by having people submit individual line items.
Your workaround of using an empty report to prevent people from accidentally adding existing records to the wrong parent is extremely helpful!
