Cost Calculation fields with flexible pricing

[blissland]

I'm wondering how to set fields up so that the prices for items purchased will fill in the COST field when the items are selected while at the same time having the option to fill in an alternate price.  So if I indicate that Customer 1 selects Item A, the cost will fill in with $100 as I've pre-specified elsewhere for that item, but I could go in and make it $80 for one customer.  Currently if I go in and alter the price in that customer's record, it also changes the price for that item for everyone who has bought that item, because it's a calculation field.  But if I change it to a lookup field, then all the records that are currently filled in go blank, and there's too many records to go back and manually enter all the correct data.  Surely there's a solution to this.

[comment]

The solution is to use a lookup.

 

30 minutes ago, blissland said:

But if I change it to a lookup field, then all the records that are currently filled in go blank,

If the field is currently an unstored calculation (I am guessing here, because your description is very vague), then define a new Number field and populate it with the current values returned by the calculation (using Replace Field Contents). Then you can make the new field lookup the correct price from now on, and use it instead of the existing field.

Make sure you have a backup before you try this.

[blissland]

I have a few different pictures of what you're suggesting, so just to be clear....

Are you saying that I make a new field, populate it with what's in the old field, and then convert the new field to a lookup field from that point forward (and then just remove the old field)?

Or are you saying that I create a new field that always enters the values of the old field but unlike the old field is editable?  

I think you're saying the former.

 

[comment]

51 minutes ago, blissland said:

I think you're saying the former.

Correct.

You could also reuse the existing field, if you (1) copy the calculation formula, (2) change the field's type to Number,  (3) use the copied formula to replace the field's contents, and (4) set the field to auto-enter the correct price from now on.