Pulling through a custom field from Product to Opportunity Product

(0) Share

Report

Posted on by Community Member

Hi,

We've encountered an issue that I'm unsure how to resolve...

One of my colleagues created a new custom field in the Product entity - called 'category', in order to report on and group certain products by their type.

The same field has also been created in the Opportunity Product entity - again called 'category', that looks at the same coded list.

The products have all had categories assigned to them, but in their infinite wisdom, they didn't create a link / mapping between the field in Product and Opportunity product, so now when products are selected from the list in an Opportunity, the result is the Opportunity Product is created, but without a category, so it then cannot be reported on.

I'm fairly new to customising things, but I'm pretty sure that I need a 1:N relationship created between Product:Opportunity product so that it maps across and populates. The trouble is, I added one, published it, but it didn't seem to work and still it's created without a category.

Am I missing something obvious? Has anyone had a similar issue?

Thanks in advance,

Craig

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

David Jennaway 14,065 on at

Like (0)

Report

There is already a relationship between product and opportunityproduct, but unfortunately CRM doesn't let you customise the mappings for this relationship

Is it necessary to copy this field into the opportunityproduct ? For reporting purposes you should be able to join to the product to use this field.

Otherwise, you could create a workflow on the opportunityproduct to copy the value into the opportunityproduct record

Was this reply helpful? Yes No
TheMarkChristie 10,328 Moderator on at

Like (0)

Report

Hi Craig,

You should be able to do this with the out of the box mappings Using Opportunity to Opp Lines/Product as so

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi David - thanks for the quick response - I did manage to find my way to the standard relationship and did find it odd you couldn't add / edit the mappings here, so that answers that.

I actually had the same conversation with our Dev team today as the line created in the opportunity product will have the same unique name as what is in the product table, which of course contains a valid, populated category - so they are going to see if they can make the reports use that instead - trouble is they are using Power BI to make these reports, but that shouldn't be an issue as it's pretty robust!

I did also have a look at creating a workflow but without a relationship between the different entities it doesn't allow me to select the correct entity type to run the update on. I did create a new relationship between them, which then allowed me to select the correct entity to update, but for some reason it didn't have the desired outcome.

Craig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Mark,

Thanks, but unfortunately the link needs to be between the Product and the Opportunity Product, not the Opportunity and the Opportunity product - the category I need to pull through exists in the Product and nothing is held / stored in the Opportunity itself.

I did come across this initially, and as David says above, for some reason you cannot edit the mappings between these for some reason, so I ended up creating my own which didn't work...

Craig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Craig ,

You can populate the category in the opportunity product :

1- using Javascript on change of the product : loading the category from the product using Web API : this is on client side

2- on server side : you can use Plugins

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi CLAB01 - does this mean there is no standard way of getting this working to your knowledge? I'm sure there are plenty of things achievable by using external plugins or coding with JS, but I'd like to think there was a way of doing this within the system itself...

Thanks,

Craig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Craig ,

I can't think of a configuration way to do this .

As David mentioned, the relationship between product and opportunityproduct is not visible in CRM web interface . Although you can find a way to see it and modify it to do the mapping . This would be Unsupportable , and i wouldn't advise you to do it.

Also since your Category field is a lookup , you can't use Calculated field .

That's why i advised you to go with JS/Plugins.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Hi Craig,

There's no way to do this with built in functionality inside D365, you would have to create a plugin to get the required information/mapping.

That being said, I've recently faced this problem myself and found an easy solution which didn't involved me creating a plugin (which is good as i'm not a developer). I found a solution called North52 which gives you access to multiple functions which are pre-built and you can leverage using simple code, a lot like functions inside Excel.

They have both a free to use model and paid model, the free model allows for using up-to 5 functions as many times as you like, so great for smaller deployments or when you just need that little extra functionality. I'd suggest checking them out: http://www.north52.com/

They also have a great knowledge base as well, so the solution to what you are looking for is something like this: http://support.north52.com/knowledgebase/articles/397611-xrm-formula-21-copy-field-from-the-product-to-q

I'm not affiliated with them in anyway, but when you find a good product you should tell people. Hope this helps.

Thanks
Matt

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the heads up Matt - I'll review on Tuesday and let you know what I find out!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Perfect Matt - thanks for leading me on to this!

Was this reply helpful? Yes No