Report Field Name Clickable & Directed to Particular Doc

In this below code, I want the ID row value should be clickable and it directed to that particular oppoertunity. I need that ID column clickable.

def execute(filters=None): 
    columns = [
        _("ID") + "::150",  # New ID column
        _("Enquiry Name") + "::150",
        _("Enquiry Owner") + "::150",
        _("Status") + "::150",
        _("Stage") + "::150",
        _("Probability") + "::150",
        _("Expected Closing Date") + "::150",
        _("Next Activity Date") + "::150",
        _("Closing Month") + "::150",
        _("Enquiry Amount") + "::150"
    ]
    
    # Define the SQL query
    query = """
        SELECT
            o.name AS `ID`,  -- Get the ID (e.g., CRM-OPP-2024-00016)
            o.customer_name AS `Enquiry Name`,
            u.full_name AS `Enquiry Owner`,
            o.status AS `Status`,
            o.custom_stage AS `Stage`,
            o.custom_probability__copy AS `Probability`,
            o.expected_closing AS `Expected Closing Date`,
            o.custom_next_activity_date AS `Next Activity Date`,
            CONCAT(
                CASE
                    WHEN MONTH(o.expected_closing) = 1 THEN 'January'
                    WHEN MONTH(o.expected_closing) = 2 THEN 'February'
                    WHEN MONTH(o.expected_closing) = 3 THEN 'March'
                    WHEN MONTH(o.expected_closing) = 4 THEN 'April'
                    WHEN MONTH(o.expected_closing) = 5 THEN 'May'
                    WHEN MONTH(o.expected_closing) = 6 THEN 'June'
                    WHEN MONTH(o.expected_closing) = 7 THEN 'July'
                    WHEN MONTH(o.expected_closing) = 8 THEN 'August'
                    WHEN MONTH(o.expected_closing) = 9 THEN 'September'
                    WHEN MONTH(o.expected_closing) = 10 THEN 'October'
                    WHEN MONTH(o.expected_closing) = 11 THEN 'November'
                    WHEN MONTH(o.expected_closing) = 12 THEN 'December'
                END,
                ' ',
                YEAR(o.expected_closing)
            ) AS `Closing Month`,
            o.opportunity_amount AS `Enquiry Amount`
            
        FROM
            tabOpportunity o
        LEFT JOIN
            tabUser u ON u.email = o.opportunity_owner
        WHERE
            o.expected_closing BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 MONTH)
            AND o.docstatus = 0
        ORDER BY
            o.expected_closing;
    """
    
    # Execute the query
    data = frappe.db.sql(query, as_dict=True)
    
    # Format the 'Enquiry Amount' with commas and 2 decimal places
    for row in data:
        row['Enquiry Amount'] = f"{row['Enquiry Amount']:,.2f}"
        # Make each ID clickable link to the enquiry
        row['ID'] = f'<a href="/desk#Form/Opportunity/{row["ID"]}">{row["ID"]}</a>'
    
    return columns, data, True  # Indicate HTML rendering

I hope the following code helps you achieve the anchor link in the script report.

def get_columns():
    return [
        
        {
            'fieldname': 'name',
            'label': 'Id',
            'fieldtype': 'HTML',
        },
...
]
def get_data(filters):
    conditions = get_conditions(filters)
    print("-------- get data ------------")
    print(conditions)
    sql_po = build_sql_po(conditions)
    sql_indent = build_sql_indent(conditions)
    sql_waste = build_sql_waste(conditions)
    sql_invcount = build_sql_invcount(conditions)
    full_sql = find_transtype_only_sql(conditions, sql_po, sql_indent, sql_waste, sql_invcount)
    print("-------- full sql ------------")
    print(full_sql)
    data = frappe.db.sql(full_sql, as_dict=True)
    data_with_anchor = formate_link_column_to_anchor_link(data)
    print('data_with_anchor')
    print(data_with_anchor)
    return data_with_anchor

def formate_link_column_to_anchor_link(data):
    print('format_link_column_to_anchor_link')
    domain_url = get_domain_name_of_the_site()

    for d in data:
        print(d)
        # d.update({})
        trans_type = d.trans_type
        name = d.name
        print(trans_type, name)
        formatted_url = formate_the_url(domain_url, trans_type, name)
        print(formatted_url)
        d.name = formatted_url

    return data

def formate_the_url(domain_name, trans_type, trans_id):
    part_url = get_url_path_based_on_trans_type(trans_type)
    farmate_part_url_with_id = f"{domain_name}/app/{part_url}/{trans_id}"
    anchor = f"<a href='{farmate_part_url_with_id}' target='_blank'>{trans_id}</a>"
    return anchor

@Syed_Ahamed try this

columns = [
_(“ID”) + “::150::Link/Opportunity”,
_(“Enquiry Name”) + “::150”,
_(“Enquiry Owner”) + “::150”,
_(“Status”) + “::150”,
_(“Stage”) + “::150”,
_(“Probability”) + “::150”,
_(“Expected Closing Date”) + “::150”,
_(“Next Activity Date”) + “::150”,
_(“Closing Month”) + “::150”,
_(“Enquiry Amount”) + “::150”
]

not woking.

@Syed_Ahamed check this


apply fieldtype according to this

1 Like