Totals Row

[Christoph Moosbrugger]

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

[Alexander Bautz]

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

[Christoph Moosbrugger]

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.

[Christoph Moosbrugger]

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

[Alexander Bautz]

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

[Christoph Moosbrugger]

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

Attachments:

1. 

   2018-01-24-08_52_30-.png
   

2. 

   2018-01-24-08_52_00-.png
   

[Christoph Moosbrugger]

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.

[Alexander Bautz]

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 6 years, 8 months ago by Alexander Bautz. Reason: Fixed typo

[Martin Čuchran]

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

Attachments:

1. 

   vlookup_view.png
   

2. 

   configuration.png
   

[Alexander Bautz]

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

[Martin Čuchran]

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:

1. 

   vlookup.png
   

[Alexander Bautz]

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

Alexander

[Christoph Moosbrugger]

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

[Alexander Bautz]

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

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

Alexander

[Christoph Moosbrugger]

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

[Alexander Bautz]

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

[Christoph Moosbrugger]

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

[Alexander Bautz]

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

[Christoph Moosbrugger]

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

[Alexander Bautz]

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

[Christoph Moosbrugger]

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

• This reply was modified 6 years, 8 months ago by Christoph Moosbrugger.

[Alexander Bautz]

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

[Christoph Moosbrugger]

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

Attachments:

1. 

   2018-03-18-15_19_29-–-F12-Entwicklungst.png
   

[Alexander Bautz]

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

[Christoph Moosbrugger]

Hi Alexander,

many thanks!! Problem solved 🙂

christoph

[Christoph Moosbrugger]

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

• This reply was modified 6 years, 4 months ago by Christoph Moosbrugger.

[Alexander Bautz]

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

[Christoph Moosbrugger]

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.

[Alexander Bautz]

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

[Christoph Moosbrugger]

Hi,

I am totally confused now… When the error happened I did the check with the console.

the console brings up following error: SCRIPT5007: SCRIPT5007: Unable to get property ‘629’ of undefined or null reference

it seems it cannot find the item with “thisItemID”.

I just randomly did get into the editing of the EditForm, saved it without changing anything and now it works but on all instances (we have several different locations where we use this code).

weird things happen…

thanks.
Christoph

[Alexander Bautz]

It’s hard to tell what caused this, but please let me know if you find out more, or you have any further questions.

Alexander

[Author]

Posts