+ Start a Discussion
AncaDAncaD 

How can I query the history table for a custom object?

I have a custom object "Test" from which I need to select the records for which a field "Status" of the object has a value 'x' and was changed to this value in a specified date range (previous month from the current month). For this I need to query the history table for that object, to find out when was field "Status" changed to value 'x', and see if the date is in the specified range. I've tried various ways to write the SOQL for this, but none is working.

 

What I need should look similar to this:

 

Select a.Last_Name__c, a.First_Name__c, (Select Id, ParentId, CreatedDate, Field, OldValue, NewValue From Histories where Field = 'Test' and NewValue = 'x' and CreatedDate >= 2009-08-01T00:00:00.000Z and CreatedDate < 2009-09-01T00:00:00.000Z) From Test a where a.Status__c = 'x'

 

 

Any suggestions are appreciated

 

Thank you

Best Answer chosen by Admin (Salesforce Developers) 
CaptainObviousCaptainObvious
Try removing the and NewValue = 'x' filter. I remember trying a similar query in the Apex Explorer and receiving a message like "NewValue' can not be filtered in query call".

All Answers

CaptainObviousCaptainObvious
Try removing the and NewValue = 'x' filter. I remember trying a similar query in the Apex Explorer and receiving a message like "NewValue' can not be filtered in query call".
This was selected as the best answer
AncaDAncaD
I actually need the NewValue field's value - I would need it in a Where clause, but I can't use it in there. Is there any way to capture the value of the NewValue field in a query?
skyfjskyfj

Dear AncaD:

      I'm sorry to can't help you.But I think you maybe  help me, So I commented this subject.

      I'm trying to select datas from two tables(parent and child table), SOQL is like this:

      select a.Id,a.Name,a.Industry,(Select c.Id,c.FirstName,c.LastName From a.Contacts c) From Account a

      and I use blew code to access "Contact" object, and I always get  Contact is null, I really don't know how to do?

Please help you if you can.

 

    [

         for (int i = 0; i < qr.records.Length; i++)
         {
                    account = (Account)qr.records[i];
                    lstContact.Add(GetContactList(account.Contacts));

                    dr = dt.NewRow();
                    dr["ID"] = account.Id;
                    dr["Name"] = account.Name;
                    dr["Industry"] = account.Industry;
                    dt.Rows.Add(dr);
          }

 

 

        private List<Contact> GetContactList(QueryResult qr)
        {
            List<Contact> lst = new List<Contact>();

            if (qr.size > 0)  // qr =null I don't know why?
            {
                for (int i = 0; i < qr.records.Length; i++)
                {
                    Contact contact = qr.records[i] as Contact;
                    lst.Add(contact);
                }
            }
            return lst;
        }

    ]

 

 

Sincerely

skyfj