function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
JillDJillD 

Need Help in linking data from a custom table to Opportunity Products

After five months, I have a new job as an SF Admin. Would really rather not go job hunting again for a while, so I want to impress my new employers.

 

What I have is a custom inventory table that is being fed data from the ERP system. What I want is a way to create a report that lists opportunity, the products in the opportunity, the number of each product and the number of that product that is actually in the inventory. Sort of like this:

 

Library A - "Beginning Algebra" - 30 copies - 135 in stock.

 

I would think that linking the Inventory table to the opportunity product table would be easy, but nothing I've tried has worked.

 

Note, the person who is going to be using this report is not tech savvy. I need my report to be a one click solution for her, even if it's more complicated for me.

*werewolf**werewolf*

Linking the inventory table to the oppty table is generally the right way to go.  Did you try making a custom report type for it?

*werewolf**werewolf*

Wait a minute, on second thought, I'm thinking your structure should be like (this is hard to draw in text so bear with me):

 

                             ProductInventory

Oppty           Product

Oppty Product

 

Is that what you did?  If so then you should be able to add a formula field to OpptyProduct to get the inventory number from the grandparent.

JillDJillD

Hi Werewolf! Thanks for answering!

 

My diagram looks kind of like this:

 

Inventory (has lookup field to Product)

\                     

Product

\

Opportunity Product

 

Since Opportunity Product is linked to Opportunity, I gave up on trying to link Inventory to Opportunity. I don't think I need that, anyway. However, the information doesn't seem to be filling in.

 

I have tried several custom report types. I'm new on this project, my supervisor has made several attempts as well, but we are not getting the report we want.

 

Any suggestions on how to link Inventory to Product?

JillDJillD

My latest idea is to create a link table called "Inventory Product." Basically, it has:

 

OpportunityID, InventoryID, ProductID, "other fields as needed". This seems to work, as I created a report using Inventory Product in my development database that has all the information that I want.

 

My problem now is how to automatically populate this table. I think that I should create a trigger, probably on OpportunityLineItem, that will fire up when the Opportunity Product is added. The trigger will create an Inventory Product record and populate it with OpportunityID, InventoryID, ProductID.

 

However, I cannot figure out how to write the code for a trigger. I've been looking at sample code for triggers for hours and I'm only getting more confused and frustrated. Are there any simple instructions for creating triggers anywhere?

 

Thanks for reading and for any advice you may have.