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.


  1. Do I guess the ALTERNATEKEYPREFIX is set to ‘001-00’ in SQL update?

    • Typically yes. The only time that would be different is if the implementation had remote users that synchronized. Then each remote would have their own sitecode, which the prefix is a calculation of the sitecode. So, as long as no remotes, you can just safely use 001-00.

  2. Hola, supongamos que quiero aumentar en 1 el numero de ticket, lo paso a pretty y despues como lo vuelvo a clave para guardar en la tabla.

    • No funciona así para aumentar en 1 porque el número “pretty” del ticket se basa en el TicketID del ticket. Pase el ID a esta función y ejecute una instrucción UPDATE para guardar el valor devuelto en la tabla.

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!