Hi everyone,
I'm getting an error with this query:
SELECT
t.tranId,
t.createdFrom,
t.custbody_bb1_mss_shipmentref as consignmentNumber,
t.custbody_bb1_mss_trackinglink,
t.custbody_lap_elogii_trck_link,
t.shipmethod,
CASE
WHEN t.custbody_bb1_mss_shipmentref IS NOT NULL
THEN BUILTIN.DF(t.custbody_bb1_mss_trackinglink)
ELSE BUILTIN.DF(t.custbody_lap_elogii_trck_link)
END AS tracking_url,
CASE
WHEN t.custbody_bb1_mss_service IS NOT NULL
THEN BUILTIN.DF(t.custbody_bb1_mss_service)
ELSE BUILTIN.DF(t.shipmethod)
END AS carrier_name,
tl.item,
tl.quantity
FROM ${recordType} t
JOIN transactionLine tl
ON t.id = tl.transaction
WHERE t.id = ${recordId}
The issue I'm having is with the line "THEN BUILTIN.DF(t.custbody_bb1_mss_trackinglink)".
If I return t.custbody_bb1_mss_trackinglink on its own (1) then what I see is probably an HTML link to the tracking information.
custbody_bb1_mss_trackinglink is a rich text field, a clickable link to the tracking page.
If I try to return this in my CASE WHEN statements without BUILTIN.DF then it throws an error, perhaps because it and custbody_lap_elogii_trck_link are not the same field types. But I'm not sure if that would help anyway.
Even if this did work, what it returns is 1 - a clickable tracking link.
What it returns with BUILTIN.DF is 2 - the display text of the link.
But what I need is 3 - the URL of the link.
Does anyone know if it's possible for me to extract the URL out of this Rich Text link using SQL?
Or should I perhaps try a different approach and workflow the HTTP link into a second custom field that I can then grab using the SQL? Again, how would I do this if it is a suitable approach?
