Custom Report based on Requested Items to be Ordered

Here’s another report that someone might find useful.

I created a custom field for the Item Supplier Document that I called _part_no_url labelled simply URL. This field is of type LongText and I customized the Item Supplier Form to show the field “In List View” so that it appears on the Item Form in the Supplier Details section. In this field I paste the URL that points to the Supplier’s website for purchasing the item. If the part_no_url field is NULL then the report just show s the Supplier Part Number as regular text.

When you click on the Supplier Part on the report, the URL is opened in a new tab with a name referring to the Supplier. That way if you have multiple items to be ordered for a given supplier, it only opens one browser tab for the Supplier and any subsequent URLS for the same supplier replace the existing tab. This way you don’t end up with multiple tabs for each supplier. Just one. You can order your items one-by-one in the same tab this way.

select as "Material Request:Link/Material Request:120",
    mr.transaction_date as "Date:Date:100",
    mr_item.item_code as "Item Code:Link/Item:75",
    CONCAT("<a href=\"","/desk#Form/Item/",mr_item.item_code,"\">",mr_item.item_name,"</a>") as "Item Name::160",
    sum(ifnull(mr_item.qty, 0)) as "Qty:Float:100",
    sum(ifnull(mr_item.ordered_qty, 0)) as "Ordered Qty:Float:100", 
    (sum(mr_item.qty) - sum(ifnull(mr_item.ordered_qty, 0))) as "Qty to Order:Float:100",
    i.default_supplier as "Supplier:Link/Supplier:200",
    IF(ISNULL(i_supplier.part_no_url),i_supplier.supplier_part_no,CONCAT("<a href='", i_supplier.part_no_url, "' target='",i_supplier.supplier,"'>",i_supplier.supplier_part_no,"</a>")) as "Part::160"
    `tabMaterial Request` mr, `tabMaterial Request Item` mr_item, `tabItem` i
    left join `tabItem Supplier` i_supplier on (i_supplier.parent = AND i_supplier.supplier = i.default_supplier)

    mr_item.parent =
    and mr.material_request_type = "Purchase"
    and mr.docstatus = 1
    and mr.status != "Stopped"
    and = mr_item.item_code

group by, mr_item.item_code

    sum(ifnull(mr_item.ordered_qty, 0)) < sum(ifnull(mr_item.qty, 0))

order by i.default_supplier asc

Hope this report is useful for someone.