locked
Mapping Opportunity Product field to Invoice product RRS feed

  • Question

  • Hi Forum. I wish to map a custom field on an opportunity product to its related custom field on an invoice product.

    I have seen a lot of similar threads as this however none that map specifically a opportunity product to a invoice product.

    To give some background our current design is that an opportunity gets converted straight to an invoice via this function shown in the picture. When we create an invoice in this fashion the line items (opportunity products) of the opportunity are also converted to invoice products related to the newly created invoice.


    What we have is a lookup field called "Xero Account Code" that is auto-populated via javascript based upon the selected product.

    I am aware of the hidden mapping pages and so far my attempts have been to create mappings (and new corresponding custom fields) on the chain from Opportunity product --> Quote product --> Order product --> Invoice product.

    I followed this guide to discover this

    I had hoped that this would work under the assumption that when the create new invoice button is clicked on the opportunity it follows this natural chain in the background. I tested converting the opportunity to a quote first the see if the field carried over correctly and it did. Then I've tested it to an order and it also did. But when trying an invoice or order it did not.

    I'm hoping that there is simply a mapping page somewhere that I wasn't able to find or that someone can enlighten me as to the process that an invoice product gets its data from an opportunity product.

    Thank you for reading and thank you for any responses. If any further information is required to resolve the issue please ask.

    Regards,

    Spike.

    Friday, August 10, 2012 12:32 AM

All replies

  • If you are running the on premise version of CRM, you can access the 'hidden' mapping screens following the method in this blog post. It doesn't directly reference going from Opportunity to Invoice, but you should be able to substitute Invoice when it is looking for Quote to get the screen you would need. 

    Jason Lattimer

    Friday, August 10, 2012 4:56 AM
    Moderator
  • Hi there JLattimer, thank you for your response.

    However I am aware of this blog post and have followed a similar guide that I linked in my original post that also demonstrates finding the hidden mappings in CRM 2011 Online version (which is what I'm using). I will continue to search for my desired mapping which is fast becoming the holy grail of CRM for me.

    If you have any other suggestions I would greatly appreciate them.

    Regards,

    Spike.

    Sunday, August 12, 2012 11:42 PM
  • Ultimately I think you would need to create a plugin or some JavaScript to get the missing data from the Opportunity Product into the Invoice Product when it is created. This is totally feasible except the snag you could potentially see is that there could be multiple Opportunity Products that are all the same so that when it came time writing code to get the values when creating the Invoice Product - it might see Product A - Qyt 1 on 4 different lines but your missing data could be different for each line item. Granted you might have something in place to prevent duplicate lines but that would not be the 'norm'. At the database level there is no direct relationship between Invoice Product and Opportunity Product. By going to the standard Opportunity -> Quote -> Order -> Invoice process, CRM is able to systematically create a Quote Product from the Opportunity Product 1 by 1 and apply the mapping on each as it goes. 

    Jason Lattimer

    Monday, August 13, 2012 4:17 AM
    Moderator
  • I see what you are saying Jason and thank you for your advice.

    I'm a little confused about your concerns about duplication of the opportunity products, could you explain it another way?

    If I am reading correctly however then in our case it may not be an issue because the missing field we want to map is based off of the kind of product itself, so given that we can obtain that piece of data we can still map it correctly.

    Could you elaborate on how this might be achieved via JavaScript, I am eager to know, and cannot fathom how; but I am very new to it and the CRM system so I would surely learn something important here.

    I am looking into the plugin option currently although it is a lot to take in so far, any pointers there that you have to offer would also be greatly appreciated.

    I agree about following the normal flow from Opportunity -> Quote -> Order -> Invoice and did indeed map this flow in hopes that, that would solve my problem. One of (if not) my main questions is how it is that the invoice products are created when you create an invoice from an opportunity as per the picture in my original post.

    I know I have asked a lot but you have been very helpful so far in my learning of CRM. If you have some more answers the these questions that would be a great help.

    Thank you kindly,

    Spike Tickner.

    Monday, August 13, 2012 5:19 AM
  • Your Opportunity Products could potentialls look something like this:

    Line1. Product A  Qty. 1 Unmapped field value: 'abc'

    Line2. Product B Qty. 2 Unmapped field value: 'xyz'

    Line3. Product B Qty. 2 Unmapped field value: 'lmno'

    In the database Opportunities and Invoices separate data into header and detail. There is a field in the Opportunity that links to the Invoice so we know they are related. However at the line item level, there is nothing to related a specific Opportunity line with a specific Invoice line. So if you were to programmatically try and pull data for the corresponding Line2 Invoice product after it is created, you really have no unique way to tell the difference between Line2 and Line3.

    Now from what you just said, the values aren't really related to the Opportunity Product, but rather the base Product. This takes away any concerns about duplication/mismatching because there won't be any duplication of the base Product.

    The best resource for learning coding against CRM is the Microsoft Dynamics CRM 2011 Software Development Kit. It has many examples and working sample code. 

    If you were looking to do get the values in JavaScript - the code would look something like this (I have not tested this fully - I stripped down some existing code I had)

    function Product_OnChange() {
        //get values from the base product
        var productid = new Array();
        productid = Xrm.Page.getAttribute("productid").getValue();
    
        if (productid != null && productid.length > 0) {
            var id = productid[0].id;
            var odataSelect = Xrm.Page.context.getServerUrl() + "XRMServices/2011/OrganizationData.svc/ProductSet(guid'" + id + "')";
    
            $.ajax({
                type: "GET",
                contentType: "application/json; charset=utf-8",
                datatype: "json",
                url: odataSelect,
                beforeSend: function (XMLHttpRequest) {
                    XMLHttpRequest.setRequestHeader("Accept", "application/json");
                },
                success: function (data, textStatus, XmlHttpRequest) {
                    if (data.d.your_field != null) {
                        Xrm.Page.getAttribute("your_mapped_field").setValue(data.d.your_field);
                    }
                },
                error: function (XmlHttpRequest, textStatus, errorThrown) {
                    //alert('OData Select Failed: ' + odataSelect); 
                }
            });
        }
    }
    

    To make this code work you would also need to add jQuery as a web resource.

    Hopefully this get you pointed in the right direction.


    Jason Lattimer

    Monday, August 13, 2012 11:59 AM
    Moderator