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.)
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
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
Your new screenshot of your Stock Table shows a Lookup field ({SKU+Size (from Orders)}). This seems superfluous.
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:
- One record per sku+size (your current setup)
- 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.
