For a variety of reasons, people are looking to get their data out of InfoPath forms and into something better suited to modern technology. Whether moving out of InfoPath because of uncertainty about the product’s future, the need for something that allows for responsive design, or another of the many reasons, one of the biggest challenges is how to get your data out of existing InfoPath forms.
To date, there is no product that can automatically convert InfoPath forms into another system. With Nintex workflow, however, you can take data out of a form – even complex forms with repeating tables – without writing any code. Below, we’ll walk through the steps to move data from InfoPath and put it into SharePoint lists.
The InfoPath Structure
In this example, we are using a Project Risk Register form. There is a section for general project information and repeating sections that allow for adding multiple risks.
If we go into the XML structure, it looks like the following:
In order to get data out of this form, we’ll need to create two lists: a list for the Projects and a Risk Register where items can be associated to a project.
Create your SharePoint Lists
First, we will create the Parent List, called Projects. We will create columns for the data that was in the form, including Project Name, Status, Customer, Project Manager, Start Date and End Date. (Note: You may want to create this as site content type, and then add the content type to the custom list.) We will use the appropriate data type for each column.
The new entry form for projects looks as follows (when using Nintex Forms):
Next, we’ll create the Risk Register list with the data fields shown in the form.
We will also create a Lookup column called Project, which will look up the title from the Project list. This will allow the links to be linked together.
Since the Risk Score is calculated through a rule in InfoPath, we will make it a calculated column in SharePoint.
Create the Workflow – Extract to Parent List
Now that we have our lists built, it is time to take the data from the form and add it to the lists. First, we will cover extracting data for the Project list, since it is the parent and is the easier of the two.
- Create a new Nintex Workflow.
- Create your variables. In this case, we are creating the variables that correspond to the data fields both in InfoPath and the list. We will also create a List Item ID variable type.
- For your first step, use the Query XML function in the Integration actions.
- Leave the XML Source as Current Item and leave Process using XPath select.
- Select your field from the XPath builder.
- Return the results as text and store in the appropriate variable.
- Repeat as needed for all the fields.
- Once you have your data saved to variables, select Create Item from the Libraries and Lists actions.
- Select all the fields available for the item and set them to equal the corresponding workflow variable. Set Store new Item ID in the List Item ID variable that you created in step 1.
That is all there is to it for creating the parent list item. Now, for the fun part.
Create the Workflow – Extract to Child Lists
For the repeating sections, we will need to use collection variables, For Each loops, and Collection Operations.
- Our first series of variables will be Collection variables for each field in the repeat section. We will NOT create a collection variable for any field that becomes a calculated field.
- Next, we will create variables for each data field in the repeating section and use the data type appropriate to the field. Note that you do not need a variable for the calculated field.
- We will also create a Number variable called Index.
- We will use the Query XML step again and leave the XML Source and Process using drop downs set to the defaults, as we did with the parent list.
- This time, when we go select our fields, we will go into the repeating section and select the fields. The results will be returned as Text, and we will store the result in the corresponding collection variable.
- Repeat this for all the fields we are storing in collection variables.
- Once we have all the collection variables created, we will use a For Each Loop step, found in the Logic and Flow section.
- Configure the For Each action to use the collection for title, store the result in the text variable for title, and set the Index.
- At this point, the workflow should look like the following:
- We will now use a Run Parallel Actions set and various collection variables. In the Run Parallel actions, create a branch for each collection variable, except the one for the Title, which we used at the start of the For Each Loop.
- In each branch, use the Collection operation step to extract the correct information from the collection.
- Configure each Collection Operation as shown below. Always use the Index variable for the Index value, and then match the collection variable to its corresponding single value variable.
- The final step is to Create a List Item in the Risk Register, just as we did for the Project list. We will use the variables extracted in the Collection Operations for our field values.
- Finally, for the Project Lookup column, we will use the List Item ID variable we stored the ID for the Project item created.
When you run the workflow, you will see the items from the InfoPath form created into the lists.
With our data now out of InfoPath, the next step is to redesign the project form so we can see the related items. But, that is for another episode of Nifty Nintex Tricks.