Adding Spreadsheets to Nintex Forms

*** Edit ***

When you assume...

Daniel Kinal, was good enough to get in touch with me and let me know the error of my ways. You can now use calculated fields within repeating sections to achieve the same result. There's a tutorial here. If you've come here looking for the solution, I can at least save you some time!

*** Original Post ***

Nintex Forms is a fantastic way to automate a number of business processes but one of the limitations I recently encountered was needing to provide spreadsheet capabilities in a form which was replacing a current Excel document used to collect this information. Just a note, if you don’t require the client side calculations but are looking to work with the data in a repeating section, please check out Vadim Tabakman’s excellent post on parsing line item data using a Nintex workflow .

First, let’s look at the requirements. Our form is designed, among other things, to capture the phases of a project and their budgets. The phase budget has three elements: billed fees, disbursements and consultant charges. In Excel the section would appear as follows:

Modelling the calculations we have four sum totals and a line item total to calculate. Totals for columns C, D and E can be can be calculated at any time since they’re not dependent on any previous calculations but we need to make sure that we correctly calculate the line item total before calculating the grand total.

Go ahead and create a new list and add the following fields. These can all be text fields.

  • PhaseName
  • PhaseNumber
  • PhaseFee
  • FeeSumTotal
  • PhaseDisbursement
  • DisbursementSumTotal
  • PhaseConsultant
  • ConsultantSumTotal
  • PhaseTotal
  • ProjectSumTotal

Ok, with the list items created. Let’s go into Nintex Forms. Wipe all current fields to give a blank slate. Increase the width of the form to at least 1000px. Drag in the repeating section.Create a new panel and size it so that it can fit within the repeating section. Change the CSS class to lineitem (careful of case)

Now, in order, add the following fields into your new panel. When dragging them in make sure that you see the panel background highlight in grey. If not, Nintex does not consider them within panel. Fields are (in order): PhaseName, PhaseNumber, PhaseFee, PhaseDisbursement, PhaseConsultant, PhaseTotal. Shorten the field lengths to fit and add some labels above so that your users will like you.

Now add the appropriate totals columns below the columns: FeeSumTotal, DisbursementSumTotal, ConsultantSumTotal, ProjectSumTotal.

We already added a CSS class for the lineitem panel. Now let’s add the remaining classes. As you probably know Nintex creates its own naming convention for all elements in a form. These internal variables are dynamically generated and are really helpful:

2_ctl00$ctl33$g_99d32365_1a2a_4880_9da0_1767cb2f1ee2$ctl00$ListForm2$formFiller$FormView$ctl25$ctl27$2a654a87_ed16_43c5_bb43_a795bf1c01eb$ctl00$ctl04$ctl11$ctl02 (lots of great stuff in there)

Nintex does have the option to store an ID for a variable at form load (right click on input field and choose Settings > Advanced > Store Client ID in Javascript Variable) so why wouldn’t we make use of that? Well, at the time of writing forms has a difficult mechanism for referencing these variables within a repeating section. In my testing, number inputs only had the first variable referenced (regardless of how many fields had this setting activated). With text inputs it’s different but still problematic. Each input will be assigned two values when the form loads. Watch the form source as you add new rows and you’ll see that one variable value is referenced exactly once (for the first row) while the remaining rows use a prefixed value of the second variable in the format: (2_[value],3_[value],4_[value]). It’s possible to mine this for the correct associations but I found it easier to just fall back on classes that I know will exist in the manner I’ve named them.

So, anyway, add the following CSS classes to the appropriate fields: phasefee, feesumtotal, phasedisbursement, disbursementsumtotal, phaseconsultant, consultantsumtotal, phasetotal, projectsumtotal.

Next, edit the settings of the two fields PhaseNumber and PhaseName and in Settings > Appearance add the Control CSS Class nocalc . We’ll discuss the use of these classes in a bit.

Last, but not least, let’s make sure that our users can’t muck up the calculations we're created for them. There are five fields (all the sum totals and the phase total) that need to be disabled. We can do this by creating a rule that will always evaluate to true. Target the PhaseTotal field and create a new rule using the condition equals(1,1). Make sure to check the Disable flag.

Ok, now to the code. You can add this in one of two ways. Either upload the file into a folder (SiteAssets for example) and reference it in Settings > Advanced > Custom JavaScript Includes or if you prefer, simply paste the entire file contents into Settings > Custom JavaScript. Personally I prefer the include approach since modifications involve editing the JS file itself rather than needing to republish the form. The code is documented and not minified for legibility.

A few points:

  • All jQuery references within Nintex Forms need a NWF$ prefix instead of the normal $
  • All reads from inputs must be tested to avoid non number (NaN) values which will mess up our math. We do this by running parseInt() on the input and then testing the returned value with isNaN(). If the value is non numeric we simply replace with a zero, otherwise read the value.
  • Nintex repeating sections have hidden inputs. We filter these out of our calculations by the .not() statement on the CSS class nf-hidden-associated-control
  • When calculating the sum of columns we don’t care about the selector target. We want all matching inputs within the appropriate class.When calculating a line item, however, we want to specifically limit our change to the row in which the modification occurred. We do this by isolating the selector to the just changed input (This), moving up (via parents()) the first occurrence of the CSS class lineitem before moving down (via children()) to find the phasetotal class and the input within. The nocalc class is to avoid the possibility of numeric values entered into either the PhaseNumber or PhaseName fields throwing off the line item calculation.
  • To handle for a user deleting a row we bind a function to mouse clicks on the nf-repeater-delete-row CSS class (the delete row X image). When this event fires we wait a half second to allow the repeating section to finish redrawing before we recalculate all the column sum totals.

You’re done. Take your form for a spin.

It’s Time To Transform

Let us show you how much easier your work life can be with Bonzai Intranet on your team.