1

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

2

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.

  • It's not working yet. The Org Price Colmn Name and Product Colmn name are those the name of the columns of the original parent list? – Basemaster Jun 23 '17 at 14:58
  • No it will be the name of Order list. Since we are dealing with Order List here. – ThinkB4Code Jun 23 '17 at 15:04
  • Well, I have tried getting it working this weekend, but without success. Here I have some screenshots with additional info and the original column names (in dutch) img1 img2 img3 img4 – Basemaster Jun 26 '17 at 9:34
  • Start with adding JQuery to page and put statement like $("input[title='Org Price Column Name']"). And see the output. If the output contains length = 1 then you are on right track. – ThinkB4Code Jun 27 '17 at 6:02
  • Make sure you do this on Order New Form. – ThinkB4Code Jun 27 '17 at 6:03
1

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.

enter image description here

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.

enter image description here

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.
        }
    });
  • The first option is not sufficient for my needs, since I need to fetch a product price to use in a calculated field (Sub-total). The second one might work only I'm not an experienced programmer so an example would be highly appreciated. – Basemaster Jun 23 '17 at 12:08
  • are you aware of JQuery Ajax calls? – ThinkB4Code Jun 23 '17 at 12:11
  • Not so much, only some simple operations following tutorials. – Basemaster Jun 23 '17 at 12:14
  • I had Updated my code. If you require complete steps then i'll update above ans as well. – ThinkB4Code Jun 23 '17 at 12:18
  • Do I put this in a HTML source content webpart on the formedit and formnew page? Here is an example of what I have. base-online.nl/test/spsampel.JPG – Basemaster Jun 23 '17 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.