country101
Scholar

Master Summary Group

Jump to solution

Hi All,

 

Relatively new to Nintex and are after some friendly advice.

 

I have created a form which has a repeating section with the following columns:

'Employee Name' [text field], 'Normal Time Hours'[number field], 'Premium Hours'[number field], 'Day Work Hours'[number field]

 

Multiple employee names will be added within the form and some names will be duplicated.

 

I want to create a summary section at the bottom (non editable, viewing only) which states the following:

 

1. Total number of hours entered (sum of all 'Normal Time Hours', 'Premium Hours', 'Day Work Hours')

2. Total number of hours entered for each employee entered (sum of all 'Normal Time Hours', 'Premium Hours', 'Day Work Hours' per employee)

 

Requirement 2 will ideally look like the following example:

 

John Doe - 10

Mark Smith - 14

 

Any ideas on how to achieve this?

 

Many thanks

0 Kudos
Reply
5 Replies
MegaJerk
Collaborator

Re: Master Summary Group

Jump to solution

Are you working with a Classic Form or a Responsive Form? 

0 Kudos
Reply
country101
Scholar

Re: Master Summary Group

Jump to solution

Responsive form (newest version)

0 Kudos
Reply
MegaJerk
Collaborator

Re: Master Summary Group

Jump to solution

With a little work, we can accomplish what it is you are trying to do.

 

First let's start with a test form that I've made: 

MegaJerk_0-1635282115654.png

 

I've added colorful borders around the controls so you can better see what it is I've laid out. 

 

At the top is a Repeating Section (red), inside of which are four Single Line Text Controls (green).

 

Below the Repeating Section is a Calculated Control (gold), and below that is a Multiline Textbox Control (blue).

 

Control Settings

Below are the settings of each control on the form.

 

Employee:

MegaJerk_1-1635282295957.png

 

Normal Time Hours: 

MegaJerk_2-1635282319962.png

 

Premium Hours:

MegaJerk_3-1635282365163.png

 

Day Work Hours:

MegaJerk_4-1635282383198.png

 

Calculated Control:

MegaJerk_7-1635282701484.png

 

 

Multiline Textbox:

MegaJerk_6-1635282567432.png

 

Rules

 

There is ONE rule running, and it's running on the Calculated Control. Because we need to hide the Control, but we don't want the control to be disabled, we must use a rule. It is as follows:

MegaJerk_8-1635282788010.png

 

Calculated Control JavaScript

 

Once you have setup these controls, the rule, and you've made absolutely sure that you've named all of the controls as I have, then you can open up your Calculated Control's settings and enter the Formula Builder:

MegaJerk_9-1635282962849.png

 

Inside of the Formula Builder, you can copy and paste the following code:

(function(employeeNames, nHours, pHours, dHours, summaryTableName){
	"use strict";
	var employees = {};
	var totalHours = 0;
	var summaryTable = NWF$("[data-controlname='" + summaryTableName + "'] div");
	var convertHours = function(hours){

		var hoursInt = parseInt(hours, 10);

		if (Number.isNaN(hoursInt)){
			hoursInt = 0;
		}
		return hoursInt;
	};

	var createTable = function(employees) {
		var table = NWF$("<table class='st'><thead><tr><th class='st-header'>Name</th><th class='st-header'>Hours</th></tr></thead><tbody></tbody></table>");

		Object.keys(employees).forEach(function(employee){
			table.find("tbody").append("<tr><td class='st-row'>" + employee + "</td><td class='st-row'>" + employees[employee].hours + "</td></tr>");
		});

		return table;
	};

	if (summaryTable.find("style").length < 1) {
		summaryTable.html("<style type='text/css'>.st{border-collapse:collapse;border-color:black;border-spacing:0;border-style:solid;border-width:1px;}.st td{border-style:solid;border-width:0px;font-family:Arial, sans-serif;font-size:14px;overflow:hidden;padding:10px 5px;word-break:normal;}.st th{border-style:solid;border-width:0px;font-family:Arial, sans-serif;font-size:14px;font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}.st .st-header{font-weight:bold;text-align:left;vertical-align:top}.st .st-row{text-align:left;vertical-align:top}</style><table></table><span style='display: block;text-align: right;font-weight: bold;padding-top: 10px;'>Total Hours: 0</span>");
	}

	employeeNames.forEach(function(employee, index){
		var normalHours;
		var premiumHours;
		var dayWorkHours;
		var employeeHours;

		if (employee) {

			normalHours = convertHours(nHours[index]);
			premiumHours = convertHours(pHours[index]);
			dayWorkHours = convertHours(dHours[index]);
			employeeHours = normalHours + premiumHours + dayWorkHours;

			if (employees[employee]) {
				employees[employee].hours += employeeHours;
			} else {
				employees[employee] = {};
				employees[employee].hours = employeeHours;
			}

			totalHours += employeeHours;
		}
	});

	summaryTable.find("table").replaceWith(createTable(employees));
	summaryTable.find("span").text("Total Hours: " + totalHours);

}(control_EmployeeName, control_NHours, control_PHours,control_DHours, "control_SummaryTable"));

 

Once you've done that, DO NOT HIT OK JUST YET! Scroll to the BOTTOM of the code, and replace the text for - control_EmployeeName, control_NHours, control_PHours, and control_DHours with their Named Control references:

MegaJerk_10-1635283326188.png

 

MegaJerk_11-1635283333063.png

MegaJerk_12-1635283338654.png

 

Do this for ALL four Controls, and it should result in looking like:

MegaJerk_13-1635283423472.png

 

Now that all of your controls are correctly referenced, you can click on the OK button, and then Save the settings.

 

