Solved

Master Summary Group

  • 25 October 2021
  • 5 replies
  • 12 views
C

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

icon

Best answer by MegaJerk 26 October 2021, 23:33

View original

5 replies

MegaJerk
Userlevel 5
Badge +14

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

C

Responsive form (newest version)

MegaJerk
Userlevel 5
Badge +14

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: 



 


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:



 


Normal Time Hours: 



 


Premium Hours:



 


Day Work Hours:



 


Calculated Control:



 


 


Multiline Textbox:



 


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:



 


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:



 


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:



 




 


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



 


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:



 


With 1 row:



 


With 2 rows:



 


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.


 

C

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


 


The solution works perfect!


 


Many thanks

C
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

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings