distinct count based on data AND upper case -v- lower case letters

[debbiepy]

I am trying to convert ID's into Unique IDs. SFDC give us the 15 character ID, but it could be identical to another ID except one letter may be Capitalized in one and not in the other. I cannto get Crystal reports to distinctcount using capital -v- non capital. So, I was given this instruction from salesforce.com to convert the 15 character id to the unique 18 character ID. Can Crystal REports handle this:

Example:
Convert 500x000000003TR to 18 character ID

|---------|---------|---------|
chunks | chunk 1 | chunk 2 | chunk 3 |
|---------|---------|---------|
chunk index |1|2|3|4|5|1|2|3|4|5|1|2|3|4|5|
|---------|---------|---------|
id string |5|0|0|x|0|0|0|0|0|0|0|0|3|T|R|
|---------|---------|---------|
string bits |0|0|0|0|0|0|0|0|0|0|0|0|0|1|1|
|---------|---------|---------|

|---------|
chunk index |5|4|3|2|1|
|---------|
chunk 1 bits = |0|0|0|0|0|
|---------|

|---------|
chunk index |5|4|3|2|1|
|---------|
chunk 2 bits = |0|0|0|0|0|
|---------|

|---------|
chunk index |5|4|3|2|1|
|---------|
chunk 3 bits = |1|1|0|0|0|
|---------|

chunk 1 index = 0 = A
chunk 2 index = 0 = A
chunk 3 index = 24 = Y

Resulting ID: 500x000000003TRAAY

Bit to integer to character map
From right to left

|--------------|
|Bits |int|char|
|--------------|
|00000| 0 | A |
|--------------|
|00001| 1 | B |
|--------------|
|00010| 2 | C |
|--------------|
|00011| 3 | D |
|--------------|
|00100| 4 | E |
|--------------|
|00101| 5 | F |
|--------------|
|00110| 6 | G |
|--------------|
|00111| 7 | H |
|--------------|
|01000| 8 | I |
|--------------|
|01001| 9 | J |
|--------------|
|01010| 10 | K |
|--------------|
|01011| 11 | L |
|--------------|
|01100| 12 | M |
|--------------|
|01101| 13 | N |
|--------------|
|01110| 14 | O |
|--------------|
|01111| 15 | P |
|--------------|
|10000| 16 | Q |
|--------------|
|10001| 17 | R |
|--------------|
|10010| 18 | S |
|--------------|
|10011| 19 | T |
|--------------|
|10100| 20 | U |
|--------------|
|10101| 21 | V |
|--------------|
|10110| 22 | W |
|--------------|
|10111| 23 | X |
|--------------|
|11000| 24 | Y |
|--------------|
|11001| 25 | Z |
|--------------|
|11010| 26 | 0 |
|--------------|
|11011| 27 | 1 |
|--------------|
|11100| 28 | 2 |
|--------------|
|11101| 29 | 3 |
|--------------|
|11110| 30 | 4 |
|--------------|
|11111| 31 | 5 |
|--------------|

 

[synapsevampire]

If you want quality help, post your software version, the database, and the connectivity used.

"I cannto get Crystal reports to distinctcount using capital -v- non capital."

File->Report Options->turn off the Database Server is Case-Insensitive

I made some assumptions here that may prove to require additional help because you decided not to post basic technical information, please take the time to do so in future posts.

-k

 

[Madawc]

There are also a LowerCase and UpperCase functions, at least in Crystal 10. Do a Disctinct Count on a formula field using one or other conversion.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10