Picking default columns values from related list

In my last post I showed one way of how to establish parent – child relations in SharePoint. Sometimes there is a need to pickup default columns values from related list. For example in my Sales Orders Management application I want to choose Product from a list when creating new Order or Invoice. But I can’t just have a lookup column to the chosen Product because over time Product price and name can change and I need to have price and Product name fixed as it was at the time the Order or Invoice was created. So I need to copy price and name values from the chosen Product item to the Order details item.

It is possible to achieve this using just JavaScript (jQuery and SPAPI) and CEWP (Content Editor Web Part). All you need to do is add CEWP to your page (in my example I added it to the Order view page) and add the following code (see comments inside):

//Set up jQuery and SPAPI (in my case I have installed jQuery and SPAPI in Layouts directory)
<script src="/_layouts/jquery-1.3.2.js" type="text/javascript"></script>
<script src="/_layouts/SPAPI/SPAPI_Core.js" type="text/javascript"></script>
<script src="/_layouts/SPAPI/SPAPI_Lists.js" type="text/javascript"></script>
<script type="text/javascript"> 

//You may need do modify this function depending where your site collection is located
function GetRootUrl() {
    var pathparts = document.location.pathname.split('/');
    var url = 'http://' + document.location.host + '/' + pathparts[1] + '/';
    return url;
}

lastSelectedCategory = 0;

//filter product by selected catagory
function FilterProducts() {

    //get Product control (it can be INPUT or SELECT)
    var productElement = $("select[title='Product']");
    if (productElement.length == 0) { productElement = $("input[title='Product']"); }
    if (productElement.length == 0) return;

    //get Product Category control (it can be INPUT or SELECT)
    var categoryElement = $("select[title='Product Category']");
    if (categoryElement.length == 0) { categoryElement = $("input[title='Product Category']"); }
    if (categoryElement.length == 0) return;

    //get selected Product Category value
    var category = '';
    if (categoryElement[0].optHid) { category = document.getElementById(categoryElement[0].optHid).value; }
    else { category = categoryElement[0].value; }

    if (lastSelectedCategory != category) {

        var foundedSelectedProductID = '';
        var foundedSelectedProduct = '';

        //get sletected Category Products
        var lists = new SPAPI_Lists(GetRootUrl());
        var items = lists.getListItems(
		'Products',   // listName
		'',         // viewName
		'<Query><Where><Eq><FieldRef Name="Product_x0020_Category" LookupId="TRUE"/><Value Type="Lookup">' + category + '</Value></Eq></Where></Query>',  // query
		'<ViewFields><FieldRef Name="ID"/><FieldRef Name="Title"/></ViewFields>',  // viewFields
		'',  // rowLimit
		'<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>'  // queryOptions
	);

        if (items.status == 200) {
            var rows = items.responseXML.getElementsByTagName('z:row');
            var options = '';
            //format Products values for INPUT control
            if (productElement[0].optHid) {
                var productSelected = document.getElementById(productElement[0].optHid).value;
                for (var i = 0; i < rows.length; i++) {
                    var productID = rows[i].getAttribute('ows_ID');
                    if (productID == productSelected) { //trying to select the same product in new category if it exists
                        foundedSelectedProductID = productID;
                        foundedSelectedProduct = rows[i].getAttribute('ows_Title');
                    }
                    options += rows[i].getAttribute('ows_Title') + '|' + productID + '|';
                }

                productElement[0].choices = options.substring(0, options.length - 1);

                productElement[0].value = foundedSelectedProduct;
                productElement[0].match = foundedSelectedProduct;
                document.getElementById(productElement[0].optHid).value = foundedSelectedProductID;

            } else {
            //format Products for SELECT control
                var productSelected = productElement[0].value;
                for (var i = 0; i < rows.length; i++) {
                    var productID = rows[i].getAttribute('ows_ID');
                    if (productID != productSelected) {//trying to select the same product in new category if it exists
                        options += '<option value="' + productID + '">' + rows[i].getAttribute('ows_Title'); +'</option>';
                    } else {
                        options += '<option selected="selected" value="' + productID + '">' + rows[i].getAttribute('ows_Title'); +'</option>';
                    }
                }
                productElement.html(options);
            }
        }
        lastSelectedCategory = category;
    }
}

$(document).ready(
     function() {

         //FilterProducts(); //uncomment if you want Products be filttered on startup;

         // Filter products on Category change (Category also can be INPUT or SELECT)
         var categoryElement = $("select[title='Product Category']");
         if (categoryElement.length == 0) { categoryElement = $("input[title='Product Category']"); }
         if (categoryElement.length != 0) {
             if (categoryElement[0].optHid) {
                 $("input[id='" + categoryElement[0].optHid + "']").bind("propertychange", function() { FilterProducts(); });
             } else {
                 $("select[title='Product Category']").change(function() { FilterProducts(); });
             }
         }
         // End Change Product Category
     }
    );

Same way can be implemented so wanted cascading drop down lists in SharePoint.

Posted in Customization, javascript
One comment on “Picking default columns values from related list
  1. sean says:

    Hi wanted to know if the information on Picking default columns values from related list and how to establish parent – child relations in SharePoint are good for 2010. I am trying to implement the new revision of the documents generator and i thinks its awesome. thank you for your help