Apex Code Development You need to sign in to do that
Don't have an account?
Juan Romero Big Problem with Opportunity History here...
Hey guys,
I need to generate an aging report on opportunities. Specifically, what I need to do is provide an average of how many days all the opportunities have been on the current stages. For example I have an stage called "Initial Meeting". I have X number of opportunities currently on this stage, so I want to get an average on the number of days, so I could report that opportunities on this stage have been there for an average x days. This way we can know how long we are letting them sit there before they move on to the next stage.
The problem:
Since the opportunity history entity is not available through the XML RPC interface, the only way I could figure out for producing such numbers was to generate a report on the website, and create a local table from the results in Excel. Now this works great, until I get to the point where I need to filter certain opportunities out. When I do a join by the opportunity ID field I GET AN EMPTY RECORDSET???.... after carefull examination, I found out what the problem was: the XML RPC interface is producing 18 digit record IDs (Case unsensitive) where as the website is producing 15 character record ids..... bummer... all that work, and I cannot perform the final join.....
Is there a way around it?
Can the website produce 15 digit IDs?
Has anyone else achieved this before? If so, how did you do it?
Another issue I noticed.... there is no Stage Change Date field in opportunity history.... I am using the [last modified] date field hoping it will reflect when the record was created.....
Please let me know ASAP.... this report is very important for us, and I need to get it running as soon as possible.... Thanks!
I am able to generate the aggregates
Hi Juan,
You need to either remove the useCaseSafeIDs parameter from your query or set the useCaseSafeIDs to 0. I recommend removing the parameter as the logic for properly handling that parameter has been recently fixed and may not be in production at the moment.
The API will default to case-insensitive ids if you do not specify the useCaseSafeIDs. There is no option in reporting to specify the format of the IDs returned.
To convert from 18 to 15 character ids, you simply remove the trailing three characters.
Thanks for your response DevAngel,
What I am trying to accomplish is exactly the opposite. Since my SQL Server local installation is CASE INSENSITIVE, I need to convert my values from 15 to 18 characters rather than the opposite. The problem is that the only way I can get the opportunity history data is thrhough a report on Salesforce.com, and Salesforce returns 15 character IDs, which are useless for my 18 character SQL Server installation.
Is there any way to convert those values to 18 character IDs?
Is there a known algorithm to accomplish that, or is it just a random number that your database generates?
Is there a way I can perhaps request this from Salesforce?
Thanks!
Hi Juan,
Here is the scoop on converting 15 to 18 character ids.
All case-sensitive ids are 15 chars.
To convert a 15 char case-sensitive id to an 18 char case-safe id follow these steps.
1. Divide the 15 char into 3 chunks of 5 chars each.
2. For each character give that position a value of 1 if uppercase, 0 otherwise (lowercase or number).
3. Combine the bits from each chunk into a 5 bit integer where the rightmost bit is the most significant bit. This will yield a number between 0 and 31 for each chunk.
4. Constuct an array that contains the sequence of capital letters A-Z and 0-5.
5. Use the integer from each chunk to choose a character from the array.
6. Append the resulting 3 characters, in chunk order, to the end of the 15 char id.
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 | 1 |
|---------------|
|11011| 27 | 2 |
|---------------|
|11100| 28 | 3 |
|---------------|
|11101| 29 | 4 |
|---------------|
|11110| 30 | 5 |
|---------------|
|11111| 31 | 6 |
|---------------|