Some entities in Infor CRM (Saleslogix), such as Tickets, use a “pretty key” as an easier to use value to reference the record. These pretty keys take a table ID value and then create a value like 000-00-001234. If you are creating an external application with this functionality, you’ll need to be able to create these pretty keys there as well. If you’re importing data via SQL, this SQL Function can be used to create the pretty key values inline.

Related Information

• Creating a Saleslogix Ticket Pretty Prefix and Suffix in External Applications
• SQL Stored Procedure to Create SalesLogix Table ID Values

Refer to the articles above for some ways to create pretty key values in C# or other types of applications. Also, creating table ID values in a SQL Stored Procedure can come in handy if you’re needing to also create pretty keys in T-SQL.

Let’s take a look at the SQL function to create pretty keys. This function takes a single parameter of the records ID value. It uses this to create the pretty key in the same way that the Infor CRM client does.

if exists (select * from sys.objects where object_id = OBJECT_ID(N'[sysdba].[GetPrettyKeySuffix]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function sysdba.GetPrettyKeySuffix
go
create function sysdba.GetPrettyKeySuffix 
(
    @id varchar(12) 
)
returns varchar(24) 
as
begin
    declare @key varchar(24)
 
    select @key
        = cast(id.digit1 as bigint)*power(36,0)  
        + cast(id.digit2 as bigint)*power(36,1)
        + cast(id.digit3 as bigint)*power(36,2)
        + cast(id.digit4 as bigint)*power(36,3)
        + cast(id.digit5 as bigint)*power(36,4)
        + cast(id.digit6 as bigint)*power(36,5)
    from
    (
        select 
              case
                  when substring(@id, len(@id)-0, 1) like '[0-9]' then substring(@id, len(@id)-0, 1)
                  else ascii(substring(@id, len(@id)-0, 1)) - ascii('A')+10
              end as digit1
            , case
                  when substring(@id, len(@id)-1, 1) like '[0-9]' then substring(@id, len(@id)-1, 1)
                  else ascii(substring(@id, len(@id)-1, 1)) - ascii('A')+10
              end as digit2
            , case
                  when substring(@id, len(@id)-2, 1) like '[0-9]' then substring(@id, len(@id)-2, 1)
                  else ascii(substring(@id, len(@id)-2, 1)) - ascii('A')+10
              end as digit3
            , case
                  when substring(@id, len(@id)-3, 1) like '[0-9]' then substring(@id, len(@id)-3, 1)
                  else  ascii(substring(@id, len(@id)-3, 1)) - ascii('A')+10
              end as digit4
            , case
                  when substring(@id, len(@id)-4, 1) like '[0-9]' then substring(@id, len(@id)-4, 1)
                  else  ascii(substring(@id, len(@id)-4, 1)) - ascii('A')+10
              end as digit5
            , case
                  when substring(@id, len(@id)-5, 1) like '[0-9]' then substring(@id, len(@id)-5, 1)
                  else  ascii(substring(@id, len(@id)-5, 1)) - ascii('A')+10
              end as digit6
    ) id
 
    if len(@key) < 6 set @key = right('000000'+@key, 6)
    return @key
end
go

The pretty key values created by this function will be identical to those created within the Infor CRM client. To use it you can simply add it inline, something like this:

update ticket set alternatekeysuffix = sysdba.GetPrettyKeySuffix(ticketid) where alternatekeysuffix is null

Download the GetPrettyKeySuffix SQL Function