cancel
Showing results for 
Search instead for 
Did you mean: 

Populating Repeating Section Controls with List Data

jblair
Nintex Newbie
17 27 17.7K

Populating Repeating Section Controls with List Data

Part 1: Creating the Form and Lists

In a post written by Vadim Tabakman, he showed us how to parse repeating section data in a Nintex Form using Nintex Workflow. Vadim’s clever User Defined Action (UDA) queries the XML in the repeating section, extracts the text and stores it in variables for later use by our workflow. This concept has proven invaluable on several workflows Synergy has built for our customers.

But what happens when we want to do the reverse? In this scenario, say you want to create an onboarding form which automatically populates the controls within a repeating section with a new users’ default resources. This would include Resource Name, Description, and the administrator for a resource. The default resources will be dependent on the user’s role within the business, determined by their department.

Challenges:

We have a few problems to overcome. Controls bound to a column cannot store more than a single value, so repeating data presents a problem. If a repeating section is unbound, the data is effectively encapsulated within the XML, making accessing the data more challenging. In the scenario I’m about to present, the amount of data in each repeater can vary significantly, so creating a simple lookup will create performance issues. Finally, the customer in this case was averse to using JavaScript due to their
limited ability to support it.

Lists

Department

First, I created a Department list to store departments which will be used in the form. This has a Single Line of Text column, which is the Title column which has been renamed. 
  Figure 1: Department List

Resources

Secondly, I created a Resources list. This has a Resource column (renamed Title column), a Description column (Multiple Line of Text), a Resource Administrator (Person or Group), and finally, the Department column. The Department column is a Lookup configured to pull one or more values from the Departments list. In our customer’s particular case, there were over 400 resources!


Figure 2: Resources List

Onboarding Form

I created a simple tabbed form for this post (again, thank you Vadim). In the Employee Information panel/tab (at top), I am using Single Line of Text columns for Employee ID, First/Last Name, and Job Title. Notes is a Multiple Line of Text (MLOT) column, and Department is a Lookup configured to pull values from the conveniently titled Department list. This lookup only allows a single choice. These controls are bound to Onboarding Form columns.

NOTE:   The Department value could also be a choice control/column. I chose to create a lookup to prevent my customer from having to update the values in the Choice
Column within the list, instead allowing values to be added or removed from the Department list).

Figure 3: Onboarding Form in Forms Designer

Onboarding List Columns

Figure 4: Onboarding List Columns

The second panel/tab has a Repeating Section control. This control is bound to a Multiple Line of Text (MLOT) column titled Default Resources. It is important to note, when doing this, the MLOT must be set to Plain Text or it will not let you bind the Repeating Section to the MLOT column.

Figure 5: Default Resources Column Setting

Figure 6: Repeating Section Name and Binding

Within this, I have a Resource Name Single Line of Text control, a Resource Administrator Person or Group control, and a MLOT Description control. For Resource Name and Resource Administrator, I have named the controls (ResourceName and ResourceAdministrator respectively).

Figure 7: Resource Name Control Name

However, I chose not to name the Description control. While I would recommend naming your controls, as it makes it easier for the workflow configuration, the steps outlined in this article can be done without naming controls. I’ll show you why naming the controls is helpful in just a bit.

Figure 8: Description Control (unnamed)

In order to build the workflow, you are going to need some information from the repeater. To do this, create an item and populate it with some data. In the Repeating Section Controls, put in some easy to remember values. This will help display the start and end tags and the content within the control, which is especially important with unnamed controls.

Figure 9: Assigned Resources Sample Data

When we save the item, we get back some data. The bound controls on the Employee Information tag get stored in the columns, and the data from the Repeating Section is stored to the Default Resources MLOT column as XML.

Figure 10: Sample Data

Since that is impossible to read, the data that was returned is as follows:

<?xml version="1.0" encoding="utf-8"?><RepeaterData><Version /><Items><Item><ResourceName type="System.String">TestResourceName</ResourceName><ResourceAdmin
type="System.String">i:0#.w|synergy\jason</ResourceAdmin><_x0031_100a024-4518-4b10-8a57-d51408654ed5 type="System.String">Test Description</_x0031_100a024-4518-4b10-8a57-d51408654ed5></Item></Items></RepeaterData>

So for our three controls (Resource Name, Resource Administrator, Description) we have the following XML to concern ourselves with:

Resource Name: <ResourceName type="System.String">ControlDataHere</ResourceName>

