Populate parent fields from related items

[dejan.lamovsek]

Hello guys,

I'm working on some forms with related items. To be more accurate, I have "Contracts" as parent and "Annexes" as related childs. What I want to achieve is to have two data in Parent getting from childs.

- One is the "Due Date", which is the max of all dates in the Annexes and Contract "Due Date" fields and

- the other is "Value", which is the SUM of Contract "Value" field and all Annexes "Value" fields.

So when a new child is saved or there is a change on existing one, the parent fields should populate.
Maybe there is a simple solution to do this, but I didn't find it on Forum. I'm also not an IT specialist, so please help me out with this.

Thanks in advance,
bye

[Nikita Kurguzov]

Dear Dejan,
Unfortunately, there is no direct way to detect change in Related Items. But what you want is possible to do with an interval timer that will check Related Items and recalculate all the fields from time to time, let's say every 5 seconds. It's not the most efficient way and if you have really large number of children items per parent, it would be better to use a button instead that will launch all the calculations when necessary.

I can probably write the code for you, if you can tell me more about all the fields that need to be checked:
1) What fields on the parent need to be checked? You mentioned "Due Date" and "Value", but you also wrote that these fields need to be replaced with other values, depending on values in Related Items. So, are there two fields, like "ContractValue" and "TotalValue"? Or is it just one field? Then, should it be checked or only children fields?
2) Please, provide correct InternalNames for all the fields, both Parent and Child, they can be different from the names you see in the title. For example, "Due Date" is most likely to be "Due_x0020_Date", but it can also be "DueDate" or something else.
You can check internal names of the fields in the designer:

InternalName.png (3.89 KiB) Viewed 2118 times

3) Finally, are you using Quick Edit mode for Related Items or just regular mode? Not essential for me to know, but might help me to provide you with better answer.

[dejan.lamovsek]

Dear Nikita,

Thanks for quick reply. To be more accurate, I prepared an image (values and titles are fictitious):

PopulateParent.JPG (200.37 KiB) Viewed 2117 times

Red fields are values that should be sumed in field "Celotna vrednost brez DDV" = in English "Total Value", green fields are due dates and max value of them should be recorded in field "Končni rok" = in English "Max Date".
There is another color in this image - orange. This is another problem I have, all totals are shifted to the right as you see but I can remove them since they will be copied in parent fields.

I'm totaly comfortable with a button that will populate the fields, I try to avoid unnecessary internal timers. There should be a button to populate and also if this button is not clicked, a "Save" button should populate them, so the saved Items have allways correct values.

1, 2) Yes, you are right. There are two fields for Value (Contract Value [VrednostBrezDDV] and Total Value [CelotnaVrednostBrezDDV] and also two fields for Date (Contract Date [Veljavnost] and Max Date [KoncniRok]). Internal names are inside [ ]. Both Parent and Childs values and dates should be taken into account for Total Value and Max Date.
Childs internal names are the same, because they are in the same Document Library, but are different Content Types (Aneks). There are some other Content Types in the form as second Related Item and values of them shouldn't be taken into account, so the filter must be set on Content Type or View.
Child Content Type "Aneks" doesn't have fields Total Value [CelotnaVrednostBrezDDV] and Max Date [KoncniRok], so calculations for parent are done from fields Contract Value [VrednostBrezDDV] and Contract Date [Veljavnost].

3) No, I am not using Quick Edit mode for Related Items.

I hope I didn't complicate my explanation too much.

Thanks,
bye

[Nikita Kurguzov]

Dear Dejan,
Try to run this code from JavaScript editor:

Code: Select all

function calculateTotal(){
	var total = parseFloat(fd.field('VrednostBrezDDV').value());
	var rows = fd.relatedItems(0).data('ctx').ListData.Row;
	rows.forEach(function(item) {
	    //it's important to include . after the name of the field here
	    //it stores actual numeric value, not presented value which is not a number
	    total += parseFloat(item['VrednostBrezDDV.'])
	});
	fd.field('CelotnaVrednostBrezDDV').value(total);
}

function calculateMaxDate(){
	var maxDate = new Date(fd.field('Veljavnost').value());
	var rows = fd.relatedItems(0).data('ctx').ListData.Row;
	rows.forEach(function(item) {
	    var date = new Date(item['Veljavnost']);
		if (date > maxDate)
			maxDate = date;
	});
	fd.field('KoncniRok').value(maxDate);
}

//add button with CSS class: calculate-button
//don't add any scripts on click, this is enough:
$('.calculate-button').click(function(){
	calculateTotal();
	calculateMaxDate();
});

