I need to create a product stock managing app

[renteaser]

Good morning everyone:

I need to create an app that allows to manage inventory of a product (in my case they are appliances). Here’s how I have set up my database:

This first picture is the data type “Appliance Catalogue” which saves the information of the different types of appliances that I am selling.

This second picture is the data type “Appliances inventory” which saves every specific appliance from every type of appliances that I have to sell.

To give you an example, imagine that I have a fridge type A in the appliance catalogue database and that I display that catalogue in the catalogue page for users to buy it. But in fact, I have 3 fridges type A that I can sell, which are stores in the appliance inventory database. I need to keep track of where every item is so I know if it is sent etc. Every item in the appliance inventory database has associated an item in the appliance catalogue database. So appliance catalogue to appliance inventory is a 1-many relationship and appliance inventory to appliance catalogue is a 1-1 relationship.

Here is my catalogue page:

From there, a user selects with the dropdown in the right upper corner how many of each type he wants to buy and by clicking in a confirm button the workflow associates the quantity selected with the amount of appliance inventory of each type and associates to each appliance inventory item the user. Again, an example. If I select X amount of fridge A, the workflow should look in the inventory database for appliance with the same name as the appliance catalogue and associate X amount of them to the current user.

I guess this is a simple stock managing feature and that every ecommerce must to do it this way.

Cannot wait to hearing your responses

[gazinhio]

So your inventory type is kind of like serial number tracking, in that each record is for a specific physical item. What you need to do, I gather, is to work out how much stock/inventory you have of each appliance based on the records in the inventory type? You should then be able to run a simple count on the inventory type for a specific appliance to give you the quantity in stock. The real complexity is that you’ll need to allocate/assign a physical item/inventory record to a customer to know where it’s going to. Also, as an example, have a flag (yes/no field) on the inventory type to say whether it’s been allocated to a customer. Then when you’re running your count to determine the quantity of stock you have available for an appliance you can add a constraint to it to filter out the inventory that’s already assigned/allocated.

In my experience in developing serial number tracking inventory systems, the front-end eg order entry or the ecommerce site doesn’t want to get involved in selecting specific serial numbers, it needs to be a back-office/warehousing process so the complexity comes in making sure you allocate overall stock at the point of order processing before allocating specific inventory and then, at the dispatching stage enforcing the selection of specific inventory to dispatch and move out of stock. Order processing/fulfilment systems can get pretty complex if you’re not careful, just don’t get lost in the weeds.

I’m not entirely sure what answers you are specifically looking for but I hope that gives you a few pointers.

[renteaser]

Thank you for you response @gazinhio!

That is how I have done my thought process. The problems comes when I try to create that workflow in Bubble.

In the catalogue showing page, I have established that if search for appliance inventory with name=catalogue name with constraints owner is empty :count = 0, then you cannot choose that appliance and a text “not available” shows up. To keep things simple I am restricting the amount of each appliance that you can select to 1. So that first part is done.

The second part, as you said, is the complicated one. Now I need to assign, to the appliances from the inventory database, as the owner the current user. But only to those appliance whose appliance catalogue have quantity > 0 and only to one of them (:first item??) since quantity is restricted to 1 as maximum value.

Any idea on how to do this?

[gazinhio]

To get a list of appliances to display the stock quantity and ignore those with no inventory records I would do the following as the Data Source for the repeating group and then you get at the quantity and the details of the inventory item. This has the added benefit of completely ignoring appliances with no stock (no inventory records).

Just be a bit careful with performance on this one, grouping are optimised by Bubble but it might be better to have a “Stock Quantity” field on the appliance that get’s updated when an inventory item for it is moved in or out, you can use a back-end DB trigger to do this. You can then just display a straight list of appliances without the need for grouping and aggregation and put a constraint on Stock Quantity > 0. With bubble’s database I have found it’s always better to optimise for reads over writes and ensure your reads (queries, searches etc) are as performant as possible.

Hope that helps.

[gazinhio]

If you wanna have a look at that I’ve put the project link below (I’ve spelt appliance wrong btw )

Bubble Editor - Ncc-accounts-test