my customer is asking us to send the tax invoice and he has quoted a quotation number.
When I search the quotation number I can open the quotation document. in connections tab I need to go to Sales order then I need to open it then go to connections tab and then reach the sales invoice.
Since these are all inter connected quote>sales order> sales invoice >delivery note> and payment records. It will be good to keep them in connections tab in reference section of each document types. So this can reduce the number of navigations.
or is there any existing settings in the present version. Frappe dev team please consider this as a feature request. Is there anybody looking out for the same options.
You need to create a query report, find the ID in the report, and then open the document.
Here is a simple version of the query report:
SELECT
q.name AS "Quotation:Link/Quotation:200",
GROUP_CONCAT(
DISTINCT CONCAT(
'<a href="/app/sales-order/', so.name, '" target="_blank">', so.name, '</a>'
)
ORDER BY so.name SEPARATOR ', '
) AS "Sales Order::200",
GROUP_CONCAT(
DISTINCT CONCAT(
'<a href="/app/delivery-note/', dn.name, '" target="_blank">', dn.name, '</a>'
)
ORDER BY dn.name SEPARATOR ', '
) AS "Delivery Note::200",
GROUP_CONCAT(
DISTINCT CONCAT(
'<a href="/app/sales-invoice/', si.name, '" target="_blank">', si.name, '</a>'
)
ORDER BY si.name SEPARATOR ', '
) AS "Sales Invoice::200",
GROUP_CONCAT(
DISTINCT CONCAT(
'<a href="/app/payment-entry/', pe.name, '" target="_blank">', pe.name, '</a>'
)
ORDER BY pe.name SEPARATOR ', '
) AS "Payment Entry::200"
FROM `tabQuotation` q
LEFT JOIN `tabSales Order Item` soi
ON soi.prevdoc_docname = q.name
LEFT JOIN `tabSales Order` so
ON soi.parent = so.name
AND so.docstatus = 1
LEFT JOIN `tabDelivery Note Item` dni
ON dni.against_sales_order = so.name
LEFT JOIN `tabDelivery Note` dn
ON dni.parent = dn.name
AND dn.docstatus = 1
LEFT JOIN `tabSales Invoice Item` sii
ON sii.sales_order = so.name
OR sii.delivery_note = dn.name
LEFT JOIN `tabSales Invoice` si
ON sii.parent = si.name
AND si.docstatus = 1
LEFT JOIN `tabPayment Entry Reference` per
ON per.reference_name = si.name
LEFT JOIN `tabPayment Entry` pe
ON per.parent = pe.name
AND pe.docstatus = 1
WHERE q.docstatus = 1
GROUP BY q.name
ORDER BY q.transaction_date DESC;
