Stock Management

[thomas_avice]

Hello to all,

I am looking to create a database that will allow me to manage multiple products.

I have a first table “inventory” which lists the different products that I offer:
ID1 product 1 quantities nb ordered nb sold
ID product 2 quantities nb ordered nb sold
…

Then I have a sales table that references the different sales. Is it possible to make a dynamic follow-up of the stocks with orders including different products with different quantities? example of order:
product 1 *3
product 2 * 4
product 3 *1

I would like to have several quantities on the screen below

[Alessio_Monino]

hi @thomas_avice,
to achieve what you are planning you would need three tables instead of two:

• products
• sales
• sales line items

STRUCTURE CHANGE: The sales line items would be the table linking products and sales and would contain several records, each matching a specific product to a specific sale. These records could then contain several fields among which one of them could be the quantity of the product in that sale (but there could be many others including shipping status, product condition, priority, etc.)

VISUALIZATION: To visualize all the products in each sale in a friendly, intuitive manner you could use a view in the sales line items table grouped by sale, so that for each sale you have a group containing all the products related to it

Hope this helps!

Website: alessiomonino.com
Calendly: Calendly - Alessio Monino
Email: alessio.monino@gmail.com

[thomas_avice]

Hi Alession,

Thank you for your feedback.

If I understand correctly, with this way of working I have to create a row in the “sales line items” table for each product purchased during an order?

This is what I wanted to avoid. I would like to be able to create a single line with all the products purchased with their quantity and to be able to track the stock directly.

Is this not possible according to you?

[Alessio_Monino]

hi @thomas_avice,
you can achieve what you are mentioning by having, for example, for each product a column with the product and one with the quantity (in a Product 1, Quantity Product 1, Product 2, Quantity Product 2 format). However, I would strongly discourage you from opting from such a solution because it reduces significantly the functionality of your system, preventing roll-ups, counts, averages, etc. at a sales level. Moreover it makes any automations for line items much harder and messy.

Hope this helps!

Website: alessiomonino.com
Calendly: Calendly - Alessio Monino
Email: alessio.monino@gmail.com

[thomas_avice]

Thank you for your feedback. I will try both solutions and get back to you.

Have a nice day