Hi,

We want to use SOQL to get all Leads whose status changed from A to B in a time window. My original idea is query LeadHistory and filter by OldValue and NewValue.
 

select fields(standard),Lead.Id, Lead.something from LeadHistory where Field='Status' and OldValue='Working - Contacted' and NewValue='Closed - Converted'

And then the query failed and I realized that OldValue and NewValue are not filterable.

The only workaround I can figure it out is to only condition on Field and use our downstream tasks to filter OldValue and NewValue.

select fields(standard),Lead.Id, Lead.something from LeadHistory where Field='Status'

But if a Lead changes status a couple times in the time window, the downstream tasks will see the same Lead multiple times. And it adds more burden on us to generalize the solution to more objects (such as Oppurtunity vs. OppurtunityFieldHistory).

I wonder if there is an eaiser way to get all Leads whose status just changed from A to B through SOQL?

Thank you!

Hi,

We want to use SOQL to get all Leads whose status changed from A to B in a time window. My original idea is query LeadHistory and filter by OldValue and NewValue.
 

select fields(standard),Lead.Id, Lead.something from LeadHistory where Field='Status' and OldValue='Working - Contacted' and NewValue='Closed - Converted'

And then the query failed and I realized that OldValue and NewValue are not filterable.

The only workaround I can figure it out is to only condition on Field and use our downstream tasks to filter OldValue and NewValue.

select fields(standard),Lead.Id, Lead.something from LeadHistory where Field='Status'

But if a Lead changes status a couple times in the time window, the downstream tasks will see the same Lead multiple times. And it adds more burden on us to generalize the solution to more objects (such as Oppurtunity vs. OppurtunityFieldHistory).

I wonder if there is an eaiser way to get all Leads whose status just changed from A to B through SOQL?

Thank you!