Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
ascarl2

# Help - displaying 18 character ID on accounts and reports

Hello,

Someone must have had this problem in the past...

Salesforce ID’s are upper and lower case.  And they can

have the same number/letter combo but they treat upper and lower case letters
different.  When we bring these into our accounting system (Great Plains)
they make them all upper case letters.  Therefore 2 customers may have the
same ID # in Great Plains.

All we need is a unique ID for each account but the problem

is we have already used up all 3 auto number external id fields.

The other postings regarding this problem were helpful

however we need ours to show up in a report and not be solved through the excel
connector.  I thought we could just build a formula field to display the
18 character (in case sensitive) ID but the trouble is the formula is too
large. Here is the code:

Id &
Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ",
16 * IF(LOWER(Mid(Id,0,1)) != Mid(Id,0,1),1,0) +
8 * IF(LOWER(Mid(Id,1,1)) != Mid(Id,1,1),1,0) +
4 * IF(LOWER(Mid(Id,2,1)) != Mid(Id,2,1),1,0) +
2 * IF(LOWER(Mid(Id,3,1)) != Mid(Id,3,1),1,0) +
1 * IF(LOWER(Mid(Id,4,1)) != Mid(Id,4,1),1,0),1) &
Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ",
16 * IF(LOWER(Mid(Id,5,1)) != Mid(Id,5,1),1,0) +
8 * IF(LOWER(Mid(Id,6,1)) != Mid(Id,6,1),1,0) +
4 * IF(LOWER(Mid(Id,7,1)) != Mid(Id,7,1),1,0) +
2 * IF(LOWER(Mid(Id,8,1)) != Mid(Id,8,1),1,0) +
1 * IF(LOWER(Mid(Id,9,1)) != Mid(Id,9,1),1,0),1) &
Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ",
16 * IF(LOWER(Mid(Id,10,1)) != Mid(Id,10,1),1,0) +
8 * IF(LOWER(Mid(Id,11,1)) != Mid(Id,11,1),1,0) +
4 * IF(LOWER(Mid(Id,12,1)) != Mid(Id,12,1),1,0) +
2 * IF(LOWER(Mid(Id,13,1)) != Mid(Id,13,1),1,0) +
1 * IF(LOWER(Mid(Id,14,1)) != Mid(Id,14,1),1,0),1)

Any ideas on the code or formula field work around?

Are there any other options?

-Andy

ascarl2

my engineer help also supplied me with this code - which is also too large:

Id & MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345", IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,0,1)),16,0) + IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,1,1)),8,0) + IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,2,1)),4,0) + IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,3,1)),2,0) + IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,4,1)),1,0),1) & MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345", IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,5,1)),16,0) + IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,6,1)),8,0) + IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,7,1)),4,0) + IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,8,1)),2,0) + IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,9,1)),1,0),1) & MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ012345", IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,10,1)),16,0) + IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,11,1)),8,0) + IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,12,1)),4,0) + IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,13,1)),2,0) + IF(CONTAINS("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Mid(Id,14,1)),1,0),1)

Jakester

The way we did it was with an s-control. It won't show in reports, but it will show it on your page layouts. First, Setup-->Develop--S-Control, then click New, then put in:

function normaliseSforceID( id) { // fluff up a 15 char id to return an 18 char id if (id == null) return id; id = id.replace(/\"/g, ''); // scrub quotes from this id if (id.length != 15) { //print('well, id is not 15, bye' + id + ' ' + id.length); return null; } var suffix = ""; for (var i = 0; i < 3; i++) { var flags = 0; for (var j = 0; j < 5; j++) { var c = id.charAt(i * 5 + j); if (c >= 'A' && c <= 'Z') { flags += 1 << j; } } if (flags <= 25) { suffix += "ABCDEFGHIJKLMNOPQRSTUVWXYZ".charAt(flags); } else { suffix += "012345".charAt(flags-26); } } return id + suffix; }

Next, make a second s-control that's specific to show the 18 character for your object. In this case, Account, so call it something like "Account ID 18 Digit" and make it HTML:

Drop that s-control onto your page layout, and you can see it, but you can't include it in reports :-(

The way I get the 18 digit IDs en masse is using the fantastic DB Amp product. It requires having a local SQL database, so as long as you have one, it rocks!!

ascarl2

thanks jakester - I may have to just accept the fact I won't be able to view in reports.

i'll also have to take a look into that tool - thanks again

-ascarl2