Resource Administrator: <ResourceAdmin type="System.String">ControlDataHere</ResourceAdmin>

Description: <_x0031_100a024-4518-4b10-8a57-d51408654ed5 type="System.String">ControlDataHere</_x0031_100a024-4518-4b10-8a57-d51408654ed5>

To quickly recap, I named the Resource Name (ResourceName) and Resource Administrator (ResourceAdmin) controls, and did not name the Description control. Because I named the first two controls, we get easy to read start and end tags. With my unnamed Description control, we get the less simple hex for the control (_x0031…). So it’s still possible to use unnamed controls in your repeater, but it’s going to be a bit messy, thus the recommendation to name your controls.

We now have the XML we are going to use when populating the controls within the Repeating Section. And now for the workflow.

Part 2: Workflow to populate the Repeating Section with list data

In Part 1 of this post, I showed you how to create a few lists and a Nintex Form with a repeating section. That repeating section stores data within a plain text Multiple Line of Text column as XML. I am now going to show you how to build the workflow to query list data based on information in the form and, when returned, automatically create rows within your repeating section for each value that is returned.

Our requirements for this workflow are as follows:

  • New Employee information will be populated in the Employee Information tab of the Nintex Form by a manager who has hired a
    new employee, including the Department he or she will work in.
  • Each department or business unit has default resources assigned to a new employee in that department.
  • The workflow should query the Resources list and return items tagged with the user’s new department, and automatically create a
    row in the repeating section for each returned resource.

Workflow Configuration

Query List

So, we start with a Query List action. The Query List action will query the Resources list when Department is equal to the Department value selected by the user in the form.

Figure 1: Query List with Filter

Once we have our filter set up, we need to return the values needed to populate the controls in the Repeating Section. For this, some Collection variables have to be set up.

Figure 2: Returned Fields and Variables

For Each

Now that we have our data in variables, we need to extract the individual values in each collection. We’ll start with a For Each action, and get the subsequent values using a Collection Operation. To start with, we will loop using the collection for the Resource Name (the choice here is arbitrary). As the data in the list is text, we’ll use a Single Line of Text type variable. Finally, because we need to repeat this process for the Administrator and Description, we’ll create an Index variable using a number type variable.

Figure 3: For Each Configuration

Collection Operation 1

Now we need to move on to the Resource Administrator and Description values. To extract these, we will add a Collection Operation within the For Each action. The first Collection Operation action will ‘Get’ values from the Resource Description collection and store them in a text variable. Because the column in the Resources list is a Multiple Line of Text column, I created a variable of this type, though a Single Line of Text column would probably also work. Use the index you created in the For Each so the collection
operation knows when to start and stop processing values.

Figure 4: Resource Description Collection Operation Configuration

Collection Operation 2

Now that we have the Resource Descriptions, we can process the Resource Administrators, again using a Collection Operation action. As the data type for this is a Person or Group, you’ll need a Person or Group type variable to store the result. Again, use your Index to tell the action when to start and stop processing values.

Figure 5: Resource Admin Collection Operation Configuration

By now, you should have something that looks like this:

Figure 6: Workflow Designer

Build String

We have now extracted all of the values from the collection variables, and have the data stored in individual variables of the appropriate data type. We now need to insert that data into the XML from our Repeating Section. To do this, we need to use a Build String action, again inside of the For Each, as we are building this string for each row of data.

Since we have an understanding of the start and end tags for our controls within the Repeating Section from Part 1 of this post, we will build on that by inserting the variable data we captured in the above steps. Between the tags for the three controls, insert a reference using your workflow variables from our previous operations. Store the results in a Multiple Line of Text variable (I called mine mlotXMLSnippet).

Figure 7: Build String configuration

Pro Tip: In my resources list, you savvy SharePoint types might have seen some of the description values have non-web compliant characters (specifically, an “&” in my sample data). If inserted into XML, this will create a problem which prevents the repeater data from being displayed. To overcome this and effectively ignore the special characters, I have wrapped my variable reference in <![CDATA[{WorkflowReference}]]>. This instructs the XML to ignore the content contained in the string. If your data
does not contain any special characters, you can ignore this step.

Collection Operation 3

Finally, now that we have our individual XML strings for each row of returned data from our query, we need to add them to a new collection. For this, I created a new Collection variable, and am using an Add operation. The data I am adding comes from the result of my Build String action, which was stored in the mlotXMLSnippet variable.

