Sum values from another field of a multi lookup field

Forums Classic DFFS Sum values from another field of a multi lookup field

Viewing 2 reply threads
  • 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

Viewing 2 reply threads
  • You must be logged in to reply to this topic.