//fields will also be calculated automatically prior to saving:
fd.onsubmit(function(){
	calculateTotal();
	calculateMaxDate();
	return true;
});

It might not work if some names are not correct, but it runs for me with my field names. If you encounter any issues or console errors - let me know! Screenshots of console errors might be helpful in that case.

[dejan.lamovsek]

Hello Nikita,

Thanks for that code. We are getting in right direction but still not there yet The following problems are:

1.) The code gives me "NaN" in TotalValue field and "NaN.NaN.NaN" in MaxDate field if I have Related Items in the Accordion section.

2.) If I put Related Items out of the Accordion the calculation of TotalValue is done, but wrong. It sumes all Childs +1. It should sume all Childs + Parent.

3.) MaxDate field is allways populated with "NaN.NaN.NaN", doesn't matter if it is in or out of the Accordion section. Maybe it is a format problem (We use dd. mm. yyyy). I also tried to populate a text field with MaxDate value and it gives me "Invalid Date".

If the solution could work in a Accordion and/or Tab Control section it would be great. Thanks in advance for the effort.

bye

[Nikita Kurguzov]

Dear Dejan,
Not sure what's the problem with the accordion, it works for me just fine outside and inside of it, and I can't think of a reason why it wouldn't.

What you can do is debug the code in browser's console, everything is very straightforward, I can walk you through all the lines of code. Try it both inside and outside Accordion to see if there is any difference, any errors popping up, etc.

First, populate the form and try to run this command in console:

Code: Select all

//should return value of the field as a number:
parseFloat(fd.field('VrednostBrezDDV').value());

Next, try to run this command:

Code: Select all

        var total = 0;
        //this takes all the rows in Related Items
	var rows = fd.relatedItems(0).data('ctx').ListData.Row;
	rows.forEach(function(item) {
	    //it's important to include . after the name of the field here
	    //it stores actual numeric value, not presented value which is not a number
	    total += parseFloat(item['VrednostBrezDDV.'])
	});
	//should return total sum of VrednostBrezDDV in the Related Items
	total;

If it doesn't work, try to run this instead, it will give you all the values it finds:

Code: Select all

        var rows = fd.relatedItems(0).data('ctx').ListData.Row;
	rows.forEach(function(item) {
	    console.log(item['VrednostBrezDDV']);
	    console.log(parseFloat(item['VrednostBrezDDV.']));
	});

Do the same with dates, will it return correct values:

Code: Select all

new Date(fd.field('Veljavnost').value());

And this:

Code: Select all

        var rows = fd.relatedItems(0).data('ctx').ListData.Row;
	rows.forEach(function(item) {
	    console.log(new Date(item['Veljavnost']));
	});

Tell me if you encounter any errors or unexpected values, this will help us solve the problem.

[dejan.lamovsek]

Hello Nikita,

Code 1: well yes it returns a number, but it takes the first . or , as a decimal point. In my case the number was one Million written like 1.000.000,00 so the first point is right behind one and therefore it resulted 1. That is also the reason that instead 1MIO only 1 was added to the result. So the formula calculates right but the shape botters it.

Code 2: gives me the Sum of all childs

Code 3: gives me seperate values of each child

Code 4: gives me: [date] Invalid Date [date] Invalid Date

Code 5: gives me twice the same as code 4. There is an arrow in front of this line, so I can open it and there is thousends of functions listed under it.

Is there anything else any other information I can send you to break this issue?

Thanks, bye,
Dejan

[dejan.lamovsek]

Nikita hi,

I tried runnig your codes in the console while childs are in accordion. All results are the same except of code 2 and 3:

Code 2: gives me NaN

Code 3: gives me undefined and NaN

Any progress?

Thanks, bye

[dejan.lamovsek]

Nikita hi,

Don't waste time on this any more, I resolved the TotalValue and MaxDate problems, but I still have problems with the accordion. Any clue?


Thanks, bye

[dejan.lamovsek]

Hello Nikita,

Don't bother I also solved the accordion problem. To summarize:
- the problem of fields was that they were not in the right format, so I had to reform and form them again back to fit in the js calculation. Sting to Date and again Date to String and so on.
- the accordion problem was in line

var rows = fd.relatedItems(0).data('ctx').ListData.Row;

It was my problem, because I have two related tables in my form and the right one to calculate was the second one. So I had to change the number to 1 , so the line looks now like:

var rows = fd.relatedItems(1).data('ctx').ListData.Row;

All the problems posted in this post are now solved. Thank you for the code.

Cheers,
Dejan