Repeating table’s column promotion/concatenation & extracting individual data

Repeating tables in Info Path is no doubt a wonderful control that gives the users the suppleness to enter information in a table. But when it comes to use the individual rows/columns of this very table in SharePoint, all doubts and fears surface.

Same was with me so I decided to write about it.

Problem statement – How to promote a column of a repeating table and how to extract the individual row of data.

Repeating table gives four options for field promotion

  1. first – promoting the very first row’s column
  2. last – promoting the last row’s column
  3. count – giving the count of the rows in the table
  4. merge – merging a column’s data and promoting (we’ll be dealing with this one, rest all being unpretentious and straightforward)

Say my table here consists of details such as the product name, quantity and cost.

RepeatingTable1

The Info Path fields look like below

RepeatingTable2

It is always advisable to rename the Group1, Group2 to your project specific relevant names. Here, it is ProductsGroup and Product respectively to identify the fields with ease.

Now I fill up the form’s repeating table with some data

RepeatingTable6

In order to be able to use the fields in SharePoint, it is always sagacious to use a separator for individual data. Our goal is to write a rule to achieve the same. To get the data in this format –  “Laptops;Mouse;”, add a text field “ProductNameMerged”

RepeatingTable3

Goto the properties of the text field and click on fx to write the formula

RepeatingTable4

Write the formula as below:

eval(eval(Product, ‘concat(my:ProductName, “;”)’), “..”)

or if you check the “Edit XPath” check box, the formula should look like

xdMath:Eval(xdMath:Eval(../my:ProductsGroup/my:Product, ‘concat(my:ProductName, “;”)’), “..”)

RepeatingTable5

This will result in the text box having values separated by a semi colon as and when data/rows are being entered in the repeating table. For the scenario above, the result shall be “Laptops;Mouse;”.

Detailed explanation of the XPath formula

  • concat(my:ProductName, “;”) – Adds a semicolon to each product name
  • eval(Product, ‘concat(my:ProductName, “;”)’) – Loops through each Product to create a list of product names
  • eval(eval(Product, ‘concat(my:ProductName, “;”)’), “..”) – Gets the parent of the product names, and converts them to a string.

Here you go…. Now that you have a text field with repeating table’s column separated by a semi colon, you can use regular expressions or the inbuilt string functions to extract the data.

Advertisements

28 thoughts on “Repeating table’s column promotion/concatenation & extracting individual data

  1. Hi

    i have a error Carácter inesperado.
    eval(eval(../my:grupo1/my:grupo2, –>‘<–concat(../my:grupo1/my:grupo2/my:nombre, “;”)’), “..”)

    • Hi Freddy,

      Instead of writing the formula in the wizard, I suggest you copy the formula from my post to a text file and edit it with your field names. Then put the same thing in your formula wizard. That should fix the problem.

      • Freddy, once you put the formula in the wizard, ensure that you remove and re-enter all characters like double quotes, single quotes. I faced the same and this worked.

  2. Hi Manisha, I’m having the same problem as Freddy, and that is with copy and paste of the code. I’m using InfoPath 2013, thank you Dottie

    • Hey Dottie,

      I assume you are using an English keyboard.

      What you can do is remove the apostrophe from the formula and put it back again from your keyboard, instead of pasting it from my code.

  3. Hi Manisha, Great article. I tried your approach but instead of getting the result string as “Laptop;Mouse;Keyboard”, I’m getting “Laptop;Laptop;Laptop”. Only the first entry is getting repeated as many times the number of rows in the repeating table. Any ideas how to fix this?

  4. Pingback: Repeating table’s column promotion/concatenation when data is from secondary source | My Learnings

  5. Hi Manisha,

    do you know if we can use the same thing with Nintex to move a repeating table from InfoPath form to another??

    • Hi Ahmad,

      Well, we can’t use the same formula to move a repeating table from one InfoPath form to another. However, you can promote the fields to a library, run a workflow to split the data and create separate items in another list. Then from your new InfoPath form, create a connection to this list and bind your repeating table.

      I know this doesn’t seems to be the very right approach but it works!!

      • thanks Manisha,
        but do you have an example, i am afraid that I couldn’t get the idea correctly…
        again thanks for your quick reply,
        have a nice day….

      • Ahmad,

        I’ll try to put the idea once again.
        You have the data in the format a;b;c; that is promoted to the library, right?

        Run a workflow to split this data and create items in another list; which means your list will have three items called a, b and c.

        Now in your second InfoPath form, add a new data connection to this list and pull the data.

        Let me know if this helps, else I shall try to create an example for you.

  6. hey Manisha,
    if you have a sample please send it to me, it did not work for me, I tried to split the data and enter it as records but it did not work, by the way,
    I am using Nintex workflow.

  7. Hi Manisha,

    I have tried your formula and I am getting only the first entry
    Laptop; and the rest of the entry is not included.

    Can you tell me how I can fix this? Thanks

    • Hi Lucy,

      Can you check the formula of eval that you’re applying on the “ProductNameMerged” column. I believe that could be the only cause what I can foresee.

      • Yes, for some reason my ProductNameMerged XPath had an extra prefix when I hit ‘ok’, just deleted the prefix and kept it to my:ProductNameMerged and it worked!

        Thanks Manisha!

  8. Manisha, Please help, I just cant get this double eval to work.
    I have a repeating looks like this:

    – gRCAActionTrans
    +group21
    ++group22
    fields5
    field6
    field7

    I then have a field called ActionsConCat on which I hoped to:
    eval(eval(group21, ‘concat(field5, “;”)’), “..”)

    If I enter text into the first line within the repeating table, it works but anything entered after this doesn’t show up

    Any help be much appreciated – I’ve been on this problem for days.. must have been on every website there is for double eval.

    Thanks
    Mike

    • Hi Mike,

      When you say “f I enter text into the first line within the repeating table, it works but anything entered after this doesn’t show up”, do you mean to say that if you enter any symbols such as – or + before the text, it doesn’t works?

      Manisha

  9. Hi Manisha,
    I am having the same problem as Lucy. The formula keeps returning the first row only and seems to ignore the loop. I have checked the over the eval several times. Is it because I am trying to create the merge on a Repeating Section rather than 1 field?

    Thanks for your help!

    • Apparently yes Kora.

      We apply this merge on a repeating field, so that one can get all the values entered for that field. Please try to use the formula for a repeating field instead of a repeating section.
      You can generate various merged fields for all your repeating fields inside the section and use them.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s