Hi,
I’m trying to make a custom report showing all Purchase Orders that have not been paid. I’ve started by making a “Script report” using Purchase Orders - but now need to do an Outer Join on Purchase Invoices and match them against the Purchase Order.
I’m seeking guidance on the join?
The script looks like this so far:
results = frappe.db.get_all('Purchase Order', ['*'], filters=filters)
columns = [
{
'fieldname': 'transaction_date',
'label': _('PO Date'),
'fieldtype': 'Date',
'width': 100
},
{
'fieldname': 'supplier_name',
'label': _('Supplier Name'),
'fieldtype': 'Data',
'width': 220
},
{
'fieldname': 'project',
'label': _('Project'),
'fieldtype': 'Data',
'width': 150
},
{
'fieldname': 'net_total',
'label': _('Total ex GST'),
'fieldtype': 'Currency',
'width': 150
},
{
'fieldname': 'grand_total',
'label': _('Total inc GST'),
'fieldtype': 'Currency',
'width': 150
},
{
'fieldname': 'status',
'label': _('Status'),
'fieldtype': 'Data',
'width': 150
}
]
## finally, we assemble it all together
data = columns, results
