SharePoint Document Generator – Part 2: Picking default columns values from a related list

Guest Author: Mindaugas Bliudzius

In my last post I showed one way to establish parent – child relations in SharePoint. Sometimes there is a need to pickup default columns values from a related list. For example in my Sales Orders Management application I want to choose Product from a list when creating new a Order or Invoice. But I can’t just have a lookup column to the chosen Product because over time Product prices and names can change. 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 the 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)

//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;
}

function ChangeProduct() {

    // get Product selection control
	var klElement = $("select[title='Product']");
	if (klElement.length == 0) { klElement = $("input[title='Product']"); }
	if (klElement.length == 0) return;

	// get selected Product ID
	var kl = '';
	if (klElement[0].optHid) { kl = document.getElementById(klElement[0].optHid).value; }
	else { kl = klElement[0].value; }

	JSRequest.EnsureSetup();

	// query Products list to get selected Product
	var lists = new SPAPI_Lists(GetRootUrl())
	var items = lists.getListItems(
		'Products',     // listName
		'',             // viewName
		''+kl+'',  // query
		'',  // viewFields
		'',  // rowLimit
		'FALSE'  // queryOptions
	); 

	if (items.status == 200)
	{ 

	   var rows = items.responseXML.getElementsByTagName('z:row');
	   if (rows.length > 0){

            // set Order details item's Title, Item Number and Price to the coresponding values of selected Product
		   $("input[title='Title']").val(rows[0].getAttribute('ows_Title'));
		   $("input[title='Item Number']").val(rows[0].getAttribute('ows_Item_x0020_Number'));
		   // you may need to chanche this depending on yout local currency format
		   $("input[title='Price']").val(Number(rows[0].getAttribute('ows_Price')).toFixed(2).replace(/\./,"."));
		}
	}
}

$(document).ready(
	 function()
	 {
	    // if Order details fields is still empty, set them to the Product selected by default
  		if ($("input[title='Title']").val()=='') { ChangeProduct(); }

        // Find Product control (where ID of selected Product is stored)
        // SharePoint renders combobox control as SELECT until items number is below 20
        // After that control is rendered as INPUT. As you can see I have a quick workaround for this.

		var klElement = $("select[title='Product']");
	    if (klElement.length == 0) { klElement = $("input[title='Product']"); }
	    if (klElement.length == 0) return;

        // attach to the propertychange event
		if (klElement[0].optHid)
	    {
		    $("input[id='"+klElement[0].optHid+"']").bind("propertychange", function () { ChangeProduct(); });
	    } else {
		    $("select[title='Product']").change(function () { ChangeProduct(); });
	    }
	 }
); 

The same can be implemented if we want cascading drop down lists in SharePoint.

Guest Author: Mindaugas Bliudzius
Mindaugas Bliudzius has been a software developer for a 15 years. For the past few years he has focused heavily on SharePoint based solutions development and consulting. Mindaugas is co-founder of www.sharepointdrive.com and co-owner of a SharePoint hosting company based in Lithuania.