Sum action in vLookups

[Juanma]

Hi Alexander and all,

I love this functionality. Actually I just included to be able to associate expenses (childs) to each of the contracts (parents) in a list.

The contract has a budget, and the expenses should not go beyond that limit.

I have a question here:

I know I can have the vLookup column in the parent list set as “Sum”, to have an aggregated view of the expenses. However, could I use that in order to include in the parent list a field as “Remaining budget” being a calculated field between Budget minus the sum of the expenses?

Your help is much appreciated.

Thanks!

Br,

Juanma

[Alexander Bautz]

Hi,
Sorry for the late reply.

There are currently no such option is vLookup or DFFS so this would require a few lines of custom code. Can you provide a few screenshots so I can take a look and see what it takes?

Alexander

[avala]

Alexander,

I’m in a similar situation where I’d like to do a calculation in the parent form from the _vLookup children totals. We have a few additional requests coming in the next month that will require a similar function. Is there a way to pull the TD.vLookupTotals while in the parent form or an alternate solution?

I’ve included an attachment for reference.

• This reply was modified 8 years, 10 months ago by avala.

Attachments:

1. 

   vLookup-Totals.png
   

[avala]

As a quick follow up, this is the custom JS I’m using in the form. I’d like to populate the Subtotal field with the Sum from the Line Total Column in the vLookup attachment (see post attachment above).

fields = init_fields_v2();

function CalcTotal()
{

var Subtotal = getFieldValue('Subtotal');
var Tax = getFieldValue('Sales_x0020_Tax');
var Prod = ((Subtotal*Tax)/100);
var TotalCalc = (+Prod + +Subtotal);

setFieldValue('Total',TotalCalc);
}

• This reply was modified 8 years, 10 months ago by avala.

[Alexander Bautz]

Hi,
Sorry for the delay. To pull the value from “Line Total” in your vLookup table you can use this line of code:

var vLookupTotal = $(".vLookup_LineTotal_vLookupTasks").text();

You must change the text “LineTotal” to the fieldinternalname from the “child list” and “vLookupTasks” to your vLookup columns fieldinternalname.

Alexander

[avala]

No worries. I appreciate you taking the time to answer our little questions.

This is the code I’m currently using (I’ve commented out the rest until I can get the Subtotal working). I’ve grabbed the field internal name from the child list for Line Total (Line_x0020_Total) and used the field internal name for the vLookup (vLookup) field in the parent. Is this correct? When the function is triggered it empties the Subtotal field.

fields = init_fields_v2();

function CalcTotal()
{

var vLookupTotal = $(".vLookup_Line_x0020_Total_vLookup").text();
//var Subtotal = getFieldValue('Subtotal');
//var Tax = getFieldValue('Sales_x0020_Tax');
//var Prod = ((Subtotal*Tax)/100);
//var TotalCalc = (+Prod + +Subtotal);

setFieldValue('Subtotal',vLookupTotal);
//setFieldValue('Total',TotalCalc);
}

Attachments:

1. 

   2016-01-05_8-37-18.gif
   

2. 

   2016-01-05_8-38-08.gif
   

[Alexander Bautz]

I forgot to tell that the selector uses a class on the totals “cell” to grab the correct value. I looked in the change log, but I must have forgot to mention it there. If you grab the latest version of vLookup from the DFFS download package it should work.

Let me know how it works out.

Alexander

[avala]

Alexander,

The Subtotal field is still being cleared on function run. I’ve attempted the following:

1. Updated all DFFS files and plugins to December 14th, 2015 versions
2. Updated the following files to the January 03, 2016 BETA 1
DFFS_DirectToBackend.html
vLookup_ListView.html
vLookup_NewForm.html
SPJS-vLookup_backend.js
SPJS-vLookup_frontend.js
SPJS-vLookup_receiver.js
SPJS-utility.js
jquery.js
DFFS_backend_min.js
DFFS_frontend_min.js

This is my code:

fields = init_fields_v2();

function CalcTotal()
{

var vLookupTotal = $(".vLookup_Line_x0020_Total_vLookup").text();
setFieldValue('Subtotal',vLookupTotal);

//var Subtotal = getFieldValue('Subtotal');
//var Tax = getFieldValue('Sales_x0020_Tax');
//var Prod = ((Subtotal*Tax)/100);
//var TotalCalc = (+Prod + +Subtotal);
//var Subtotal = getFieldValue('Subtotal');

//setFieldValue('Total',TotalCalc);
}

• This reply was modified 8 years, 9 months ago by avala.

[Alexander Bautz]

It’s hard to tell without looking at it, but how are you running the code? – remember that the vLookup data is loading async, and may not be ready when your code runs.

Try bringing up the dev console (hit F12 > Console) in the form and write this in the Console:

$(".vLookup_Line_x0020_Total_vLookup").text();

What is the output?

Alexander

[avala]

I’ve updated all DFFS files to the January 03 Beta (v4.365 BETA 1).
Ran the following in the Dev Console: $(“.vLookup_Line_x0020_Total_vLookup”).text(); which returned, “”

The function is run on the following rules:

Rule 1
If Subtotal
Is Changed
Validate on Form Load and field change
Run these functions: CalcTotal

Rule 2
If SalesTax
Is Changed
Validate on Form Load and field change
Run these functions: CalcTotal

It appears to be running just fine, it’s just returning an empty string. When I comment out the new subtotal var and setfield value the Total Calculations work perfectly.

• This reply was modified 8 years, 9 months ago by avala.

[Alexander Bautz]

If it returns empty, the selector must be wrong. Use the dev tools to inspect (right click > inspect) the vLookup total to see if it has the class you try to select.

Alexander

[avala]

Bingo!
The correct class was vLookup_total_vLookup and not the vLookup field “Line_x0020_Total”. THANK YOU for your help on this- totally above and beyond!

Final code:

fields = init_fields_v2();

function CalcTotal()
{

var vLookupTotal = $(".vLookup_total_vLookup").text();
setFieldValue('Subtotal',vLookupTotal);

var Subtotal = getFieldValue('Subtotal');
var Tax = getFieldValue('Sales_x0020_Tax');
var Prod = ((vLookupTotal*Tax)/100);
var TotalCalc = (+Prod + +vLookupTotal);

setFieldValue('Total',TotalCalc);
}

[Alexander Bautz]

I’m glad it worked!

Best regards,
Alexander

[Author]

Posts