We have an issue where we update our pricebook once a year, but we do not want to maintain multiple pricebooks. The problem is when we update the list price, Salesforce goes thru all our opportunities and updates all the List Price. The problem is we already have a trigger that calculates discount and sale price based on List Price. So, once the List Price is updated, unless the user edits the existing opportunity line items, the discount and sale price calculation is incorrect.

However, we only want the re-calc to be performed for any open Opportunities and ignore closed ones. So, we created a custom List Price field and I set the formula to be equals to List Price if null, or if not null, then set it to List Price if stage is not "Closed xxx". Otherwise, I want it to be set back to itself or not changed.

However, if I do:

IF(

  ISNULL(List_Price__c), ListPrice,

  IF(

    CONTAINS(TEXT(Opportunity.StageName),"Closed"), List_Price__c,

    ListPrice

  )

)

I'm getting the "Formula cannot use another formula field that directly or indirectly refers to itself".

I also tried setting up List_Price__c as just a currency field and use workflow rules to populate the value. This works, except for existing records it is null, unless I edit and save any part of the opportunity line item. If I set it as formula (say a simple List_Price__c = ListPrice), then by virtue of viewing the Opportunity Line Item, the List_Price__c value is populated.

I guess I could do a mass update of all the existing Opportunity line items on the backend and then use the workflow rule from now on, but since I'm learning how best to maximize Salesforce functionality, I'm wondering if anyone knows of a way to solve this formula issue?

In any programming language, I can just do the following:

List_Price__c = List_Price__c == null ? ListPrice : List_Price__c

Thanks.