Hi
I am busy creating a custom report to report certain statusses on one report. The report seem to be
correct except that, the “sales order” number is only in the first row of a given “set” ?
Hope that makes sense.
Let me draw up a small table to illustrate
This is what I am currently getting
MFG-PP-00001 , ACC-SO-00001 , MFG-WO-00001 , MFG-JC-00001 , Completed
MFG-PP-00001 , ( blank ) , MFG-WO-00001 , MFG-JC-00002 , COmpleted
MFG-PP-00001 , (blank ) , MFG-WO-00001 , MFG-JC-00003 , Compelted
I would like to get this
MFG-PP-00001 , ACC-SO-00001 , MFG-WO-00001 , MFG-JC-00001 , Completed
MFG-PP-00001 , ACC-SO-00001 , MFG-WO-00001 , MFG-JC-00002 , COmpleted
MFG-PP-00001 , ACC-SO-00001 , MFG-WO-00001 , MFG-JC-00003 , Compelted
My code looks like this…
def get_results(filters):
pp = frappe.qb.DocType("Production Plan")
wo = frappe.qb.DocType("Work Order")
jc = frappe.qb.DocType("Job Card")
query = (
frappe.qb.from_(pp)
.inner_join(wo)
.on(wo.production_plan == pp.name)
.left_join(jc)
.on(wo.name == jc.work_order)
.select(
pp.name,
wo.sales_order,
wo.name,
wo.status,
jc.name,
jc.status,
)
)
return query.run()
results = get_results(filters)
columns = [
{
'fieldname': 'pp.name',
'label': _('Productin Plan Name'),
'fieldtype': 'Link',
'options': 'Production Plan',
'align': 'left',
'width': 200
},
{
'fieldname': 'wo.sales_order',
'label': _('Sales Order'),
'fieldtype': 'Link',
'options': 'Sales Order',
'width': 100
},
{
'fieldname': 'wo.name',
'label': _('Work Order'),
'fieldtype': 'Link',
'options': 'Work Order',
'width': 200
},
{
'fieldname': 'wo.status',
'label': _('WO Status'),
'fieldtype': 'Data',
'align': 'left',
'width': 100
},
{
'fieldname': 'jc.name',
'label': _('JC Name'),
'fieldtype': 'Link',
'options': 'Job Card',
'width': 200
},
{
'fieldname': 'jc.status',
'label': _('JC Status'),
'fieldtype': 'Data',
'align': 'left',
'width': 100
},
]
data = columns, results, message
Would appreciate some assistance to make this look better.
Thank you