Sum action in vLookups

Forums vLooup for SharePoint Sum action in vLookups

Tagged: 

Viewing 9 reply threads
  • Author
    Posts
    • #7481
      Juanma
      Participant

      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

    • #7521
      Alexander Bautz
      Keymaster

      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

    • #9670
      avala
      Participant

      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 5 years, 9 months ago by avala.
      Attachments:
    • #9674
      avala
      Participant

      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 5 years, 9 months ago by avala.
    • #9746
      Alexander Bautz
      Keymaster

      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

      • #9757
        avala
        Participant

        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);
        }
    • #9768
      Alexander Bautz
      Keymaster

      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

    • #9776
      avala
      Participant

      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 5 years, 8 months ago by avala.
    • #9779
      Alexander Bautz
      Keymaster

      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

      • #9781
        avala
        Participant

        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 5 years, 8 months ago by avala.
    • #9784
      Alexander Bautz
      Keymaster

      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

      • #9788
        avala
        Participant

        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);
        }
    • #9796
      Alexander Bautz
      Keymaster

      I’m glad it worked!

      Best regards,
      Alexander

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