Our org has been storing serial numbers in an arbitrary text area field in the Opportunity Products object, delimited by arbitrary characters (commas, newlines, spaces, etc). This is very bad for a number of reasons, one of which is the fact that Opportunity Products aren't indexed for searching and there is no way to look up Opportunities by the serial numbers of products sold because of the fact that they're storing the serial numbers in that arbitrary text field on the Opportunity Products object.
I am trying to move this data into the correct object for storing that information (Assets). I would like to add a Lookup field to the Assets object for Opportunity Products, so that we can associate a list of Assets with an Opportunity Product.
For example, if a user creates an Opportunity Product record tied to an Opportunity to represent a sale of Product XYZ with a quantity of 3, they will need to be able to associate a list of 3 Assets with 3 unique serial numbers to this Opportunity Product record.
I tried to add this Lookup field to the Assets object but I don't see Opportunity Products in the drop-down list. I suppose I could associate the Assets with the Opportunity directly, but this is less desirable as I feel that it would make more sense to associate it with Opportunity Product. Otherwise, users will have to look at both the Assets list and the Opportunity Products list to figure out which Assets correspond to which Opportunity Products. This can become very cumbersome if there is a large list of Opportunity Products and Assets.
I was thinking I could possibly create a custom junction object to tie Assets to Opportunity Products (if it will let me add a lookup to the Assets object for that junction object), but since I can't think of any case where an Asset would be associated with more than one Opportunity Product, I feel like this would be overkill and could potentially lead to people accidentally associating them with multiple Opportunity Products.
Any way to do this?