Sum multiple vLookup Total columns

Forums vLooup for SharePoint Sum multiple vLookup Total columns

Tagged: 

This topic contains 13 replies, has 3 voices, and was last updated by  Alexander Bautz 1 year, 3 months ago.

  • Author
    Posts
  • #10589

    avala
    Participant

    This request is an extension of Juanma’s postJuanma’s post.

    Using four vLookup fields. Each vLookup collects Man Hours per job. I then have a custom function run on the MH field change to multiply the hours by a static rate and output the data into total estimate man-hours and total estimated cost (example 0). Man Hours and cost is totaled into a EstimatedMH field and TotalEstimate field.

    
    
    fields = init_fields_v2();
    
    function CalcTotal()
    {
    //Get Man-hours
    var LeadDesignerMH= getFieldValue('Lead_x0020_Designer_x002f_Tech_x');
    var DesignerMH= getFieldValue('Designer_x0020_MHs');
    var InternMH= getFieldValue('Other_x0020__x002d__x0020_Intern');
    var TechnicianMH= getFieldValue('Technician_x0020_MHs');
    
    //Get Rates
    var LeadDesignerRate= getFieldValue('LeadDesignerRate');
    var DesignerRate= getFieldValue('DesignerRate');
    var InternRate= getFieldValue('OtherInterRate');
    var TechnicianRate= getFieldValue('Technician_x0020_Rate');
    
    //Calculations
    var LeadDesigner= (LeadDesignerMH*LeadDesignerRate);
    var Designer= (DesignerMH*DesignerRate);
    var Intern= (InternMH*InternRate);
    var Technician= (TechnicianMH*TechnicianRate);
    
    var EstimateMH= (+LeadDesignerMH + +DesignerMH + +InternMH + +TechnicianMH)
    var TotalCalc = (+LeadDesigner + +Designer + +Intern + +Technician);
    
    setFieldValue('Estimated_x0020_MHs',EstimateMH);
    setFieldValue('Total_x0020_Estimate',TotalCalc);
    }

    Each of these vLookups then use the Sum total row (example 1) to show the total MH’s and Cost per vLookup. We then need to aggregate to total estimated MH’s and Cost from all four vLookup.

    In using the solution outlined in Juanma’s original post, we can easily do this if we are using just One column, however since each lookup returns TWO values in the vLookupTotals row, the TotalCalc function is combining both rows into a single, string value before adding them together example 2).

    My current thought is to point the TotalCalc function to look at the EstimatedMH column and TotalEstimate columns separately, convert the result into an array and then sum up the values for each vLookup table.

    Before I start all of this, am I missing an easier solution?

  • #10623

    Alexander Bautz
    Keymaster

    Hi,
    I’m not 100% sure I understand, but you can get the “total” value for the third column of the vLookup field “vLookupTasks” this:

    $(".vLookup_total_vLookupTasks:eq(2)").text()

    Also, you can access the data object directly like this:

    spjs.vLookup.dataObj.vLookupTasks

    This is the raw data and you would have to iterate over each item like this:

    $.each(spjs.vLookup.dataObj.vLookupTasks[GetUrlKeyValue("ID")].items,function(i,item){
    	console.log(item.NumberColumnName);
    });

    Hope this helps,
    Alexander

    • #10883

      avala
      Participant

      Thanks, Alexander. This code, combined with some currency functions, works like a dream. The last hurdle is to run this function after the vLookup tables have loaded. I’m currently calling the function from an HTML button, but I’d like to automate this if possible.

      For anyone in a similar situation:

      
      
      fields = init_fields_v2();
      
      function AggregateTotals()
      {
      //get Man hours
      var totalVDCMH= $(".vLookup_total_vLookupVDC:eq(13)").text();
      var totalProposalMH= $(".vLookup_total_vLookupProposal:eq(11)").text();
      var totalDesignMH= $(".vLookup_total_vLookupDesign:eq(17)").text();
      
      //get cost
      var totalVDCCost= $(".vLookup_total_vLookupVDC:eq(14)").text();
      var totalProposalCost= $(".vLookup_total_vLookupProposal:eq(12)").text();
      var totalDesignCost= $(".vLookup_total_vLookupDesign:eq(18)").text();
      
      //calculate totals
      var totalMH= (+totalVDCMH+ +totalProposalMH+ +totalDesignMH);
      
      var totalnewVDCCost = parseFloat(totalVDCCost.replace(/[^0-9-.]/g, ''));
      var totalnewProposalCost = parseFloat(totalProposalCost.replace(/[^0-9-.]/g, ''));
      var totalnewDesignCost = parseFloat(totalDesignCost.replace(/[^0-9-.]/g, ''));
      
      var totalCost= (totalnewVDCCost +totalnewProposalCost +totalnewDesignCost );
      
      function CurrencyFormatted(amount)
      {
      	var i = parseFloat(amount);
      	if(isNaN(i)) { i = 0.00; }
      	var minus = '';
      	if(i < 0) { minus = '-'; }
      	i = Math.abs(i);
      	i = parseInt((i + .005) * 100);
      	i = i / 100;
      	s = new String(i);
      	if(s.indexOf('.') < 0) { s += '.00'; }
      	if(s.indexOf('.') == (s.length - 2)) { s += '0'; }
      	s = minus + s;
      	return s;
      }
      
      function CommaFormatted(amount)
      {
      	var delimiter = ","; // replace comma if desired
      	var a = amount.split('.',2)
      	var d = a[1];
      	var i = parseInt(a[0]);
      	if(isNaN(i)) { return ''; }
      	var minus = '';
      	if(i < 0) { minus = '-'; }
      	i = Math.abs(i);
      	var n = new String(i);
      	var a = [];
      	while(n.length > 3)
      	{
      		var nn = n.substr(n.length-3);
      		a.unshift(nn);
      		n = n.substr(0,n.length-3);
      	}
      	if(n.length > 0) { a.unshift(n); }
      	n = a.join(delimiter);
      	if(d.length < 1) { amount = n; }
      	else { amount = n + '.' + d; }
      	amount = minus + amount;
      	return amount;
      }
      
      var result = CurrencyFormatted(totalCost);
      result = CommaFormatted(result);
      
      setFieldValue('TotalEstimatedMHs',totalMH);
      setFieldValue('TotalEstimatedCost',"$" +result);
      
      }
  • #10905

    Alexander Bautz
    Keymaster

    Look at the “events” described in the user manual: http://spjsblog.com/vlookup-for-sharepoint/vlookup-user-manual/#vLookupIsLoadedCallback

    Alexander

  • #10936

    avala
    Participant

    Thanks for the reminder. I’m struggling to get these events working in our Edit form and I’m sure I’m missing something obvious:

    
    
    function vLookupIsLoadedCallback(fin){
    if(fin === "vLookup"){
    
    fields = init_fields_v2();
    function CalcTotal2()
    {
    
    var vLookupTotal = $(".vLookup_total_vLookup:eq(6)").text();
    setFieldValue('Subtotal',vLookupTotal);
    
    var Subtotal = getFieldValue('Subtotal');
    var Subtotalnew = parseFloat(Subtotal .replace(/[^0-9-.]/g, ''));
    var Tax = getFieldValue('Sales_x0020_Tax');
    var Prod = ((Subtotalnew*Tax)/100);
    var TotalCalc = (+Prod + +Subtotalnew);
    
    function CurrencyFormatted2(amount)
    {
    	var i = parseFloat(amount);
    	if(isNaN(i)) { i = 0.00; }
    	var minus = '';
    	if(i < 0) { minus = '-'; }
    	i = Math.abs(i);
    	i = parseInt((i + .005) * 100);
    	i = i / 100;
    	s = new String(i);
    	if(s.indexOf('.') < 0) { s += '.00'; }
    	if(s.indexOf('.') == (s.length - 2)) { s += '0'; }
    	s = minus + s;
    	return s;
    }
    
    function CommaFormatted2(amount)
    {
    	var delimiter = ","; // replace comma if desired
    	var a = amount.split('.',2)
    	var d = a[1];
    	var i = parseInt(a[0]);
    	if(isNaN(i)) { return ''; }
    	var minus = '';
    	if(i < 0) { minus = '-'; }
    	i = Math.abs(i);
    	var n = new String(i);
    	var a = [];
    	while(n.length > 3)
    	{
    		var nn = n.substr(n.length-3);
    		a.unshift(nn);
    		n = n.substr(0,n.length-3);
    	}
    	if(n.length > 0) { a.unshift(n); }
    	n = a.join(delimiter);
    	if(d.length < 1) { amount = n; }
    	else { amount = n + '.' + d; }
    	amount = minus + amount;
    	return amount;
    }
    
    var result = CurrencyFormatted2(TotalCalc);
    result = CommaFormatted2(result);
    
    setFieldValue('Total',"$" +result);
    }
    }
    }
  • #10963

    Alexander Bautz
    Keymaster

    Hi,
    There might be some issues with your code – like the variable “TotalCalc” that has a few extra + signs. I guess something was stripped away when you pasted the code here.

    This means my code example below contains errors, but you see the correct setup of the functions.

    Is your column named “vLookup” like you use in the “vLookupIsLoadedCallback” function?

    function vLookupIsLoadedCallback(fin){
    	if(fin === "vLookup"){
    		// I don't thing you need to call this if you use DFFS
    		//fields = init_fields_v2();
    		CalcTotal2();
    	}
    }
    
    function CalcTotal2(){
    	var vLookupTotal = $(".vLookup_total_vLookup:eq(6)").text();
    	setFieldValue('Subtotal',vLookupTotal);
    	var Subtotal = getFieldValue('Subtotal');
    	var Subtotalnew = parseFloat(Subtotal .replace(/[^0-9-.]/g, ''));
    	var Tax = getFieldValue('Sales_x0020_Tax');
    	var Prod = ((Subtotalnew*Tax)/100);
    	var TotalCalc = (+Prod + +Subtotalnew);
    	var result = CurrencyFormatted2(TotalCalc);
    	result = CommaFormatted2(result);
    	setFieldValue('Total',"$" +result);
    }
    
    function CurrencyFormatted2(amount){
    	var i = parseFloat(amount);
    	if(isNaN(i)) { i = 0.00; }
    	var minus = '';
    	if(i < 0) { minus = '-'; }
    	i = Math.abs(i);
    	i = parseInt((i + .005) * 100);
    	i = i / 100;
    	s = new String(i);
    	if(s.indexOf('.') < 0) { s += '.00'; }
    	if(s.indexOf('.') == (s.length - 2)) { s += '0'; }
    	s = minus + s;
    	return s;
    }
    
    function CommaFormatted2(amount){
    	var delimiter = ","; // replace comma if desired
    	var a = amount.split('.',2)
    	var d = a[1];
    	var i = parseInt(a[0]);
    	if(isNaN(i)) { return ''; }
    	var minus = '';
    	if(i < 0) { minus = '-'; }
    	i = Math.abs(i);
    	var n = new String(i);
    	var a = [];
    	while(n.length > 3)
    	{
    		var nn = n.substr(n.length-3);
    		a.unshift(nn);
    		n = n.substr(0,n.length-3);
    	}
    	if(n.length > 0) { a.unshift(n); }
    	n = a.join(delimiter);
    	if(d.length < 1) { amount = n; }
    	else { amount = n + '.' + d; }
    	amount = minus + amount;
    	return amount;
    }

    Hope this helps,
    Alexander

  • #10979

    avala
    Participant

    Thanks, Alexander. I cleaned up the code a little bit but I’m still not getting the vLookup event loaders to work. I’m able to get the functions running off a button click just fine. The entirety of my JS code is below along with our DFFS versions. If there’s anything I can test with DFFS let me know. If it’s code on our end I’ll keep researching.

    
    
    fields = init_fields_v2();
    
    function vLookupIsLoadedCallback(fin){
    	if(fin === "vLookup"){
    		CalcTotal();
    	}
    }
    
    function CalcTotal(){
    var vLookupTotal = $(".vLookup_total_vLookup:eq(6)").text();
    setFieldValue('Subtotal',vLookupTotal);
    var Subtotal = getFieldValue('Subtotal');
    var Subtotalnew = parseFloat(Subtotal .replace(/[^0-9-.]/g, ''));
    var Tax = getFieldValue('Sales_x0020_Tax');
    var Prod = ((Subtotalnew*Tax)/100);
    var TotalCalc = (Prod + Subtotalnew);
    var result = CurrencyFormatted(TotalCalc);
    result = CommaFormatted(result);
    setFieldValue('Total',"$" +result);
    }
    
    function CurrencyFormatted(amount)
    {
    	var i = parseFloat(amount);
    	if(isNaN(i)) { i = 0.00; }
    	var minus = '';
    	if(i < 0) { minus = '-'; }
    	i = Math.abs(i);
    	i = parseInt((i + .005) * 100);
    	i = i / 100;
    	s = new String(i);
    	if(s.indexOf('.') < 0) { s += '.00'; }
    	if(s.indexOf('.') == (s.length - 2)) { s += '0'; }
    	s = minus + s;
    	return s;
    }
    
    function CommaFormatted(amount)
    {
    	var delimiter = ","; // replace comma if desired
    	var a = amount.split('.',2)
    	var d = a[1];
    	var i = parseInt(a[0]);
    	if(isNaN(i)) { return ''; }
    	var minus = '';
    	if(i < 0) { minus = '-'; }
    	i = Math.abs(i);
    	var n = new String(i);
    	var a = [];
    	while(n.length > 3)
    	{
    		var nn = n.substr(n.length-3);
    		a.unshift(nn);
    		n = n.substr(0,n.length-3);
    	}
    	if(n.length > 0) { a.unshift(n); }
    	n = a.join(delimiter);
    	if(d.length < 1) { amount = n; }
    	else { amount = n + '.' + d; }
    	amount = minus + amount;
    	return amount;
    }
    Attachments:
  • #11018

    Alexander Bautz
    Keymaster

    Hi,
    Are you sure the field name is correct?

    Try setting it up like this to alert the FieldInternalname:

    function vLookupIsLoadedCallback(fin){
    	alert(fin);
    	if(fin === "vLookup"){
    		CalcTotal();
    	}
    }

    Do you see the alert?

    PS: I’m assuming you use this code in a DispForm of a list and not in a list view?

    Alexander

    • #11024

      avala
      Participant

      Thanks, Alexander. Yes, I see the alert and it says “vLookup.” I’m using this in the EditForm and not the listview. I tested this and “function vlookupChildViewOrEditCallback(fin) in the DispForm. No changes.

  • #11118

    Alexander Bautz
    Keymaster

    Hi,
    Sorry for the delay. Is the function “CalcTotal()” not triggered? – if you move the alert inside the “if(fin === “vLookup”)…” – do you still see the alert?

    If you do, I’m afraid you must step trough your other functions and add “console.log” or “alert” in your functions to determine where it stops.

    Alexander

  • #11425

    Brent Caudill
    Participant

    Hi Alexander,

    I am attempting to get a similar solution to avala’s working. I appear to be seeing a similar issue.

    I am starting very simply for testing and what I am seeing is that the callback is triggering the calcTotal function correctly (it is logging to the console), but the variable I am creating from the vLookup_total_vLookupItems is empty. It writes a line to the console, but it is blank.

    However, if I trigger the calcTotal from a button on the form it gets the value from the vLookup_total_vLookupItems correctly.

    Here is the basic code I am using.

    
    
    function vLookupIsLoadedCallback(fin){
    if(fin === "vLookupQItems"){
    calcTotal();
    }
    }
    
    function calcTotal(){
    console.log("calcTotal was triggered");
    var vtotal = $(".vLookup_total_vLookupQItems:eq(2)").text();
    console.log(vtotal);
    }
  • #11485

    Alexander Bautz
    Keymaster

    Hi,
    I would advise you to use the “dataObj” instead. I mentioned it above, but all you need to do is to add this code to you custom js:

    My vLookup field is named “vLookupTasks” and the number field I want to total is called “Num” – change the field names to match you own.

    function vLookupIsLoadedCallback(fin){
     if(fin === "vLookupTasks"){
       myTotFunction();
     }
    }
    
    function myTotFunction(){    
      var myTot = 0;  $.each(spjs.vLookup.dataObj.vLookupTasks[GetUrlKeyValue("ID")].items,function(i,item){
          // the number is in display format and needs to be parsed as a proper number
          myTot += Number(item.Num.replace(/[^\d|\.]/g,""));
      });
      console.log(myTot); 
    }

    Please note that this method is only available when the vLookup query has finished – this is why its wrapped in the “vLookupIsLoadedCallback” function.

    Hope this helps,
    Alexander

  • #11514

    Brent Caudill
    Participant

    Thanks Alexander!! That worked perfectly.

  • #11550

    Alexander Bautz
    Keymaster

    I’m glad you got it working.

    Alexander

You must be logged in to reply to this topic.