function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ascarl2ascarl2 

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?
 
Thanks in advance
 
-Andy

 



ascarl2ascarl2
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)

 
 
 
 
 


JakesterJakester
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:
 
 
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>

<style type="text/css">
body, td {margin:0px; color:#333;}
body {background-repeat: repeat-x; background-position: left top; font-size: 75%; font-family: 'Arial', 'Helvetica', sans-serif; background-color: #F3F3EC;}
a {color:#333;}
a:hover {text-decoration:underline;}
th {text-align: left; font-weight: bold; white-space: nowrap;}
form {margin:0px; padding:0px;}
h1, h2, h3, h4, h5, h6 {font-family: "Verdana", "Geneva", sans-serif; font-size: 100%; margin:0px; display:inline;}
.titleSeparatingColon {display: none;}
.bPageBlock {width:100%;}
.bPageBlock .pbSubheader {background-color:#222; color:#FFF; font-weight:bold; font-size: 91%; padding:2px 2px 2px 5px; margin-top: 3px; overflow: hidden; margin-bottom: 2px;}
.bPageBlock .pbBody {background-color:#F3F3EC;}
.bPageBlock .detailList {width:100%;}
.bPageBlock .detailList th, .bPageBlock .detailList td {vertical-align:top;}
.bPageBlock .labelCol {padding:2px 10px 2px 2px; text-align:right; font-size: 91%; font-weight: bold; color:#333;}
.bPageBlock .detailList .labelCol {width: 18%;}
.bPageBlock .dataCol {padding:2px 2px 2px 10px; text-align:left;}
.bPageBlock .detailList .dataCol {width:32%;}
.bPageBlock .detailList .data2Col {padding: 2px 2px 2px 10px; text-align: left; width: 82%;}
.bEditBlock .detailList .dataCol, .bEditBlock .detailList .data2Col {padding: 0 2px 0 10px;}
.bPageBlock .detailList .col02 {border-right: 20px solid #F3F3EC;}
.bPageBlock .detailList tr td, .bPageBlock .detailList tr th {border-bottom:none;}
.editPage .bPageBlock .detailList tr td, .editPage .bPageBlock .detailList tr th {border-bottom: none;}
.bPageBlock .detailList th.last, .bPageBlock .detailList td.last, .bPageBlock.bLayoutBlock .detailList tr td, .bPageBlock.bLayoutBlock .detailList tr th {border-bottom:none;}
.bPageBlock .detailList table td, .bPageBlock .detailList table th {border-bottom-style: none;}
.lead .tertiaryPalette {background-color: #EBAF59; border-color: #EBAF59;}
</style>
</head>
<body class="lead">
<div class="bPageBlock">
<div class="pbBody">
<div class="pbSubsection">
<table class="detailList" border="0" cellpadding="0" cellspacing="0">
<tr>
<td class="labelCol">Account ID</td>
<td class="dataCol col02"><script type="text/javascript">
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;
}
document.write(normaliseSforceID('{!Account.Id}'));
</script></td>
</tr>
</table>
</div>
</div>
</div>
</body>
</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!! 
 
 
 
 


ascarl2ascarl2
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