Purchase/Sale history of a product

Following Need to find purchases by products

We also think it could be useful to know purchase/sale history of a product (to which suppliers a product was bought, when was last purchase, what was the purchase/sale price, the quantity, …).
Reporting don’t give all information at once.

So, wondering if a relate from product on purchase_lines/sale_lines (with some function fields from header for details) could help the user to find such information quickly? (and easy to implement)
Maybe this could be done with a new model based on a query for more efficiency ?

We did such add-on as a customization, but we think many users did the same, so why not adding this in sale/purchase modules?

Indeed it makes sense but it will be great to prevent to create lines from this view because we do not really have protection against adding lines to confirmed orders (or we should add such constraint).
Also I think we should try to reduce the number of relate on the product probably by using links for those that works only on 1 product. And also improve the discoverability by adding some relate also as links.

What details?

I do not see why we would need a table query model?

As relate is on lines, some fields are missing for having whole information at once: supplier/customer, sale_date, currency, number, reference, …

Don’t know. But to add missing details (see my previous answer) on the purchase_line/sale_line model, we add some function fields to get info from the header (like for state field). So maybe using a lot of function fields on a model could slow for other use?

Indeed it will be annoying to have to add many Function field on the line, mainly because it may prevent customization that would want to override the order value at the line level.
So probably having a dedicated model for that is not a bad idea and it will solve the creation issue.

I like the idea of a dedicated model.

We have also added the lines view as customization to allow answering such questions:

  • Which was the last products that I sold to a customer?

In such case we just have a dedicated view for the lines where the user can filter by customer, sale date or product. To allow to see the details.

For reporting, we can have a new level which shows the details from where the grouped amounts come for.

Just sharing some thougths

I’m just started thinking about the required field of such model.
Which will be the right name of such model? sale.history?

As starting field list I have:

Read from sale model:

  • company = fields.Many2One(‘company.company’, “Company”)
  • sale = fields.Many2One(‘sale.sale’, “Sale”)
  • currency = fields.Many2One(‘currency.currency’, “Currency”)
  • sale_date = fields.Date(“Sale Date”)
  • party = fields.Many2One(‘party.party’, “Party”)

I’m not sure if its worth to add the warehouse on default module or not.
On our use case the warehouse was not a decisive information.

Read from line model:

  • quantity = fields.Float(“Quantity”, digits=‘unit’)
  • unit = fields.Many2One(‘product.uom’, “Unit”)
  • product = fields.Many2One(‘product.product’, “Product”)
  • unit_price = Monetary(“Unit Price”, digits=price_digits, currency=‘currency’)
  • amount = fields.Function(Monetary(“Amount”, digits=‘currency’, currency=‘currency’), ‘get_amount’)

I prefered to have an amount field computed in python to ensure that the values are rounded to the currency, but not sure if it’s worth to do the math on the sql part.

I’m not sure if we need to include also the line description for the lines that do not have a product. Of course we will just include the lines of type line.

Feel free to share your toughts to what is missing or no can be improved.

I guess it should use actual quantity

Normally it should not trigger a new SQL read of the data so the overload will be only looping and make the computation. So a Function field in this case should be good enough.

I think we need also the description and summary because it can contain valuable information for business that mainly sale services and fill the description.

And also we should add a link to the actual sale line so it can be opened from there to see more details.
I think the relates should be from product and from party.

I think I asked for something similar here, as @maxx pointed out.

At the moment, my problem is that I need to check whether I invoiced all quarterly provision payments to my cooperation partners. Is there a smarter way than checking every single invoice?

I created Add relates to sale and purchase lines from party and product (!225) · Merge requests · Tryton / Tryton · GitLab