Sum values from another field of a multi lookup field

[Alexander Bautz]

I got a question about summing the values from another column in a list connected with a multi lookup column. Here is the request:

I have a Product list that contains columns for product [Title] and the [Install hours] for that product.

On our Orders list I have a look up column where multiple products can be added to an order.

Based on the items added to that order, I would like the Install hours (on the products list) to be summed in another field in the orders list.

Here is a code example to get you started:

function lookupExtraColumnData(){
	var a = [], b = [], c = 0;
	a.push("<Where>");
	a.push("<In>");
	a.push("<FieldRef Name='ID' />");
	a.push("<Values>");
	$("#dffs_LookupFieldName select:last option").each(function(i,o){
		b.push("<Value Type='Number'>"+$(o).val()+"</Value>");
	});
	a.push(b.join(""));
	a.push("</Values>");
	a.push("</In>");
	a.push("</Where>");
	if(b.length > 0){
		var res = spjs.utility.queryItems({"listName":"ProductListDisplayName","query":a.join(""),"viewFields":["InstallHours"]});
		$.each(res.items,function(i,item){
			if(item.InstallHours !== null){
				c += Number(item.InstallHours);
			}
		});
		// set the value in a field
		setFieldValue("SumFieldName",c);
	}
}

Add this function to the custom js textarea in DFFS backend, and call it for example on “save” using the “The form is saved” trigger.

You must change “LookupFieldName” for your lookup field name, the “ProductListDisplayName” with the display name or the GUID of the product list, the field name “InstallHours” with the actual field you want to sum, and the “SumFieldName” with the field to write the sum to.

Please note that the sum is written to a “static” field in the list item, and will not update on already added items if you update the “InstallHours” field in your products.

Hope this can be used as a staring point.

Alexander

[Todd Ingersoll]

Hey Alexander, would the above be possible with a vLookup? So, in your example, say the person was creating the products in the products list with a vLookup in the same form prior to adding them to the orders list (even though this is an unlikely scenario).

[Alexander Bautz]

Sorry for the late reply. I’m not sure I fully understand what you mean. The above example is intended used with a multichoice lookup column in the same form so using it with the results from a vLookup would require another approach.

Alexander

[Author]

Posts