Figure 8: Add Collection Operation

Set Field Value

Finally, we need to take all of this processed and assembled data from our query and insert it into our list item. To do this, I have used a Set Field Value action, this time outside of the For Each action (we only need to do this once).

The column I am updating is the Multiple Line of Text column my Repeating Section control is bound to, called Default Resources. The XML string you see here comes from the test value we created in Part 1 of this post, and contains the XML version, encoding, and the Items open and close tags in our XML. Inside of that, we insert the XML Collection we created in our final Collection Operation action. This reference to the XML Collection variable belongs between the opening <Items> and closing </Items> tags.

Figure 9: Set Field Value Configuration

Your workflow should look something like this:

Figure 10: Workflow Designer View

Testing

Now it’s time to test. If everything is configured properly, after workflow execution the query should populate the repeating sections. If you are showing the Default Resources Multiple Line of Text column, the list item will look a little messy. In the solution we built, we hid the MLOT column from the default view, and created a debugging view should someone wish to look at the XML.

Figure 11: Multiple Line of Text column showing list data inserted in the XML

Here is where Nintex Forms performs some magic. Now that the XML is in place in the column, when the list item is opened (and assuming you don’t have any mistakes in your workflow configuration), you get repeating sections populated with your list data. Sweet!

Figure 12: Populated Repeating Section

Post by Jason Blair, Nintex vTE at Synergy, a Nintex Premier Partner. Check us out for more info at www.synergyonline.com

27 Comments
Nintex Employee
Nintex Employee

This is brilliant Jason.  Thanks for sharing this.

jblair
Nintex Newbie

My pleasure Vadim. Thanks for clearing the path to doing cool things with repeating sections. Just following in your footsteps!

Not applicable

Francis Bullet SandicoRicardo Calingan​ some great material for you guys!

Automation Master
Automation Master

Paul Crawford​ he beat you to it.

brad_orluk
Nintex Newbie

This really is great stuff Jason Blair​! Thank you for sharing it.

jblair
Nintex Newbie

Thanks Brad, much appreciated. Hopefully it will lead to some interesting use cases out there in the Nintex world!

Not applicable

Hey Jason Blair​, I have done the exact same thing here, works excellently.

On one occasion I am trying to go the opposite way:

- Got a form with repeated Data (events)

- Each event can be marked as critical (checkbox)

My workflow extracts all critical events and inserts them in a List for special events. This works pretty well.

Unfortunately I have to support special characters (like ü, ö, ä) which are displayed as &amp;#252; - and I am stuck on preventing this. Have you any idea on how to solve this?

Not applicable

Hi Nils, I have the same problem with Illegal XML Characters for repeating section data

Did you get any solution on it?

Not applicable

The only solution I found so far is to fn-Replace the illegal characters one by one before creating the list item.

jblair
Nintex Newbie

Nils, Boriss: One approach which I found for when pulling illegal characters into XML is wrap them in <![CDATA[StringWIthIllegalCharacters]]>. The <![CDATA[]]> string is an instruction to ignore any characters within that section of the XML. I would give that a try, edit the XML itself and add that wrapper around any data that has those characters and it should ignore the characters. Let me know if that doesn't work for some reason, but that should work for you.

Not applicable

In my case this is not a possible solution.

My users enter information inside a repeated section (events). The workflow then extracts the items of the repeated section and inserts them as list items into a sharepoint list.

Since the users enter the data into the repeated section, i have no way of wrapping that data with xml-comment.

jblair
Nintex Newbie

Nils,

    I don't know if you've seen Vadim Tabakman​'s blog article http://www.vadimtabakman.com/nintex-formsworkflow-parsing-repeating-section-data.aspx  about parsing repeating section data? I have done both taking repeating section data and creating list data and taking list data and populating repeating sections (this blog post). Vadim's post gives you a fantastic approach for doing what you are asking. He's gone so far as to post a UDA you can download as well. I did find it needed a little tweaking (I suspect when he published it he was using an older build of NWF), but I've used that method successfully in several production workflows we've built for customers. Since we are "getting" the data from the repeating section XML in Vadim's instance, you should still be able to wrap the results from the XML query in CDATA to ignore the special characters using an Update XML action post query. It's worth playing around with. Vadim, any tips you'd like to share on ignoring special characters?

Automation Master
Automation Master

you may want to apply fn-XmlDecode() on your data to get readable content.

jeepr92
Nintex Newbie

