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
damo2929damo2929 

Accessing opportunity field history via soql

 

Hi all am wanting to do some math to generate times an opportunity has been in each state, fo this we have opportunity field tracking enabled and tracked.

 

 

now what I have been asked to develop is a system which now read the field history and back fill custom fields in the main opportunity for how long the opportunity is in each stage.

 

 

now I can generate a report on the reports engine for this table but am not able to see the object. anybody know how to access this information via apex ?

 

the fields am after from the table are

edit date

old value

new value

field event

stage duration

parent ID

 

ordered by edit date if possible.

 

so that I can get them into an array so that I can itterate though them to populate fields with the time at each stage.

Cory CowgillCory Cowgill

You can query the OpportunityFieldHistory object to get most of this information. For example, all the fields available on the Oppty Field History are:

 

Select o.OpportunityId, o.OldValue, o.NewValue, o.IsDeleted, o.Id, o.Field, o.CreatedDate, o.CreatedById From OpportunityFieldHistory o

 

 
BobChathamBobChatham

How would I pull related fields from the parent Opportunity in the same query (e.g., Amount, Name)? I'm having difficulty formulating the SOQL for this relationship. For example:

 

Select op.Id, op.Name, op.Amount, op.StageName, op.Probability, op.LastModifiedDate, (Select ID, Field, OldValue, NewValue from OpportunityFieldHistory) from Opportunity op where op.LastModifiedDate > 2011-07-01T00:00:01z

 Thanks

Bob
Cory CowgillCory Cowgill

You would do this query:

 

Select o.CloseDate, o.Amount, o.AccountId, (Select OldValue, NewValue From Histories) From Opportunity o

 

If you have the Force.com IDE, you can use the Schema Browser to generate queries easily. Take a look at Opportunity, then expaned the "Children Relationships" portion.
BobChathamBobChatham

Thanks, this worked and is a much better way to develop queries. Is there a way to turn a SOQL query into a report on the Reports tab? Looks like this is still on the wishlist...

 

Barring this, is there some way to drop the SOQL query results directly into an Excel worksheet? After poking around in the Excel Connector code, it seems to me that it's limited to querying tables (i.e., can't take a SOQL query as input). Is there some way that you're aware of to accomplish this?
WizradWizrad

Cory is a mastermind so I dont want to steal his thunder, but the way to do this is use Data Loader and perform an export.  You can export based off a query and the results will be in CSV format.
Cory CowgillCory Cowgill

Wizrad! How dare you attempt to steal my thunder! :) Hope all is well at MM. :)

 

Wizrad is right, you can also use DataLoader depending on your end goal.

 

Depends on your end goal. Are you trying to build a report or custom VF component or whatever. Not sure, but Dataloader is a second option. :)
Ashish Kumar YadavAshish Kumar Yadav
Hi All,
Can anyone help me to how to write subqueries to fetch the record from opportunityfieldhistory new value and old value.
find below queries-
SELECT Count(ID),SUM ( HardwareMValue__c) Amount_Added1
FROM Opportunity where Hardware__c = true and and CreatedDate = today
and OppHis.FromStageName = 'Prospect' and  oppHist.Tostage = 'Close Dropped'