Duplicate check with link

[BrianI]

Hi, when entering a new lead I want to check for duplicates based on the lead's name. Then if a duplicate is found add a link to that record so the user can check if it is a real duplicate or just someone different with the same name.

I have create a calculated field with the following...

CASE
WHEN 
(SELECT COUNT(lead_id) FROM ns_tbl_leads WHERE full_name=target.[@field:first_name] + ' ' + target.[@field:last_name] ) > 0
THEN 
"<a href='lead.html?lead='>" + Duplicate + "</a>"
END

This works just fine.

The problem is how to insert the lead_id into the href of the anchor to create the correct link. You cannot concatenate a string in the href and nor do I know a way of building variables in the CASE statement within a calculated field.

 

Any ideas?

 

Thanks

[CoopperBackpack]

Hello @BrianI,

As I understand this is the Results page of the Report and the Calculated Field.

Please test these steps:

1) Use the following formula in the Calculated Field:
 

CASE
WHEN 
(SELECT COUNT(lead_id) FROM ns_tbl_leads WHERE full_name=target.[@field:first_name] + ' ' + target.[@field:last_name] ) > 0
THEN '<a href="lead.html?lead=' + '[@field:lead_id]' + ' ">Duplicate</a>'
END

Replace the [@field:lead_id] field with the correct field name if needed.

2) HTML is not rendered in the Calculated Field. So, you need to add an HTML block and add the Calculated Field as HTML there:

3) As the last step you can hide the Calculated Field by adding the styles into the Header. Disable the HTML editor before pasting the code.
Replace 3 with the position of your Calculated Field. In my example it is the 3rd field. 

<style>
form[action*='[@cbAppKey]'] tr.cbResultSetTableHeader th:nth-child(3),
form[action*='[@cbAppKey]'] tr.cbResultSetDataRow td:nth-child(3){
      display:none !important;
}
</style>

[BrianI]

Thanks for the reply. I think my issue was getting the combination ' and " in the right order.

It is actually on a submission form.

The calculated field renders the html without a problem.

The lead_id has to come from another SELECT query. So what I ended up with is...

CASE
WHEN 
(SELECT COUNT(lead_id) FROM ns_tbl_leads WHERE full_name=target.[@field:first_name] + ' ' + target.[@field:last_name] ) > 0
THEN 
'<a target="_blank" href="lead.html?lead=' + (SELECT TOP 1 (lead_id) FROM ns_tbl_leads WHERE full_name=target.[@field:first_name] + ' ' + target.[@field:last_name]) + '">CAUTION: Potential duplicate lead found!!</a>'
END

I will actually change this to point to a search form and pre fill the name in the search criteria as there could be more than one potential match.

Thanks again for the pointer!