Help with query report - ifnull

Frappe Framework Customization
1

Hi Guys ,

I created a query report where I am linking my custom module with the standard sales order report , I want to return a field from my custom module on selecting a sales order , the report is working fine but it returns only the sales order for which I have created from custom module(product master) as I included this in the where clause ,

and tabSales Order Item.item_code = ifnull(pm.name,0)

here I want to return all the sales order even if pm is not created for the item in the sales order .Tried if null but it is not working ?

Any help ?

Thanks

2

It will be better if you share the full query. Probably you need to use CASE statement.

1 Like
3

Thanks for the reply @nabinhait . I did few changes to the report

select 
 `tabSales Order`.`name` as "Sales Order:Link/Sales Order:120",
 `tabSales Order`.`customer` as "Customer:Link/Customer:120",
 `tabSales Order`.`po_no` as "PO No:Data",
`tabSales Order`.`po_date` as "PO Date:Date", 
 `tabSales Order Item`.item_code as "Item:Link/Item:120",
`tabProduct master`.`dieno` as "Die no:Data",
`tabProduct master`.`grade` as "Grade:Link/Grade",
 `tabSales Order Item`.qty as "Qty:Float:140",
 `tabSales Order Item`.delivered_qty as "Delivered Qty:Float:140",
 (`tabSales Order Item`.qty - ifnull(`tabSales Order Item`.delivered_qty, 0)) as "Qty to Deliver:Float:140",
 `tabSales Order Item`.base_rate as "Rate:Float:140",
 `tabSales Order Item`.base_amount as "Amount:Float:140",
 ((`tabSales Order Item`.qty - ifnull(`tabSales Order Item`.delivered_qty, 0))*`tabSales Order Item`.base_rate) as "Amount to Deliver:Float:140",
 `tabBin`.actual_qty as "Available Qty:Float:120",
 `tabSales Order`.`delivery_date` as "Expected Delivery Date:Date:120",
 `tabSales Order Item`.item_name as "Item Name::150",
`tabSales Order Item`.date as "Del Date:Date:150",
 `tabSales Order Item`.warehouse as "Warehouse:Link/Warehouse:200"

from
 `tabSales Order` JOIN `tabSales Order Item` 
 LEFT JOIN `tabBin` ON (`tabBin`.item_code = `tabSales Order Item`.item_code
 and `tabBin`.warehouse = `tabSales Order Item`.warehouse),`tabProduct master`

where
 `tabSales Order Item`.`parent` = `tabSales Order`.`name`
 and `tabSales Order`.docstatus = 1
and `tabProduct master`.name = `tabSales Order Item`.item_code
 and `tabSales Order`.status not in ("Stopped", "Closed")
 and ifnull(`tabSales Order Item`.delivered_qty,0) < ifnull(`tabSales Order Item`.qty,0)
order by `tabSales Order`.transaction_date asc

here I need all the sales order even if product master is not created for an item .

Thanks

4

Try using LEFT JOIN for tabProduct master as well

1 Like