Record Hyperlinks and Calculated Fields Containing Slashes in the Infor CRM LAN Client

The Infor CRM LAN client allows for a built in hyperlink functionality where you can specify a specific formatted link and the LAN client will open up that specific record.

So for instance, if you wanted to open a specific Contact record the special link would look like this:

slx:CONTACT/C6UJ9A000FRP

Recently one of our clients asked about building this link as a string calculated field. Normally you can do this by just putting together the static text and then adding the field to be merged in.

For a string calculated field that would look like this:
slx:CONTACT/|CONTACT.CONTACTID|

However when you actually try this calculated field it actually returns this:
slx:CONTACT//C6UJ9A000FRP

From what I can tell this is because the / character acts as an escape character and so the calculated field engine is doubling the character to //.

The LAN navigation link logic is a pretty simple text parser that looks for the first occurrence of / and then expects the next 12 characters to be the ID. In the case of the calculated field result, that results in a invalid ID because it returns “/C6UJ9A000FR” instead of “C6UJ9A000FRP”

To get around this the best approach is to create a SQL function instead to act as the calculated field. Normally for a calculated field you call the calculated field name to get the value. So if you had a calculated contact field called “Test” you would call:

select top 10 test from sysdba.contact

Instead, with the SQL function you would call the function name and pass in the expected single parameter, like this (remember functions need to have the schema name explicitally declared):

select top 10 sysdba.fnGetContactLink(contactid) from sysdba.contact

In the sample function it actually outputs the link as an HTML tag. If you don’t want that you can just change the function to the commented out line.

Here is the sample SQL function script:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sysdba].[fnGetContactLink]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [sysdba].[fnGetContactLink]
GO

CREATE FUNCTION SYSDBA.fnGetContactLink
(	
	@ContactID char(12)
)
RETURNS varchar(80)
AS
BEGIN	
	DECLARE @ResultVar as varchar(80)
	Declare @Name as varchar(128)

	set @Name = (select top 1 isnull(firstname,'') + ' ' + isnull(lastname,'') from sysdba.contact where contactid= @ContactID)
	--Plain Text:
	--set @ResultVar = 'slx:Contact/' + @ContactID
	--HTML Text:
	set @ResultVar = '<a href="slx:CONTACT/' + @ContactID + '">' + @Name + '</a>'
	
	RETURN @ResultVar
END
GO