This is great.

Now I would like to add onto this workflow.

I would like to take the finial collection and input it into a repeating section on a Flexi Task.  That way a user can see all the items from a query and either Approve or Reject.

To add onto this, I would like to get 1 Flexi Task and have each repeating section have a check box to either approve or reject each item.

Not applicable

Hello Group

I had to create an extra field to spit my XML out to for testing, because what I thought I was getting was different than the actual. I had similar issues with bad formatted XML.This has something to do with the translation of the muti-line text field that is then parsed into XML. I fixed this by creating a new Multi-line text field and setting it to PLAIN TEXT! The first person who had created the field set it as Rich text. I tried fixing it by changing it to Plain, however that did not work. Plain Text is the best text type to use for XML repeating sections.

If this does not fix it, then if you can establish a pattern you can use the Regular Expression action to replace characters.

mclaffertyj
Nintex Newbie

Hi Jason, This is exactly what I was looking for. Thank you. We pull data out of a SQL database and populate it in two lists as you described. I have used this inside a work flow using the flexi-task.This is for a mobile app and when views shows the job with the associated irrigation pumps (in the repeating section).

One anonaliy that I can't quite work out is when I do some edits to the layout (mobile and tablet) I must be altering something because when I go to view it next in the repeating section the rows do not display. The XML is still been generated as before. I saw your comment on illegal characters so I have wrapped the data as you outlined.

I must be missing something simple. If I blow the form layout away and start fresh it works but this approach is wearing abit thin as its a long form that I have to recreate each time.

Any ideas on this one?

My XML output is below.


Thanks

James

<?xml version="1.0" encoding="utf-8"?><RepeaterData><Version /><Items>Item><PumpName type="System.String"><![CDATA[Pump 1]]></PumpName><PumpNameplate type="System.String"><![CDATA[  90.0]]></PumpNameplate><PumpLocation type="System.String"><![CDATA[ghjhg]]></PumpLocation><CapacitorNameplate type="System.String"><![CDATA[20.00]]></CapacitorNameplate><Installation_config_code type="System.String"><![CDATA[jygh]]></Installation_config_code><PumpPosition type="System.String"><![CDATA[SUF]]></PumpPosition></Item>

;Item><PumpName type="System.String"><![CDATA[Pump 2]]></PumpName><PumpNameplate type="System.String"><![CDATA[  90.0]]></PumpNameplate><PumpLocation type="System.String"><![CDATA[jhgjhgfj]]></PumpLocation><CapacitorNameplate type="System.String"><![CDATA[22.50]]></CapacitorNameplate><Installation_config_code type="System.String"><![CDATA[gjhgjgh]]></Installation_config_code><PumpPosition type="System.String"><![CDATA[SUF]]></PumpPosition></Item>

;Item><PumpName type="System.String"><![CDATA[Pump 1 185 kW]]></PumpName><PumpNameplate type="System.String"><![CDATA[  185.0]]></PumpNameplate><PumpLocation type="System.String"><![CDATA[jhgjhgj]]></PumpLocation><CapacitorNameplate type="System.String"><![CDATA[46.25]]></CapacitorNameplate><Installation_config_code type="System.String"><![CDATA[jhgjhgj]]></Installation_config_code><PumpPosition type="System.String"><![CDATA[SUF]]></PumpPosition></Item>

;Item><PumpName type="System.String"><![CDATA[90 kW Booster]]></PumpName><PumpNameplate type="System.String"><![CDATA[  90.0]]></PumpNameplate><PumpLocation type="System.String"><![CDATA[hgjhgjhg]]></PumpLocation><CapacitorNameplate type="System.String"><![CDATA[20.00]]></CapacitorNameplate><Installation_config_code type="System.String"><![CDATA[ghjhgj]]></Installation_config_code><PumpPosition type="System.String"><![CDATA[SUF]]></PumpPosition></Item>

;Item><PumpName type="System.String"><![CDATA[Stock Water]]></PumpName><PumpNameplate type="System.String"><![CDATA[  7.5]]></PumpNameplate><PumpLocation type="System.String"><![CDATA[hgjhgfj]]></PumpLocation><CapacitorNameplate type="System.String"><![CDATA[1.87]]></CapacitorNameplate><Installation_config_code type="System.String"><![CDATA[hgjhgj]]></Installation_config_code><PumpPosition type="System.String"><![CDATA]></PumpPosition></Item>

;</Items></RepeaterData>

