Help editing formula field

Question

I have a formula field (Price_Book_Name__c) on the Opportunity which pulls the Name from 2 lookup fields Price_Book__c (new field) and Product_Interested_In__c (old field). An opportunity may have only 1 of these fields completed, and the Name only populates once on Pricebook Name which is great. However, if both lookup fields are completed, the Name populates twice which messes with reporting on historical opportunities. I've written the below formula on the Price_Book_Name__c field to populate the Name from both lookup fields, however I cannot figure out how to change the string so that it chooses one field, either Price_Book__c OR Product_Interested_In__c to pull the Name from so that it only populates once.

Here is my code on the Price_Book_Name__c field:

SUBSTITUTE(

IF(ISBLANK(Product_Interested_In__r.Price_Book_Name__c) ,NULL,Product_Interested_In__r.Price_Book_Name__c+", ") +

IF(ISBLANK(Pricebook2.Name) ,NULL,Pricebook2.Name+", ") + ".",

", .",

NULL

)

I've looked into using a flow for this, however I cannot change the field type of the Price_Book_Name__c field.

Could someone please assist me in updating the code so it populates Product_Interested_In__r.Price_Book_Name__c OR Pricebook2.Name, not both

Answer 1

To ensure that the Price_Book_Name__c field populates with only one of the names from either Price_Book__c or Product_Interested_In__c, you can use a formula that checks if one field is filled and then uses that value, or else it defaults to the other field.

IF(
        NOT(ISBLANK(Product_Interested_In__r.Price_Book_Name__c)),
        Product_Interested_In__r.Price_Book_Name__c,
        IF(
                NOT(ISBLANK(Pricebook2.Name)),
                Pricebook2.Name,
                NULL
        )
)

This formula first checks if Product_Interested_In__r.Price_Book_Name__c is not blank. If it's not, it uses this value. If it is blank, it then checks Pricebook2.Name. If Pricebook2.Name is also not blank, it uses this value. If both are blank, it returns NULL.