Base structure suggestions for stock inventory and custom orders

[Ashley_Jackson]

Hi all, I'm a fairly proficient AT user but I just can't wrap my brain around this current project. I make and sell jewelry pieces. I do a combination of "stock" items and custom items. For stock items I'll make 5-10 identical pieces that I sell as-is. But then there's the custom work. I want to create a comprehensive system that calculates MSRP for both stock and custom pieces, tracks orders and inventory, and tells me what stock I have currently on-hand.

I can't for the life of me figure out how to structure the base to be able to handle both stock items and custom orders. Stock items would record details like size, color choices, weight, etc. But they are set per item. On custom orders those details are set per order, not per item.

I've been toying with a base structure something like:

• Products Table for recording stock items
• Orders Table for recording custom items

The Orders table would link to the products table to pull in base information, but all the products would have set details already in place.

Does any of this make sense? lol

I would love to hear how you guys would tackle something like this! TIA!

[Hannah_Wiginton]

You'll need a 3rd table. You'll have your Products table, Line Items table, and Orders table.

In a restaurant, you have your main "Products", like Steak, Grilled Chicken, etc.

Think of Line Items like Line items on a receipt. A table of people order 3 Steaks. That's your line item.

The Steak is your actual Product and it's where you can put the Quantity. You link it back to your Steak product and you also link it to your Order table (for the custom receipt)

So your Product will be linked to your Line Items. Line Items will be linked to your Orders.

Here's a base template that might help you see the layout - Ecommerce Airtable template