mclaffertyj
Nintex Newbie

Hi Guys

I have since resolved this. I was not aware of the linked controls and was somehow deleting them when I was chaning the form layout. Now that I know that the desktop is the default all all controls need to be on here I am sorted.

Once again thanks for your sharing Jason

James

Stonehage
Nintex Newbie

Just a thank you for a simple guide to follow.

I'm building a list of requirements needed when someone joins or leaves from a lookup list based on their office. This helped me generate the XML required for the repeating section that they can then add to and amend as required.

Now I will be looking to extract the choices back out to generate individual tasks.

kellylamb
Nintex Newbie

Hi,

I have got my workflow working following the steps above and have the XML in the repeating section column but when I open the form the data isn't pulled through?

I noticed because I am using a collection variable for all the <item> entries it adds a semicolon into the XML. I removed them via the 'regular expression' action and stored it into a multiline variable and piping it back into the repeating section column.

It didn't make a difference, but if I manually copy the XML code from the column and paste it into word (I don't change anything), copy from word and paste it back into the column, when I open the form all the lines of data are showing!

Can anyone shed some light on why and how I can get this working?

I have tried querying the XML and making sure it is stored in text format but that doesn't seem to make a difference either.

Any help would be appreciated.

Thanks

Automation Master
Automation Master

Hi folks - 

I'm having this issue as well. Apparently some users are using "vertical tabs" (I think CTRL + ENTER is the equivalent) and apparently this is an illegal event in XML 1.0 - and it comes back with the 0x0B error. 


I had thought I could maybe encode, regex a replace, and then decode - but alas - since it's an illegal character, it doesn't print at all. So it really does just show up as " ". 

I think the only way around this will be to somehow tell FormData to be version 1.1 XML which I believe handles these "illegal characters" ... but I can't find a way to do that. 


‌  (in case you all have thoughts on the matter!)

Automation Master
Automation Master

could you post some sample of your data?

Automation Master
Automation Master

I will! And thank you for offering to look. Will get back to this as soon as I can, didnt want you to think I was ignoring you.

Automation Master
Automation Master

Hey there - 

So I found a way to do this - but it would require me to figure out every possible illegal XML character and add a RegEx for each one to blank it out. I was hoping I'd found an easier way ... and maybe, in my situation where I have only seen the one issue, it would be ... but I fear further illegal XML characters. So!

With Jason Blair‌'s idea, it only requires you to RegEx each control within your repeating section where this XML junk might pop-up. For me, I was getting an 0x0B error in a Notes section, and another type of different Notes section.

For those who want the visual, here ye go:


This is what solved it:


I was hoping (foolishly) I could wrap the ENTIRE repeating section in CDATA but that was a lost cause -- this, however, works. So I will be sticking this onto all of my note fields, I suppose, to avoid the dreaded error. 

I am trying to think of an automatic way to plunk this into workflows, maybe even just make it a part of the UDA ... but I think it would require RegEx to find "System.String>, figure out what the text is prior to that, hold that in a variable, then find the closing XML for that ... and... yeah. 

Well, I'll think on it, anyway.

Automation Master
Automation Master

... and add a RegEx for each one to blank it out

I believe this could be done with single regex action ...

anyway, I still wonder what might be those 'very special' characters that could not be handled regular way.

I've tried following:

this is my input from form

(note the very last character is that 'vertical tab' = AltGr+11)

this is how repeater data gets saved

this is how do I query those special characters from repeater XML

this is how do I write them to another list field

and finally this how do they look like in the list

I don't see there any problem or a reason for any special handling.

Automation Master
Automation Master

Mine chokes in the XML query - it won't let me query the string from XML and put it into a string variable, it tells me it's it's invalid.

As soon as it tries to query, I get: 

"Workflow Details

Error processing XML. ' ', hexadecimal value 0x0B, is an invalid character. Line 1, position 567.


So what I did before was print the repeating section into an email and that's where I saw the "&#xB;" which is what ends up in the data for the VT - even though it doesn't show me that in the error. 


The only way I've been able to manage was by manipulating the data as soon as the UDA is run, prior to querying. I can regex that out, or I can put cdata in.

I'm boggled that yours isn't erroring...

Automation Master
Automation Master

hm, but this is HTML encoded character, and not XML encoded.

how did you manage to get it in that form? some external input? form controls should correctly XML encode it.

Automation Master
Automation Master

this is just what I get out of the repeating section UDA