Create orders, group product SKU with different sizes and count orders

[JF_1234]

Hi!

I’ve been playing for quite some time now and just can’t get a result.

I want to use Airtable as an inventory management tool. In a table orders are automatically imported via the store. One row is automatically created per order. There I get the product with SKU and the associated size. My problem is that the SKU is the same for all sizes:

Product A Size 11 (SKU: 123456)
Product A Size 12 (SKU: 123456)

In a second table I have created the products and entered the stock level. Now I want the stock level to change automatically as soon as an order is received.

Maybe you can help me?

Thanks

[Kamille_Parks]

In the Order table create a Formula field that concatenates the SKU with the Size, much like you’ve done in the first screenshot (Stock table?).

Use an Automation to copy that formula into a Link To Record field that connects your Orders to your Stock table.

Since doing ^that will link your records together, you could then use Rollup fields in the Stock table to SUM or COUNT up all the orders for each unique SKU/size.

For your rollups to be useful, you’ll need to convert the appropriate fields into Numbers, not Single Line Texts as you have them (Stock, Grouped Sales, Sold, etc.)

[JF_1234]

Kamille_Parks:

Use an Automation t

It looks like it will work at first sight. But if you take a closer look, you will notice that a separate row has been created for each SKU+Size.

Have I made a mistake?

Stock Table

Order Table

[Kamille_Parks]

Your new screenshot of your Stock Table shows a Lookup field ({SKU+Size (from Orders)}). This seems superfluous.

JF_1234:

you will notice that a separate row has been created for each SKU+Size

That’s the idea. Its the same result as you showed in your first post, second screenshot but with the functionality to automatically count the orders and quantity ordered per unique item (SKU+size).

If you want separate stock counts by size, you need to have either:

1. One record per sku+size (your current setup)
2. One record per SKU with however many fields per size you offer, times three (one for stock you start with/add to, one for quantity ordered, one to subtract those numbers to get current stock)

Option 2 is bad database design and more difficult to maintain.