A client wanted to store the data from an InfoPath form with a repeating table as a step in a Nintex Workflow. Users were submitting expenses in SharePoint and could have multiple expenses in the form. The data was pulled out of the InfoPath form by querying the XML, and was stored in the workflow variables for use further in the workflow. The relevant portions of the workflow look like this:
GET LINE ITEMS
Add a Query XML action to your Nintex workflow called “Get line items”. Set up a variable call LineItems which is of Collection type. Configure the action as follows:
If you don’t know the XPath you can look at your form in InfoPath, right click on the field you are interested in, and select Copy XPath. This will copy it to your clipboard, and you can paste it into your action.
FOR EACH LINE ITEM
Add a For Each action to your workflow called “For each line item”. This will loop through each of the items queried in Get Line Items, above, and store the current line in a text variable. Add a variable to your workflow called LineItem which is a Single line of text. Configure the action as follows:
EXTRACT DATA FROM LINE ITEMS
Add the relevant logic to your workflow and incorporate the Query XML actions to pull the individual fields you want from your InfoPath form. From the screenshot of the flow, above, you would configure Extract Submit Date as follows:
The relevant data points you have queried in your Query XML actions are stored in the workflow variables, and are ready to be used to build and execute a SQL Insert statement during each loop. (***UDATE 2014-08-11: A reader has pointed out that the XML source in this screenshot should actually be the string variable “Line Item”. I will check this at a later date, but am updating the post to point readers in the right direction.***)
BUILD SQL INSERT STATEMENT
Drag a Build String action into your workflow, within the For Each loop. Add a variable called InsertStatementForSQL of type Single line of text . Build your insert statement in the Text portion of the action and store it in the InsertStatementForSQL variable. Use the Insert Reference button to insert the workflow variables. Note that if you are inserting a string value from a variable into the SQL table you need to surround it with single quotes so SQL will recognize it as a string.
EXECUTE SQL STATEMENT
Drag an Execute SQL action onto your workflow, within the For Each loop. In this case I’ve used a Workflow Constant for the connection. You can configure your connection string as necessary. In the query, click on the Insert Reference button and select the InsertStatementForSQL variable you populated in the previous step.
Voila, your workflow will loop through each repeating line of your form and build and execute a SQL statement inserting that data into a table.
In this case the client also want the data displayed in an email. My next post talks about that – Creating an HTML Table in a Nintex Workflow
