Sharepoint newform populate field with selected lookup info

Question

I have used SPServices cascading drop-down list successfully in a SharePoint list form. But what I would also like to achieve is when a item is selected form a dropdownlist (lookup item), an other field is dynamically populated with related data.

i.e. When I have an order line and select a product from the list, the price of the product is populated in the price box.

I know something simular can be done with a workflow, but I prefer javascript since it is less heavy en much faster.

Thanks in advance,

Bas

Answer 1

Create your product list as you want, just add price column as number to it as well.

In Order List, create the desired columns, for price/quantity create a number column and for Sub-total create a calculate column with formula =[Price_Col_Name]*[Qty_Col_Name].

Now open the Order List new form. Now click on setting(gear) icon on top right corner and select edit page. Now follow steps:

1. Click on link Add a Web Part.
2. In Categories column select Media and Content and in Part column select Script editor and click on Add button.
3. You'll see that the web part is added to your page. And inside web part there will be a hyperlink saying Edit Snippet. Click on edit snippet.
4. A new popup will be open with a text area to write script. In this copy page the following script (make sure you change the variable where ever i mentioned, dont worry, I'll tell how you can find those as well).

Script

<script
    src="https://code.jquery.com/jquery-3.2.1.min.js"
    integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4="
    crossorigin="anonymous"></script>
<script>
    var OrgPriceId = "input[title='OrgPrice']";
    var ProductDropDownId = "select[title='Product']";
    var siteUrl = "http://site_url/";
    $(document).ready(function(){
        $(OrgPriceId).prop("readonly", "readonly");
        $(ProductDropDownId).on('change', LoadPrice);
    });

    function LoadPrice(){
        var productId = $(ProductDropDownId).val();
        if(productId != null && productId != ""){
            var url = siteUrl + "_api/web/lists/getbytitle('Product')/items(" + productId + ")";
            $.ajax({
                url: url,
                type: "GET",
                headers: {
                    "accept": "application/json;odata=verbose",
                    "content-type": "application/json,odata=verbose",
                },
                success: function(data){
                    if(data.d !== undefined){
                        $(OrgPriceId).val(data.d.Price); // data.d will remain same you need to find Price column name.
                    }
                }
            });
        }
    }

</script>

In this script you need to edit following things:
1. "input[title='OrgPrice']" to "input[title='Org Price Column Name']".
2. select[title='Product'] to select[title='Product column name'].
3. http://site_url/ to your exact site URL. If you are unsure what is your site collection URL then open any of the list Product or Order and remove everything from List/Order......... Take the first part. 4. And finally data.d.Price to the data.d.Price_Column_Internal_Name. If you dont know how to get this. Open Product List Setting, under columns section find Price Column and click on it, it will navigate to a new page. In the Url find &Field=Internal_Name. Grab the internal name and replace it.

Repeat the same steps for Edit form as well.

Answer 2

There are many ways to achieve this, but following two will be what I prefer.

Lookup Columns - Additional Fields
Based on the lookup column, you can get additional columns. So you just need to select the lookup column and the additional columns will get value automatically when you save/update you list item (which have lookup column). Good for situations when you are using OOTB new forms. When you add lookup column it gives you option to all the related columns, see in image below.

REST API
If you are doing custom forms and dont want additional lookup columns, then just use SP REST API to get the details you require. In this you need to make an ajax call to the REST API list item endpoint and you'll get all the data. You need to get the lookup item ID (which can be extracted from dropdown, see in image below). And finally make the ajax call to Item EndPoint. The URL will be http://url_to_site_collection/_api/web/lists/getbytitle('Title_of_List')/Items(extracted_item_id). This will return a JSON object which you can use. Or to read on REST API follow the URL.

Following is the jQuery ajax call to get the item result.

$.ajax({
        url: "http://server:port/sites/test/_api/web/lists/getbytitle('name_of_list')/items(item_id_must_be_number)",
        type: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
            "content-type": "application/json,odata=verbose",
        },
        success: function(productData){
            console.log(productData); // This JSON object will give you price.
        }
    });