URLs in Custom Query Report

ERPNext Reports
1

I’ve got a custom query report that displays all requests to be purchased with the default supplier and part number.

I’ve added a custom field to the “Item Supplier” table (via custom fields) called part_no_url which is a URL pointing to the item on a supplier’s webpage.

Is there any way to make the URL clickable on the report?

2

You mean Script Report or Query Report ?

You can add this to your query report:

CONCAT("<a href='http://", <table_name>.part_no_url, "' target='_blank'>[insert hyperlink text]</a>") as "Link::100"

If you want it script report, you can ditch the part after the keyword as.

In <table_name>, replace by the correct table name i.e. `tab.Item` or the relevant table
Replace [insert hyperlink text] by the text you want in the link.

http is important else your link will be appended to your erpnext site and lead to a dead page.

2 Likes
3

Thank you! I was on the right track, just was didn’t get the right mix of single vs double quotes.

Here’s how it ended up in my SQL Query to show the supplier part number as a link if the supplier_part_url was populated:

IF(ISNULL(i_supplier.part_no_url), i_supplier.supplier_part_no, CONCAT("<a href='", i_supplier.part_no_url, "' target='_blank'>",i_supplier.supplier_part_no,"</a>")) as "Supplier Part::160"

3 Likes
4

You did the correct thing by using ISNULL. This is the correct answer !