Sum multiple vLookup Total columns

[avala]

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?

Attachments:

1. 

   Example1.png
   

2. 

   Example0.png
   

3. 

   Example2.png
   

[Alexander Bautz]

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

[avala]

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

}

[Alexander Bautz]

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

Alexander

[avala]

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);
}
}
}

[Alexander Bautz]

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

[avala]

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:

1. 

   DFFS-Version.png
   

[Alexander Bautz]

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

[avala]

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.

[Alexander Bautz]

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

[Brent Caudill]

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);
}

[Alexander Bautz]

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

[Brent Caudill]

Thanks Alexander!! That worked perfectly.

[Alexander Bautz]

I’m glad you got it working.

Alexander

[Author]

Posts