Sum values from another field of a multi lookup field

Forums Dynamic Forms for SharePoint Sum values from another field of a multi lookup field

This topic contains 2 replies, has 2 voices, and was last updated by  Alexander Bautz 3 weeks ago.

  • Author
    Posts
  • #10420

    Alexander Bautz
    Keymaster

    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

  • #16602

    Todd Ingersoll
    Participant

    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).

  • #16745

    Alexander Bautz
    Keymaster

    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

You must be logged in to reply to this topic.