Limiting # reservations to the available quantity of an item - how?

Rasha · ‎Mar 21, 2019

Hi all! A newb here and this is a “practice project.” I did some digging around before asking – came up empty.

I’m setting up a base (modified Product Catalog template) with a bunch of personal items I hope to sell in preparation for a move. Idea is to embed the base (via Webflow, maybe table2site) to a site with a submission (type)form to streamline inquiries.

I’ve created a quantity (Qty) field on the main table for items where I have more than one available.

Two things I’d like to work out:

Give an interested buyer the ability to reserve 1 item (of which 2 are available) and have the available quantity be automatically adjusted to reflect that now, only 1 of 2 remains. Similarly, if I release that item from hold (manually) the available Qty reverts back to 2. Ideally, if 2 are sold, that record is removed as opposed to listed with Qty 0 (or a thumbs down in Available field - dunno how yet)
Prevent more than 2 reservations for an item with Qty 2.

A nice to have: An “expression of interest” field for items where all available Qty is reserved but not yet sold – a visible waitlist.

Thanks so much for any pointers!

Mac · ‎Mar 21, 2019

Rasha,

Let me be the first to welcome you to the community! Thanks for posting your use case!

I went about it a little bit differently than you described. But this may get you started. I did not format the base for Table2Site but that’s easy enough to do.

Okay, here is your “Buyer Form”

Reserve an item - Airtable

Fill out the "Reserve an item" form quickly and securely on Airtable.

From here a potential buyer can see all the available items for sale. When an Item is placed “on Hold” in this view then a filter on the “available” grid view removes that item from the view and the next would be purchaser can no longer see those items.

On the backend, you can see all of the items you wish to sell and their status. I made a grid view for each category: Available, On Hold and Sold.

You can copy this base into your space and play around with it.

If you use the Kanban view I created it makes for a nice visual way to move items in and out of status.

Hope these ideas help and good luck with your move!

Kamille_Parks · ‎Mar 22, 2019

Another setup example: https://airtable.com/shrQFx2Fkh8wJoyfh

This is a 3 table approach where individual items are listed in the [Stock] table, similar to @Mac’s approach, that will allow you to use a checkbox to release the item from a hold . Another table for [Reservations]. And a third table called [Items] grouping like stock items together. This will get you your automatically decreasing stock quantity field.

Mac · ‎Mar 22, 2019

@Kamille_Parks love it! Nice additions!

Rasha · ‎Mar 22, 2019

@Mac and @Kamille_Parks - thank you so much for the generous answer and taking the time. Quite the welcome :slightly_smiling_face: Going to take a close look at both approaches and circle back!

Rasha · ‎Mar 24, 2019

@Mac Interesting. I did not consider listing an item multiple times. Makes perfect sense!

Took a sec to sort out how a sold green lamp (record 3, Items for sale) would appear as 1 of 4 available items for reserving on the form, and a sold rug (record 3, Items for Sale) would show up in Buyer Hold: No name in Buyer Hold field next for Sold items.

Testing the form (airtable’s are very jumpy for me), I reserved an item but noticed when it populates in Items For Sale, a manual switch from Available to On Hold is needed. Adding a filter to exclude Sold items on Available Items view was, of course, an easy fix.

I’ve incorporated elements of your base and borrowed very heavily from @Kamille_Parks’s - again, thank you both.

Main modifications to Kamile’s base:

Stock table
Grid view: added If Sold field
Available view: Filtered out Sold
Added Reserved view to allow expressing interest in a Reserved item

Reservations Table
Linked Release Hold to Stock Table
Added Interested in Reserved

Note:
~ Customers / Line Items / Orders tables are carryovers from template I’m still fiddling with.
~ Master Gallery view is theoretically the one I’ll embed into site, but there are challenges (Status, Interested count) described below

Issues / Working on:
~ Right now, when an item is released from hold, it’s returned to inventory but the record is not removed from Reservations field in Stock table.

~ How to set up a Status field (currently a manual step - single select field on Stock and MASTER tables, not linked) in general, and so that: Instead of having an item (Qty 2) display as both Available and Reserved (see Gallery view, Master table) because 1 of 2 happens to be Available while 2 of 2 is Reserved, is (# + Status) format possible?

Qty 1 - item available: Status = 1 Available; Qty 1 - item reserved: Status = 1 Reserved
Qty 2 - items available: Status = 2 Available; Qty 2 - items reserved: Status = 2 Reserved
Qty 2 - 1 reserved, 1 available: Status = 1 reserved, 1 available

~ Master gallery (and website embed) view: Interested field which represents (# Reservations + # people interested in already Reserved) is useful/relevant only to Reserved items - how to hide for other Statuses. I.e. not display Interested (0) for an Available item.

~ How to remove a Sold item of which there are 2 (decouple) so it doesn’t appear in Website/Gallery views.

Neither view is optimal in the way items display when embedded. Grid limits fields displayed to 4, but with a cleaner presentation (rows) while the gallery is clunky.

A month from now a lot of this will seem obvious.