I replied with a few solutions for this problem on another post. The best implementation involves linked records.

Thanks, I’ll give it a look.

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?

Thanks

Sorry for missing that. I can’t think of an elegant solution with Airtable’s tools.

Sub-Par Solutions

• Allow customers to select all colors (even the wrong ones), but put helper text in place to explain which products get which colors



• Ditto only provide a supplementary document (such as a PDF)



• Create unique color columns for each product type

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 TItems] 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 lBase Items] and sItems] to categorize further by either size or color.

Hope that helps!

Thanks, I’ll see if I can make that work.

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: gProducts], bInvoices], cLine Items]. The LLine 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:

1. 
   
   

   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

   
   
   



2. 
   
   

   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 bLine 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

1. Create a view of your >Line Items] table that is filtered to show NO records. Name it something like “ADD NEW RECORD”.



2. On your NInvoices] table, set the linked record field {Line Items} to point to the “ADD NEW RECORD” view.



3. 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.

I hope this is helpful!

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!

Hi there!

At miniExtensions, we've created a third-party form that integrates seamlessly with Airtable, enabling you to harness the power of linked records to create complex solutions like purchase orders. Check out this short tutorial to learn how to easily build a similar system.

Just like the Quantity field, you can also add options for color and size to your form.