I need a fairly advanced Airtable database set up and automation created based on the Airtable database for a subscription box company that delivers different items in each box based on customer preferences - no two boxes are alike.
Box Dimensions
Medium box: 27.9x21.5x14 cm (11x8.5x5.5 inches) volume: 8397.9 cm
Large box: 30x30x15 cm (12x12x6 inches) volume: 13,500 cm
Box types and cost of goods
- Regular - should fit in small box only, cost of goods is $10
- More Loot - should fit in small box only, costs of goods is $15
- Figurine, should fit in medium box but can also fit in large box if required. Cost of goods is $30 (this is the only box relevant to this job currently)
What I want to accomplish using Airtable, HubSpot, and external scripts (if required)
HubSpot contains all of our customer records and their item preferences. Airtable contains the database of all products that can go in our subscription boxes. Products have an associated cost to us, as well as their dimensions (Length by Width by Height, largest to smallest dimensions, respectively) along with product category and a unique item code.
When an order comes in for the Figurine box, I want Airtable (or a combination of Airtable + external script, can be run on our droplet) to generate a list of items from the customer title preferences synced from HubSpot that:
- Total the correct dollar value ($30, +/- 5%)
- Will fit in the dimensions of the Medium box (preferable) or Large box (if, for example, the length of the chosen figurine is too large for the Medium box). The system should always choose product quantity based on the cubic volume of the Medium box, only the figurine chosen should allow the system to choose the Large box
These items should ‘live’ in a customer record in a table for that month’s orders, populated with subscriber information (will be pulled in and synced from HubSpot CRM). (See the “January” table in the workspace with “Example Customer”). I should be able to pull up a customer record for that month and see what items the box will contain, and I should be able to pull up a customer and see every item in every box they’ve received. This will also be required to prevent the customer from ever receiving duplicate items. A running total of all items included in all boxes should also be created so that we know how many products to order, etc.
I also want to be able to use the script manually to generate boxes if we so choose. Perhaps this would be a web form of some type.
Rules the box creation process needs to adhere to
No more than one item from any one category should appear in a box, with some categories being an exception to this.
Also, subscribers should never receive the same item twice (for the lifetime of the subscriber, or in the same box). The item code must be checked to ensure this.
This is the process I’m looking for in a nutshell. There are additional nuances and additions to be made, so there will be more work upon successful project completion. You’ll probably have additional questions which we can cover on a call. Thank you!
