SQL Function for Creating Pretty Key Values for Data in Infor CRM

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

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
create function sysdba.GetPrettyKeySuffix 
    @id varchar(12) 
returns varchar(24) 
    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)
                  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

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


Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!