Testing

 

This part is straight forward. Load up a preview of the form and see the results

 

When starting:

MegaJerk_14-1635283678511.png

 

With 1 row:

MegaJerk_15-1635283689289.png

 

With 2 rows:

MegaJerk_16-1635283700130.png

 

Commented Code

 

Below is the same code used in the Calculated Control but with comments so that anyone looking at this might have a better understanding of what is happening. If you have any questions regarding it, feel free to ask:

(function(employeeNames, nHours, pHours, dHours, summaryTableName){
	"use strict";

	/* object to hold a list of current data */
	var employees = {};

	/* a sum of all valid hours entered */
	var totalHours = 0;

	/* a jquery object reference to the Multiline Text Control where our table will live */
	var summaryTable = NWF$("[data-controlname='" + summaryTableName + "'] div");

	/* 
		this function takes a value, in this case an "hour" and converts it into
		a valid integer. 
	*/
	var convertHours = function(hours){
		/* parse the hours value into an Int */
		var hoursInt = parseInt(hours, 10);

		/* if it's Not a Number (NaN), then convert it to 0 */
		if (Number.isNaN(hoursInt)){
			hoursInt = 0;
		}
		return hoursInt;
	};

	/*
		this function creates a table with all of the current data in the employees object
		and returns the jquery object of it.
	*/
	var createTable = function(employees) {

		/* create our boiler-plate empty table */
		var table = NWF$("<table class='st'><thead><tr><th class='st-header'>Name</th><th class='st-header'>Hours</th></tr></thead><tbody></tbody></table>");

		/* get the keys of our employees object and iterate through them */
		Object.keys(employees).forEach(function(employee){
			/* for every key (employee), create a new row with the name and the total hours, and add it to the table body */
			table.find("tbody").append("<tr><td class='st-row'>" + employee + "</td><td class='st-row'>" + employees[employee].hours + "</td></tr>");
		});

		return table;
	};

	/*
			if this has never been ran before, then there will be no style element
			inside of our control, so we'll need to create:

			a style element containing our css styles
			a table element that acts as a placeholder
			and lastly a span element that contains our Total Hours value
	*/
	if (summaryTable.find("style").length < 1) {
		summaryTable.html("<style type='text/css'>.st{border-collapse:collapse;border-color:black;border-spacing:0;border-style:solid;border-width:1px;}.st td{border-style:solid;border-width:0px;font-family:Arial, sans-serif;font-size:14px;overflow:hidden;padding:10px 5px;word-break:normal;}.st th{border-style:solid;border-width:0px;font-family:Arial, sans-serif;font-size:14px;font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}.st .st-header{font-weight:bold;text-align:left;vertical-align:top}.st .st-row{text-align:left;vertical-align:top}</style><table></table><span style='display: block;text-align: right;font-weight: bold;padding-top: 10px;'>Total Hours: 0</span>");
	}

	/* iterate through each employee name in the Repeating Section */
	employeeNames.forEach(function(employee, index){
		var normalHours;
		var premiumHours;
		var dayWorkHours;
		var employeeHours;

		/* if the employee name isn't blank, then we'll count the hours! */
		if (employee) {

			/* using our handy dandy function, convert the various hour values into a valid number */
			normalHours = convertHours(nHours[index]);
			premiumHours = convertHours(pHours[index]);
			dayWorkHours = convertHours(dHours[index]);

			/* add those hours all together */
			employeeHours = normalHours + premiumHours + dayWorkHours;

			/* 
					if our object already contains an employee of the same name,
					then we just add these employeeHours onto the existing "hours"
					property of the employee in question 
			*/
			if (employees[employee]) {
				employees[employee].hours += employeeHours;
			} else {

				/* otherwise, we create a new employee object */
				employees[employee] = {};

				/* and give it a new hours property with the value of total hours */
				employees[employee].hours = employeeHours;
			}

			/* 
					we then take this employee's hours and add it to the totalHours that will be
					displayed under the table 
			*/
			totalHours += employeeHours;
		}
	});


	/* update the html of the table element by replacing it with the output of our function */
	summaryTable.find("table").replaceWith(createTable(employees));

	/* update the text of the span element by replacing it with the below */	
	summaryTable.find("span").text("Total Hours: " + totalHours);

	/* REMEMBER TO REPLACE THESE CONTROL NAMES WITH ACTUAL CONTROL REFERENCES!!! */
}(control_EmployeeName, control_NHours, control_PHours,control_DHours, "control_SummaryTable"))

 

 

Conclusion

 

That should just about do it. Though this certainly a very non-standard approach to solving this sort of problem, it's about the only way I can think of for the more locked down Responsive Form side of things. Because Responsive Forms was designed in such a way that they limit your ability to load custom JavaScript, please understand that this could break at any moment during a future update! This is incredibly unsupported, and I want anyone reading this to understand that Nintex will not help you fix this if it does become broken in the future.

 

That being said, it shouldn't be too difficult for you to go in there and figure out where the problem is based on the error messages in the event that it *does* break at some point down the road.

 

Hope this gets you on your way.

 

View solution in original post

0 Kudos
Reply
country101
Scholar

Re: Master Summary Group

Jump to solution

Thank you for taking the time and effort to help me with my problem.

 

The solution works perfect!

 

Many thanks

0 Kudos
Reply
country101
Scholar

Re: Master Summary Group

Jump to solution
Please note I have another open community question which seems to require JAVA Script in order to solve.

https://community.nintex.com/t5/Nintex-for-SharePoint-Forum/Pre-populated-names/td-p/211147
0 Kudos
Reply