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
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
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.
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.