Totals Row

Forums vLooup for SharePoint Totals Row

This topic contains 28 replies, has 3 voices, and was last updated by  Alexander Bautz 3 weeks, 2 days ago.

  • Author
    Posts
  • #19367

    Hi,

    I got the option to show me the total value of all my lookup items. Is it possible to work with this totals value? e.g. use this in my form in a field.

    I have a invoice in a list. and the different items of the invoice are lookups into another list. to avoid wrong calculation I would like to use the totals row value to show the total of the invoice? does this explanation make sense?

    thanks

  • #19396

    Alexander Bautz
    Keymaster

    Hi,
    You can access the total value for a specific field like this for use in custom code:

    varMyTot = spjs.vLookup.dataObj.YOUR_vLookup_FIELD_NAME[spjs.dffs.data.thisItemID].tot.YOUR_FIELD_NAME

    Change “YOUR_vLookup_FIELD_NAME” for your vLookup field name and “YOUR_FIELD_NAME” with the field you want the total of.

    You can for example add this to your custom js to have the value from the field “PercentCompleted” in the vLookup column named “vLookupTasks” written to a field named “Test123”:

    function vLookupIsLoadedCallback(fin){
        if(fin === "vLookupTasks"){
        	setFieldValue("test123",spjs.vLookup.dataObj.vLookupTasks[spjs.dffs.data.thisItemID].tot.PercentCompleted);
        }
    }

    Alexander

  • #19416

    Hi Alexander,

    that works so far. The only issue I found is when there is only one item in the lookup. This returns no value in the totals field and therefore the test123 field stays empty.

    Is there a work around so also one item returns a totals value?

    thanks.

  • #19419

    Hi Alexander,

    I just ran into another issue. When I put the code into the JS section of the EditForm everything works well beside the issue in my previous post but today in the morning i recognized that the code is gone.

    thanks.
    Christoph

  • #19436

    Alexander Bautz
    Keymaster

    Hi,
    I cannot recreate the issue where only one item doesn’t show total – can you show me a screenshot?

    Regarding the missing code in custom js:
    I haven’t seen this happen before, but maybe there were some sort of save error?

    If you continue having the same problem, please use F12 > Console to see if you have any errors in the console while reloading the config or saving.

    Alexander

  • #19460

    Hi Alexander,

    I guess the issue with the totals row has to do with the missing code after seconds. Attached you can see two screenshots taken one after the other (see the time in the right bottom corner). the interesting part is the time when the code got save. there is one hour difference but the screenshot with no code was taken later.

    thanks.
    Christoph

  • #19470

    Hi Alexander,

    is there also a way to define when the script is executed? I guess by default it is executed on load, right? Is there any chance to run this little script on save? so when I click on save?

    thanks.

  • #19489

    Alexander Bautz
    Keymaster

    Hi,
    Maybe you had two EditForm configs open at the same time? – I have fixed an issue where you didn’t get a version conflict on save in this scenario – are you using the latest version?

    To run the code on save, use it like this:

    function dffs_PreSaveAction(){
        setFieldValue("test123",spjs.vLookup.dataObj.vLookupTasks[spjs.dffs.data.thisItemID].tot.PercentCompleted);
    }

    Alexander

    • This reply was modified 9 months ago by  Alexander Bautz. Reason: Fixed typo
  • #20067

    Martin Čuchran
    Participant

    Hi,

    i have problems with grand totals feature in my vlookup. When i turn on this option, grand totals are displayed 3 times in view. I have attached screenshot of my configuration and also view where problem occured.

    DFFS Backend v4.4.3.31|CSS version: 4.41 / 4.41
    spjs-utility version: 1.314
    vLookup plugin backend v2.2.108

    Do you have some solution/fix for this issue?

    Thank you.
    Martin

  • #20094

    Alexander Bautz
    Keymaster

    Hi,
    I set up a test, but could not recreate this behavior. I think maybe this is related to a problem I fixed in a later release. Could you try upgrading to the latest release to see if it fixes this issue?

    Alexander

    • #20105

      Martin Čuchran
      Participant

      Hi,

      new version fixed my issue, but i have found out another. When i set showTotals:false to specific field, it is working just for first level of total and not for grand total. Is is possible to somehow apply this setting to grand total?

      Thank you
      Martin

      Attachments:
    • #20125

      Alexander Bautz
      Keymaster

      I’m glad it fixed the original problem. I have confirmed your new findings and will fix it in the next release.

      Alexander

  • #20108

    Hi Alexander,

    I tried to use your second code snippet but now I have the problem it will not let me save the form.

    function dffs_PreSaveAction({
    setFieldValue(“test123”,spjs.vLookup.dataObj.vLookupTasks[spjs.dffs.data.thisItemID].tot.PercentCompleted);
    }

    and I think you miss a closing bracket in the code. can that be?

    thanks.
    Christoph

    • #20122

      Alexander Bautz
      Keymaster

      Hi,
      Yes, the code was misspelled – try this:

      function dffs_PreSaveAction(){
          setFieldValue("test123",spjs.vLookup.dataObj.vLookupTasks[spjs.dffs.data.thisItemID].tot.PercentCompleted);
      }

      Alexander

  • #20127

    Hi Alexander,

    even with new new code snippet I still cannot save the form. The issue seems to be with the New Form and the code snippet. When I have the code in the NewForm Custom JS section it will not allow me to save.

    When i delete it from the Newform and just use it in the edit form, it will work when I reenter the form, add the lines and then it will transfer the numbers correctly but this is not how it should work.

    Any solution for this that the numbers get copied already with the newform?

    thanks.
    Christoph

  • #20174

    Alexander Bautz
    Keymaster

    Ah – I think you must add return true like this:

    function dffs_PreSaveAction(){
        setFieldValue("test123",spjs.vLookup.dataObj.vLookupTasks[spjs.dffs.data.thisItemID].tot.PercentCompleted);
        return true
    }

    If this doesn’t help, hit F12 to bring up the developer tools. Look at the “Console” and let me know what kind of error you get there.

    Alexander

  • #20189

    Hi Alexander,

    this addition to the code still does not resolve the issue. Following is the error from the console:

    SCRIPT5007: Unable to get property ‘tot’ of undefined or null reference

    thanks.
    Christoph

  • #20205

    Alexander Bautz
    Keymaster

    The vLookup’s are “lazy-loaded” and you must have it visible in a tab before you can use the spjs.vLookup.dataObj

    Could this explain it?

    Alexander

  • #20207

    Hi,

    I think I know what you mean but this still does not solve the issue.

    This works with the initial code snippet you gave me but also this is kind of not the solution we are looking for. Would be a workaround.

    christoph

  • #20233

    Alexander Bautz
    Keymaster

    Did you ensure the vLookup “children” was loaded? if they are, you can bring up the developer console (hit F12 > Console) to look at the totals object by typing in the snippet:

    spjs.vLookup.dataObj.YOUR_VLOOKUP_FIELD_NAME[spjs.dffs.data.thisItemID].tot

    Do you see your field name with a total value?

    Alexander

  • #20247

    Hi Alexander,

    following code i see:

    The total value of the line items above is stored in the code as following:

    <td class="vLookupTotals vLookup_total_vlookupItems" valign="top">VALUE< /td>

    The Value of the line item is stored in the code as following:

    td class="childTableCell vLookupItems_Net_x0020_Value" valign="top" style="width:100px,max-width:100px;">

    My custom JS code is following which is implemented in the New Item FOrm and in the Edit Item Form:

    function dffs_PreSaveAction(){
     	setFieldValue("NET_x0020_Value",spjs.vLookup.dataObj.vLookupItems[spjs.dffs.data.thisItemID].tot.NET_x0020_Value);
     	return true
        }

    maybe you also need to know the Field NET_x0020_Value is in the Parent List and in the Cild List available. In the Parent List it is just a text field and in the childlist it is a calculated field where it calculates the product of amount and price per unit.

    Let me know if you need further details.

    thanks.
    Christoph

  • #20279

    Alexander Bautz
    Keymaster

    The HTML in the page is not actually used when you use the “spjs.vLookup.dataObj”, but because the “dataObj” doesn’t exist before the vLookup children query has returned the data you must have the vLookup child table visible. What I wanted you to do was to hit f12 go bring up the dev console and then type in this in the console:

    spjs.vLookup.dataObj.vLookupItems[spjs.dffs.data.thisItemID].tot

    When you hit enter, you should see “NET_x0020_Value” as one of the properties in the output in the console. This is only used to ensure your are accessing the correct properties of the “dataObj”, and if you see the correct properties, you should be able to use the variable “spjs.vLookup.dataObj.vLookupItems[spjs.dffs.data.thisItemID].tot.NET_x0020_Value” in your “dffs_PreSaveAction” function.

    Alexander

  • #20286

    Hi Alexander,

    sorry for the wrong info I sent.

    I tried again to get what you asked for. see screenshot attached. I would like to use the total of the lines. in this case it would be the 275.125.

    thanks.
    Christoph

  • #20297

    Alexander Bautz
    Keymaster

    Ah sorry, I didn’t realize you were using it from NewForm. You must change your snippet like this when accessing it from NewForm because the element doesn’t have a valid ID yet:

    spjs.vLookup.dataObj.vLookupItems[getFieldValue("_vLookupID")].tot

    Please note that getFieldValue(“_vLookupID”) is used in NewForm, but spjs.dffs.data.thisItemID must be used in DispForm or EditForm.

    Alexander

  • #20299

    Hi Alexander,

    many thanks!! Problem solved 🙂

    christoph

  • #21325

    Hi Alexander,

    I ran now into another issue. I have 4 tabs. on the first tab there is basic information, just fields to enter information. the second tab contains the lookup.

    when I get into edit form and i stay on the first tab and change some info there and try to save the form, it will not save and close the form.

    If I do the same thing after changing once to the second tab where the lookup is then I can save and close the form.

    this is not perfect. is there any solution for this?

    thanks for your help.

    Christoph

  • #21340

    Alexander Bautz
    Keymaster

    Like I mentioned further up in this thread, the vLookup items are “lacy loaded” when the vLookup field is shown in a tab. This means that if you don’t show it in a tab, the data isn’t accessible and your code will fail (with an error in F12 > Console). In this case you must wrap the function call like this to only run it when the data is loaded:

    if(spjs.vLookup.dataObj.vLookupItems !== undefined){
        // Put your code here - your vLookup data has loaded
    }

    Alexander

  • #22861

    Hi Alexander,

    maybe I am already too much ready for holidays or I am missing something.

    Following code I have in the NewForm:

    
    
    function dffs_PreSaveAction(){
        	setFieldValue("NET_x0020_Value",spjs.vLookup.dataObj.vLookupItems[getFieldValue("_vLookupID")].tot.NET_x0020_Value);
        return true
    }

    Following Code I do have in the EditForm

    
    
        function dffs_PreSaveAction(){
        	setFieldValue("NET_x0020_Value",spjs.vLookup.dataObj.vLookupItems[spjs.dffs.data.thisItemID].tot.NET_x0020_Value);
        return true
    }

    but I cannot save the form when I edit the entry.

    What do I miss?

    thanks.

  • #22869

    Alexander Bautz
    Keymaster

    Hi,
    To use the vLookup dataObj the vLookup table must have been loaded. If you have this loaded already, you must hit F12 to bring up the console and see if there is any errors there.

    Alexander

You must be logged in to reply to this topic.