Update a multiple select field using Airtable API

[d_turner]

I tried adapting the template for “Product Catalog & Ordering” but it breaks if you need for, say, User A to buy multiples of an item.

What I need to do is to track orders for multiple people of items (Jersey in Small, Jersey in Medium, Shorts in Medium, Shorts in Large, etc.) and some people may order multiples of a single item – and I need to track and pass along the unit price * number to customer purchase total.

Help?

[Katherine_Duh]

The short answer to your question is that you can definitely put in the quantity per item in this template and have it calculate the total for each client—you need to do it in the Order Line Items Table, in the quantity field.

A slightly longer answer: the product catalog and orders template is structured in such a way that there are separate tables for Clients, Client Orders, Order Line Items, and Furniture.

• Clients: information on each client (name, shipping address, etc.—linked to Client Orders table)
• Client Orders: the orders submitted by a client (fulfill by date, invoice, total order cost, etc.—linked to Client who ordered and the multiple Order Line Items comprising the order)
• Order Line Items: the quantity of each Furniture item within a given Client Order (linked to the respective Client Order and Furniture tables)
• Furniture: the items/products themselves (linked to the Order Line Items of which they are a part)

At this point, how the template works might make sense to you, but you still might not understand why the tables are set up this way. Basically, the Order Line Items table serves as a junction table to store metadata about the relationship between the Client Order and the Furniture items.

Imagine that there’s no Order Line Items table. If you attempt to directly link the relevant Furniture items to each Client Order, you cannot express anything about the relationship between Client Orders and Furniture, only that a relationship between those two concepts exists. In certain cases, just knowing that there’s a relationship is enough, but in other cases, you need to say something more specific about the relationship itself—like, for example, the quantity of a given item in an order.

For a more detailed explanation of this, you can read this article on representing many-to-many relationships in Airtable, and more specifically, this section of the article diagramming and discussing the product catalog example.

[Richard_Mast]

All of this works if you use the tables separately. But, the natural work flow is to set up a form that brings all of this together, share it with the customer, and the customer should be able to:

1. Choose his name from the list of customers which would start a new Order record.
2. Choose the items he wants.
3. Input the quantity he wants of each separate item.
4. Click submit and it should all show up in our various tables.

But the way it is now I haven’t been able to get the proper things to show up on the form. I can do a form on the Orders table and when I click on Order items it will not let me create a new order. It should automatically create the new order item record and allow me to put in the quantity.
Any one figured out how to do this yet?

[arne_nori]

hi! that would also interest me … any further insights found? :slightly_smiling_face:

[Richard_Mast]

Hi, No, I haven’t had any response except yours. :slightly_smiling_face: It would make it so much more useful!

[arne_nori]

as i see it right now im afraid that it need a seperate interface written in php or something, that accesses the api to solve this …

but it would clearly be amazing to solve this internal with a form, that can create and update information

cheers

[W_Vann_Hall]

While I agree there’s a gap in the product that falls between form submission by a random person and the current lowest-privileged user permitted data input, I’m not so certain this should be addressed by radically expanding the capabilities of the form view. Instead, I would leave the role and core functionality of forms relatively unchanged but define another class of (non-paid) user with the ability to enter (and possibly modify) his or her own data, along with the screen
enhancements required to craft more user-friendly pages. Just my $0.07 worth.

[Moe]

Richard_Mast:

the natural work flow is to set up a form that brings all of this together, share it with the customer, and the customer should be able to:

1. Choose his name from the list of customers which would start a new Order record.
2. Choose the items he wants.
3. Input the quantity he wants of each separate item.
4. Click submit and it should all show up in our various tables.

We’ve built this solution which solves all these problems:

miniExtensions

Create Line Items in miniExtensions Form

Add new linked records -with all their details- without leaving the form page. Making one-to-many, parent/child, master/detail relationships. e.g. invoices.

Also, referring to step number one:

Richard_Mast:

the customer should be able to:

1. Choose his name from the list of customers

You probably would not like your customer to see all your other customers in one list. That’s why we built this feature too:

miniExtensions

Hide Linked Records in miniExtensions Form

A user can fill in just their email and the record will get linked automatically (if it exists). If it doesn’t exist, a new linked record will get created.

[Richard_Mast]

Great! Somehow I was not notified of this reply and had to find another solution.