Table Data Field - Add URL if value like 'INC*'

[nigelapthome]

Hi Folks

In my table I am trying to set the Link URL to create a link to our ticketing system if the value in field is LIKE INC

This is the syntax:

=if([DWH SNOW INC] LIKE 'INC*','https://servicemanagement.abc.net/nav_to.do?uri=task.do?sysparm_query=number= &' & ([DWH SNOW INC]))

It is working partially, in that the URL is created only if INC is present, but the URL formed has incorrect syntax - the special characters are being replaced:

https://servicemanagement.abc.net/nav_to.do?uri=task.do%3Fsysparm_query%3Dnumber%3D%20' & INC012345678

It should be this:

https://servicemanagement.abc.net/nav_to.do?uri=task.do?sysparm_query=number= &INC012345678

How can i trap out the special characters?

Thanks

Nigel

[Andrewmiah · Accepted solution]

Hii,

You can correctly generate the URL without special character replacements, you can use the HYPERLINK function along with the SUBSTITUTE function to handle special characters. Here's the corrected syntax:  

=IF([DWH SNOW INC] LIKE "INC*", HYPERLINK("https://servicemanagement.abc.net/nav_to.do?uri=task.do?sysparm_query=number=" & SUBSTITUTE([DWH SNOW INC], "'", ""), [DWH SNOW INC]))

In this formula, the SUBSTITUTE function replaces any single quotes with an empty string to prevent special character issues in the URL. The HYPERLINK function then creates the link using the modified URL and displays the [DWH SNOW INC] value as the link text.