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
ryan_mmryan_mm 

Something strange with the OpportunityHistory table

Hi folks - We found an Opportunity record that has a set of cooresponding OpportunityHistory records with something strange: there are two OpportunityHistory records with the exact same CreatedDate (including seconds) but the one that comes back second when querying and doing an ORDER BY CreatedDate doesn't seem to have the right values. It looks something like this:

Opportunity Table
ID                  StageName
00640000009sJMSAA2  Proof of Concept

OpportunityHistory Table
ID                 OpportunityID      CreatedDate          StageName
0084000000GIjuzAAD 00640000009sJMSAA2 2008-06-10 4:31:48PM Proof of Concept
0084000000GIjvCAAT 00640000009sJMSAA2 2008-06-10 4:31:48PM Demo Scheduled 

As far as we know the user just clicked the Save button once and had changed from Demo Scheduled to Proof of Concept. Shouldn't the last record in the OpportunityHistory table ALWAYS match the current value of the cooresponding opportunity record?  At first we thought that the two OpportunityHistory records were out of order maybe due to a rounding error on the time or something. But the IDs seem to the imply that the second record was in fact created last because it has a "larger" ID. So we're confused.

This is messing up our calculations because our logic that looks through the OpportunityHistory table determines that this Opportunity is now at the Demo Completed stage which is wrong.

Any ideas on why this would happen or how to work-around it?

Ryan
Ron HessRon Hess
does sound strange, one point about the id's the last three characters are a checksum, so they must be discarded

then you get
0084000000GIjuz
0084000000GIjvC

and the ascii value of z is 122
ascii value of C is 67

so i would conclude that 'C' < 'z'

so, if the dates don't resolve order completely you can try truncate to 15 chars and then sort by ascii. 

i have no idea how this gets created at the same time unless there is a workflow or trigger helping the process along...
ryan_mmryan_mm
Thanks Ron. I has assumed the last 3 characters were special but it's good to know that they are a checksum. I will try chopping off the last three chars for the purpose of sorting but I'm not sure that will resolve the issue. Maybe I am looking at this the wrong way but I was reading the OpportunityHistory's ID left to right. So the 'u' and the 'v' are the determining characters for ordering. And if that is correct then that means the the second record was in fact created after the first. Which means that the last record in the history table does not match the current value of the opp.

Ryan
Ron HessRon Hess
oops, you are correct u < v, i missed that...