Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 30, 2020 07:52 PM
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?
Thanks,
Dave
Jan 31, 2020 09:40 AM
I replied with a few solutions for this problem on another post. The best implementation involves linked records.
Jan 31, 2020 11:41 AM
Thanks, I’ll give it a look.
Jan 31, 2020 01:38 PM
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
Jan 31, 2020 02:11 PM
Sorry for missing that. I can’t think of an elegant solution with Airtable’s tools.
Sub-Par Solutions
Jan 31, 2020 02:23 PM
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.
Hope that helps!
Feb 01, 2020 11:17 AM
Thanks, I’ll see if I can make that work.
Feb 01, 2020 07:43 PM
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.
Feb 02, 2020 10:42 AM
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:
I hope this is helpful!
Feb 03, 2020 10:56 AM
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!