0

I wasn't able to find the answer on the forum: I have an easy query which involve a where condition with parent/child relation.

Here is what I'm looking to achieve:

Select Id, ProductCode, EAN__c, Family, Product_Line__c, Name, Category__c, Product_Type__c, **CurrencyIsoCode**, isActive, (Select Id from PricebookEntries WHERE Pricebook2Id = 'XXXXX' AND <CurrencyIsoCode of the Pricebook> = <**CurrencyIsoCode of the product**>) from product2 WHERE isActive = TRUE

CurrencyIsoCode in bold should be the same.

The purpose is to match the parent currencyIsoCode field with the child CurrencyIsoCode field.

Does someone have any idea?

Thanks in advance

1 Answer 1

3

You can't achieve this in SOQL, which doesn't allow comparison of one field to another. You can only compare fields with a constant expression.

What you can do is create a formula field on the PricebookEntry whose value is equal to the outcome of that comparison:

CurrencyIsoCode = Product2.CurrencyIsoCode

Then, you can reference that field in your query:

SELECT Id, ProductCode, EAN__c, Family, Product_Line__c, Name, Category__c,  Product_Type__c, CurrencyIsoCode, isActive, 
    (SELECT Id 
     FROM PricebookEntries 
     WHERE Pricebook2Id = 'XXXXX' AND Shares_Parent_Iso_Code__c = true) 
FROM Product2
WHERE IsActive = TRUE   

Note that this can have the same performance implications as any other situation where you filter a query by a formula field, particularly at large data volumes.

1
  • Nice workaround! It is working perfectly. Thanks!
    – Mathieu D.
    Nov 26, 2020 at 5:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .