I have a social enterprise that make beanies on a buy one, give one model helping people in need and helping ease isolation for seniors . The seniors knit beanies in groups around the country and they get to share their skills, chat with friends and make beanies for those in need.
Beanies are sold online at www.makegivelive.com (shopify site) and the orders are pushed through to airtable.
The structure of the orders table is like this (but transposed)…
Product SKU | BobbieBeanieAdultCream
Sales Last 3 Months | 19
Sales Last 30 days | 2
In Stock | 4
In Production | 0
Ideal Stock | 10
Orders | 3 To Make | 9 (Orders + Ideal Stock) - (In Stock + In Production)
Allocated | 0
Balance | 0
The beanies that need to be made (To Make column), can be allocated to one of 11 groups around the country:-
In the example above, there are 9 BobbieBeanieAdultCream beanies to be made and these could be allocated to any number of groups, for example something like this
I’d like the ability for the user to select an unallocated product SKU, see how many need to be made, then allocate x number to a group, updating the ‘Balance’ fields in each table and also the To Make field when orders are allocated.
If Balance is zero, then the Product SKU is not available to be selected.
It would also be useful to be able to see, how many orders are being made by each group when allocating new orders so that orders can be distributed evenly.
Can someone help design the ideal table setup and process to achieve this please?