[SalesForce] How to query the products in standard price book based on StandardPrice

I am using the standard price book for the order ,but my standard price book contains same product with different standard price.How to query the products in standard price book based on the Standard price?

Best Answer

The other answers will work with one caveat. It will be difficult to unit test the code without using @SeeAllData=true. If you want to be able to cover your code with a unit test that does not use @SeeAlLData=true, but instead uses the new Test.getStandardPricebookId(), you will have to take a slightly different approach.

The below is an example unit test that inserts a PricebookEntry with the standard Pricebook Id. It is followed by three approaches to writing the application code, each better than the previous.

Example unit test

@isTest
static void testStandardPricebook() {

    Id productId = TestUtil.generateProduct().Id;

    PricebookEntry entry = new PricebookEntry(
        Product2Id = productId,
        Pricebook2Id = Test.getStandardPricebookId(),
        UnitPrice = 10.0;
        IsActive = true
    );

    insert entry;

    // ... call the application code under test and then assert...
}

Example application code (option 1 - fail)

List<PricebookEntry> entries = [
    SELECT Id
    FROM PricebookEntry
    WHERE Pricebook2.IsStandard = true
];

The above fails to select anything because the Pricebook2 object itself is not visible. It was just the Id that was made available through the new Test.getStandardPricebookId().

Example application code (option 2 - ok)

Id stdId = Test.isRunningTest() ?
               Test.getStandardPricebookId() :
               [SELECT Id From Pricebook2 
                WHERE IsStandard = true].Id;

List<PricebookEntry> entries = [
    SELECT ID
    FROM PricebookEntry
    WHERE Pricebook2Id = :stdId
];

Icky because you need Test.isRunningTest() in your application code, but works.

Example application code (option 3 - cleaner)

You could add a checkbox formula field to the PricebookEntry with a value of {!Pricebook2.IsStandard}. Your code would simplify to:

List<PricebookEntry> entries = [
    SELECT ID
    FROM PricebookEntry
    WHERE Is_Standard__c = true
];

The above works as the formula field value is evaluated correctly on the query.

I originally tinkered with this in this blog post I wrote a few months ago.

Related Topic