How can I obtain the current stock for the products from the database?

Support Developer
1

I am trying to do a report on Looker Studio, and one of the fields I need is the current stock for the products.
I tried to get the info from the table stock_inventory_line, but the data obtained isnt right. Can some one tell me how to get to the current stock?

2

The stock level in Tryton is not stored in the database but it is computed by the method products_by_location on product.product (it is also displayed as quantity field on the product following the context).
So if you want to retrieve this information from an external system, you must send RPC to the Tryton server.

3

If you can connect using looker studio to the database we use the following query to compute the stock of the products:

CREATE VIEW productos_stock (
    id,
    producto,
    codigo_producto,
    unidad,
    cantidad) AS
SELECT
m.product,
pt.name,
p.code,
Coalesce(pu.symbol, pu.name),
round(Sum(Case
    WHEN fl.type <> 'storage' and tl.type = 'storage' THEN m.internal_quantity
    WHEN tl.type <> 'storage' and fl.type = 'storage' THEN m.internal_quantity * -1
    ELSE 0 END
    )::numeric, pu.digits) as cantidad
FROM public.stock_move m
INNER JOIN public.stock_location fl ON fl.id = m.from_location
INNER JOIN public.stock_location tl ON tl.id = m.to_location
INNER JOIN public.product_product p ON p.id = m.product
INNER JOIN public.product_template pt ON pt.id = p.template
INNER JOIN public.product_uom pu ON pu.id = pt.default_uom
WHERE fl.type <> tl.type and m.state = 'done'
GROUP BY m.product, pt.name, p.code, pu.symbol, pu.name, pu.digits

Note that this view agreggates the quantities of all of the products for all the locations in the system and always reports all the moves done no mather the date when they are done.