Exporting InfoPath repeating table data with Nintex Workflow

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:

imageimage

image
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:

image

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.

image

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:

image

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:

image

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.

image

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.

image

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

17 thoughts on “Exporting InfoPath repeating table data with Nintex Workflow”

  1. Hi,

    I followed the above steps, in my scenario i am inserting the repeating table values into share point list.
    It is saving only one record multiple times.

    help me in this.

    Thanks,
    Naveen

    1. I remember running into this when I was working on it. Check the Xpath carefully, and the loop, to be sure you are iterating through each of the items. You may be iterating through each item, but outputting only the first item each time.
      Cheers,
      Martina

      1. can you tell me what exactly to look for within Nintex? I see that it is counting the rows in the repeating table . Which means the “for each” is working. But it is repeating the same line of information. I see there is an option for multiple outputs, but on some forms, there may be 10 lines of data on others just 2, so creating multiple outputs does not make sense. I feel like I am close. Also how are you able to get this data into a list ? I am assuming to use CREATE ITEM” and point to the list, but when I enable that it, it errors out.
        Any direction would be helpful.

        1. Please look at the screenshots in the post carefully and follow them exactly. I remember running into this when I was working on it. Check the Xpath carefully, and the loop, to be sure you are iterating through each of the items. You may be iterating through each item, but outputting only the first item each time.
          Cheers,
          Martina

          1. You have to excuse me.. My mother had me later in life and I tend to take a while to learn things… BUT THANK YOU.. I was missing the point that it stores the results in the variable Lineitem(in your result). What was throwing me was I enabled Verbose Logging and I saw X number if iterations but the data in each was the same. Hence my above post. I thought that through each iteration, it would show the change in the variables, which is does not do. It saves it in the variable, as you stated. Thank so much.

    1. The SQL Insert step is building out a SQL Statement and storing it in a string variable. Standard SQL applies. So you would write a string that follows this format:

      INSERT INTO (field1, field2, field2) VALUES (‘{workflowreference1}’,'{workflowreference2}’,'{workflowreference3}’)

      The references which you are inserting from your workflow need single quotes around them in order for SQL to recognize the value as a string to be inserted. This string gets stored in a variable in the workflow, and then is executed in a subsequent task in the workflow.

      Hope that helps.
      Cheers, Martina

  2. I’m also having the issue of saving only one record multiple times.
    When you say “You may be iterating through each item, but outputting only the first item each time.” can you tell me where I should be looking to not output the first item multiple times?

    I’m wondering if i have the ‘Query XML’ within the loop setup correctly.
    XML source: Current Item
    Output 1 Process using: XPath
    XPath is pointing to the field within my InfoPath form repeating table
    Store results in: a ‘Single line of text’ variable

    1. Hi Norman, you need to store the results in a ‘Collection’ type variable, not ‘Single line of text’. Your single line of text is just grabbing the first item. Cheers, Martina

  3. Hi, I am also having the same problem with it repeating only the first item. I have the first Query XML Pointed to my repeating table and storing the results in a Collection Variable “LineItems”

    As for the Query XML to extract data that is within the For Each Loop, I have that storing the values in a single line text Variable “LineItems”

    In my case, I am creating a new List Item in a SharePoint List. When I run the workflow, it runs but it creates the first record multiple times (same number of times as the number of items in my repeating table).

    1. Hi Tim,
      From your description your Collection variable and your single line text variable have the same name “LineItems” which is not possible. I think you are mixing them up, which is very easy to do. It seems your are looping through each item, but extracting only the first item each time. Be sure to look at the screen shots very carefully, especially For Each Line Item, and Extract Data from Line Items. This does work as documented. I remember having this exact issue and it was some small thing that needed to be tweaked.

  4. In your SQL insert section, where are you “inserting” this data into? (i.e. SP Expense Form LineItem). Is that the name of your list? or is it a variable?

  5. Hi Martina,

    Spent the whole day trying to make my solution work based on your screen shorts. finally realised there is a flaw there, thats why it always returns only the first item multiple times. in your last screen short, to extract the date… The XML source has to be the variable where you stored your result, so in this case LineItem. If you query CurrentItem everytime the solution wont work. just thought it might help others. But your post has been extremely helpful to get me started on querying repeating tables.

    Thanks,

  6. PLEASE HELP!
    I was making this…

    XML source: Current Item
    Output 1 Process using: XPath
    XPath is pointing to the field within my InfoPath form repeating table
    Store results in: a ‘Single line of text’ variable

    in the extract step, So I made what you said “you need to store the results in a ‘Collection’ type variable, not ‘Single line of text’.”.
    And now my problem is that when I´m trying to create a new item in a shrepoint list the fields that I´m storing in the variables that are collection type, in the sharepoint list display me all the collection of each column in every field.
    How can I specified to display only one item in each filed but at the same time that not to be the same item in each row but go increasing.

    THANKS.

Leave a Reply

Your email address will not be published. Required fields are marked *