SQL relationships

ERPNext
1

Hey,

i want to join via sql quotation → sales order
sales order → sales invoice.

how is this done in erpnext?

regards and thanks

2

Hi there,

It’s not clear from your post where you want to join these tables. If you’re just looking to get the data, the new query builder is quite flexible.

https://frappeframework.com/docs/v13/user/en/api/query-builder

3

Hi,

sorry i want an custom report via query reports (Query Report).

But i can’t find the relation tables / linking attributes or anything to build relations from

  • quotation to sales order
  • sales order to sales invoice

regards and thx

4

Not sure if this helps but apparently the table tabSales Order Item has a field called prevdoc_docname. If you manually remove the value of that field using mariadb then Quotation will not be connected anymore.

5

It depends on the doctype, but most “forward” documents have a link looking back. Sales Invoices, for example, have a field called sales_order I believe (or something like that. I’m not in a position to check easily right now).

If you look at the doctype definition, you can usually figure out pretty easily how to do the joins. Generally, you’d be looking for “Link” type fields.

6

hey, thanks @all for help.

For anybody who needs them in the future, the easiest way to find the table with join:

  • i.e. Sales Order → Quotation
    1. Go to Sales Order List
    2. Filter: search for Quotation
    3. now you find “Quotation (Sales Order Item)” → Sales Order Item is the join table

as described by @ozner102 in sales order item you found the “prevdoc_docname” thats the link/join field. For Sales Invoice the same workflow (Sales Invoices Items → sales_order).

regards, and thanks

1 Like