This is my first post ever here, so I’m excited to start contributing and hopefully get some advice! I’ve been browsing the community for similar topics but couldn’t find anything similar so far.
Here’s a bit of context to begin with I buy pantry items from an online store once a month. For me it can be a pain in the neck to find items I’ve already bought and liked, save items I’ll want to try and shop later, all the while making sure they’re the right price and format, and that I’m ordering them in the right quantities based on the stock I currently have at home. So I created a base to keep track of my current stock, what I bought in the past and how I liked it, the goal being to ease the decision process and spend less time browsing the store’s website and comparing items.
Now in my base, I have quite a lot of tables, but the following 3 are those most relevant to my question:
- Products on the market: actual products I can buy from the online store, which I usually save using the Web clipper extension
- Shopping carts: carts/orders with all items bought with their quantity, price when the order was placed, subtotal and total amounts
- Price tracker: though prices are not fixed and can change over time on the store, I want my cart total amounts for previous orders in the “Shopping carts” table to stay the same, so I need to keep track of previous and current item prices
You can picture my “Products on the market” table as supermarket isles with multiple categories along with items I can pick from and add to my cart. So from my “Products on the market” table, I’d like to have some sort of “add to cart” feature, similar to what you get when you’re on a product page on Amazon with the dropdown menu to choose quantity from and the add to cart button.
When I click that button, I’d like to have a new record created in my “Shopping carts” base, showing the quantity I selected for that item and the subtotal based on the latest price I recorded for that item in my “Price tracker” table. When item picking is complete, I would go ahead and check my order from the “Shopping carts” base and maybe adjust quantities or remove items before actually placing it from the online store.
Can you see what I’m trying to achieve here and does that sound feasible to you? I’m not sure if that’s something I could do using a button field to recreate an online shopping experience - that would probably be possible with a Run script action, but I’m not comfortable enough with scripting to leverage that so if you are, please let me know what you think! Or are my tables ill-designed and I am overcomplicating the whole thing? Curious to hear your thoughts about that
What seems difficult to handle to me is the fact that I need to have a temporary quantity field in my “Products on the market” table that would need to be reset after the cart is submitted and the order is placed. Also it feels like I might be duplicating stuff between the “Shopping carts” and “Price tracker” tables and not leveraging linked records as far as I could. Not sure what the right way to do that is, or maybe that’s too convoluted for Airtable to manage…
I’ve also looked into Automations features to automate the creation of a record in the “Shopping carts” table whenever I want to add a new item to the cart. However it sounds very limiting and doesn’t easily support cases where I would add an item to the cart, then change the quantity to a higher value, or all the way down to 0 if I finally want to remove the item from the cart.
Thanks a bunch!