Home › Forums › vLooup for SharePoint › Sum multiple vLookup Total columns
Tagged: DFFS; vLookup
- This topic has 13 replies, 3 voices, and was last updated 8 years, 7 months ago by Alexander Bautz.
-
AuthorPosts
-
-
March 3, 2016 at 20:31 #10589
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:
-
March 4, 2016 at 20:30 #10623
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-
March 23, 2016 at 21:16 #10883
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); }
-
-
March 25, 2016 at 09:46 #10905
Look at the “events” described in the user manual: https://spjsblog.com/vlookup-for-sharepoint/vlookup-user-manual/#vLookupIsLoadedCallback
Alexander
-
March 29, 2016 at 15:05 #10936
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); } } }
-
March 31, 2016 at 18:23 #10963
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 -
April 1, 2016 at 15:58 #10979
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:
-
April 5, 2016 at 16:05 #11018
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
-
April 5, 2016 at 16:49 #11024
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.
-
-
April 14, 2016 at 16:31 #11118
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
-
May 9, 2016 at 19:10 #11425
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); }
-
May 11, 2016 at 18:33 #11485
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 -
May 12, 2016 at 20:10 #11514
Thanks Alexander!! That worked perfectly.
-
May 15, 2016 at 13:05 #11550
I’m glad you got it working.
Alexander
-
-
AuthorPosts
- You must be logged in to reply to this topic.