I have a SharePoint 2010 Parent/Child list setup using a lookup field to join both the lists, this is working well.

The Parent list contains requisition requests and the child list contains related item details. Each of the related child list items has a 'Line Item Total' field. I need to aggregate the Line Item Totals in the child list and place the result in a 'Requisition Total' in the parent. So for any given parent record the sum of it's related child 'Line Item Totals' is reflected in the 'Requisition' total in the parent row.

I am struggling with the JavaScript code below. I am trying to aggregate Line Item Totals for a given Requisition#? Could someone please review and advise?

<script language="javascript" src="/Purchasing/Documents/jquery-1.8.2.js" type="text/javascript"></script>
<script language="javascript" src="/Purchasing/Documents/jquery.SPServices-2014.01.min.js" type="text/javascript"></script>

<script>

$(document).ready(function()
{

 $('select[title=Requisition#]').children().attr('disabled',true);
 $("input[title='Old Amount']").attr("readonly","true").css('background-color','#F6F6F6');

   // Retrieve the name of the selected parent item.
   var strParent=$("select[title$='Requisition#'] :selected").text(); 

 splist li = web.lists["ReqDetails"]; // This is the child list
    spquery q = new spquery();
    q.Query= "<Where>" +
     "<And>" +
      "<Neq>" +
       "<FieldRef Name='ID'/><Value Type='Number'>0</Value>" +
      "</Neq>" +
      "<eq>" +
       "<FieldRef Name='LinkID'/><Value Type='Number'>ReqID</Value>" +
      "</eq>" +
     "<And>" +
    "</Where>" +
    "<OrderBy>" +
     "<FieldRef Name='LineItemTotal'/>" +
    "</OrderBy>"
    q.ViewFields = string.Concat("<FieldRef Name='LineItemTotal' />");
    q.ViewFieldsOnly = true; // Fetch only the data that we need

    splistitemcollection items = li.getitems(q);

    foreach(SPListItem item in items)
    {
    sum += parseInt(item["LineItemTotal"], 10);
    }
   
   
  alert(sum)

 });

 </script>

Hi  Rick,

For  SPList  class and SPQuery classs, they  belong to  Server  Object Model. So you cannot use them in the JavaScript Client Object Model. For your issue, please refer to the thread :

http://sharepoint.stackexchange.com/questions/93736/count-items-in-a-list-javascript-csom

Thanks,

Eric

Forum Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

Thanks Eric. I checked out the link you provided and I have updated the code accordingly but it still won't work? I have included the updated code  below. It looks OK to me but I am obviously missing something. I am new to SharePoint development and appreciate any help you can provide...

<script>
    $(document).ready(function () {
   // Ensure that the SP.UserProfiles.js file is loaded before the custom code runs.
   //SP.SOD.executeOrDelayUntilScriptLoaded(loadUserData, 'sp.userprofiles.js');
   //Disable all the drop down item values.
   $('select[title=Requisition#]').children().attr('disabled',true);
   
   //Set this Field to Read only and change its background colour
   $("input[title='Old Amount']").attr("readonly","true").css('background-color','#F6F6F6');
   
   // Retrieve the name of the selected parent ID.
   var strParent=$("select[title$='Requisition#'] :selected").text(); 
   alert(strParent);
   
   SP.SOD.executeFunc('sp.js', 'SP.ClientContext', sharePointReady);
  });

 
  var clientContext;
  var website;
  
  function(sharePointReady)
  {
   clientContext = SP.ClientContext.get_current();
   website = clientContext.get_web();
   clientContext.load(website);
   clientContext.executeQueryAsync(onRequestSucceeded, onRequestFailed); 
  } 
  
  function onRequestSucceeded() {
   alert('URL of the website: ' + website.get_url());
  }
  
  function onRequestFailed(sender, args) {
   alert('Error: ' + args.get_message());
  }

</script>

I do something similar for a PO Header/PO Detail set of lists. I use SPServices to pull the data.

I put this code in the child edit page - so any time any child is edited, the parent is updated. I get all children records and total them up (except for the item I'm displaying at the current time. I add that value in directly from the form itself.

<script type="text/javascript" src="/Javascript/JQuery/JQueryMin-1.11.1.js"></script>
<script src="/Javascript/JQuery/jquery.SPServices-2014.01.min.js"></script>
<script>
var poNumber = '';
var poDetailID = '';

$(document).ready(function() {
	var queryStringVals = $().SPServices.SPGetQueryString();
	 poNumber = queryStringVals["PO"];
	 poDetailID = queryStringVals["ID"];
});

function PreSaveAction() {

// update the PO header with the total of all items + shipping + adjustments
	var query = "<Query><Where><And><Eq><FieldRef Name='Title' /><Value Type='Text'>" + poNumber + "</Value></Eq><Neq><FieldRef Name='ID' /><Value Type='Counter'>" + poDetailID + "</Value></Neq></And></Where></Query>";
	var viewFields = '<ViewFields><FieldRef Name="Total" />';
	viewFields += '</ViewFields>';
	var poTotal = 0.00;
	$().SPServices({
		operation: "GetListItems",
		async: false,
		listName: "PO Details",
		webURL: "/po",
		CAMLViewFields: viewFields,
		CAMLQuery: query,
		completefunc: function (xData, Status) {
			//alert(xData.responseText);
			$(xData.responseXML).SPFilterNode('z:row').each(function() {
				if($(this).attr("ows_Total")) {
				//alert($(this).attr("ows_Total"));
				poTotal += Number(parseFloat($(this).attr("ows_Total").split(";#")[1],2).toFixed(2));
				}

				});
		}
	});	
	// add in this changed line (we skipped it above)
var qty = $('input[title="Quantity Ordered"]').val();
var price = $('input[title="Price"]').val().replace(',','');
var extend = Number(qty)*Number(price);
poTotal += extend;
//alert(poTotal);




	
	// update the PO Header
$().SPServices({
	operation: "UpdateListItems",
	async: false,
	listName: "Purchase Orders",
	webURL: "/po",
	ID: poID,
	valuepairs: [["Total", poTotal]],
	completefunc: function (xData, Status) {
		//alert(xData.responseText);
		$(xData.responseXML).SPFilterNode('z:row').each(function() {
		});
 
	}
});

return true;
}  

Thanks for a speedy and comprehensive response :) This solution should cover the requirements more than adequately. Just on thing, should I use the internal or external names of the list fields in the code?

Rick