3

We have come to find that we have a small amount of products that have Product2 records, but no PricebookEntry for them. I'm trying to query for just those products, but I am having trouble with the relationships between them.

I have tried:

SELECT Id, ProductCode from Product2 WHERE id not IN (SELECT Id from PricebookEntry WHERE Pricebook2 = '01si0000003vpAlAAI') 

However, that query won't run because of this error: The selected field 'Id' in the subquery and the left operand field in the where expression in the outer query 'id' should point to the same object type.

The hard coded id is our Standard Price Book. We only need to get this info once so I am not worried about hard coding that id.

5

The Id column in your subquery is the Id of the PricebookEntry object. You want the reference field to Product2, Product2Id:

SELECT Id, ProductCode 
FROM Product2 
WHERE Id NOT IN (SELECT Product2Id 
                 FROM PricebookEntry 
                 WHERE Pricebook2Id = '01si0000003vpAlAAI') 

My first stop with this kind of issue is always the field reference for PricebookEntry (or whichever sObject is in question). It's a great resource for not just names but specific requirements and limitations around each standard field.

  • Let us continue this discussion in chat. – David Reed May 29 '18 at 20:34
  • For anyone reading this in the future, it turned out that we have duplicate product codes in our system with different Salesforce IDs. Since we already have the case safe Id as a field on Product2 I was able to export a report with 18 digit ids from the standard report 'Pricebooks with Products' and compare it to the results of the query list above using a standard VLookup in order to verify that the output of the query was actually correct. It was. – AstroLovesCodey May 30 '18 at 18:21

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.