2

A little context: you need to track the amount of products in an account (inventory management). To do this, you have a custom object that acts as a junction table between an account and a product. This object has a quantity field that has field history enabled.

 Id
 Name
Product__c (lookup)
Account__c (lookup)
Quantity__c (history enabled)

Now if you need to generate a report to get the exact amount of items in a certain account on a certain date, how can you filter just the last modification per item (the newest NewValue on the history per item)?

My main issue here, I believe, is that I don't know how to use SOQL to get only the latest history entry up to a date. This can become an issue for a huge history, because if I want to get a history, say, from January, internally I'd have to query everything in the history just to know what I had in stock until 1/Jan. The remaining records would be used to calculate what happened in the month.

|improve this question
2

This query compiles for me and I believe it does what you want:

SELECT
    (
        SELECT NewValue
        FROM Histories
        WHERE Field = 'Name'
        ORDER BY CreatedDate DESC
        LIMIT 1
    )
FROM Account
|improve this answer
  • do you know if there's a way to get the values for an entire month with a query like this? I mean, if I'd like to know the changes in a month. The problem of filtering by the month is that if a change occurs in, say, December, and the next change only occurs in February, then the query won't return the value that stayed the same during January. – Renato Oliveira Feb 2 '18 at 16:32
  • You could add a CreatedDate filter to your WHERE clause like WHERE Field = 'Name' AND CreatedDate = LAST_MONTH. – Adrian Larson♦ Feb 2 '18 at 16:35
  • I think I know what to do now. I can't filter by the last month because the modification could take several months to be made. I think its best to do two queries. One to get the value in the start date (like your example), and other to query the changes between the start date and the end date (with a WHERE specifying the two dates). – Renato Oliveira Feb 2 '18 at 16:40
  • 1
    Using LAST_MONTH was just an easy example to throw in the comments. Sounds like you get the idea. – Adrian Larson♦ Feb 2 '18 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.